Shulou(Shulou.com)06/01 Report--

Preface

In mysql, multi-table join query is a very common requirement. When using multi-table query, you can from multiple tables or join multiple tables using join.

What's the difference between these two kinds of queries? Which query is more efficient? With these questions, I decided to give it a try.

1. First set up two tables one and two on the local mysql

One table

CREATE TABLE `one` (`id` int (0) NOT NULL AUTO_INCREMENT, `one` varchar (100) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET = utf8

Two table

CREATE TABLE `two` (`id` int (0) NOT NULL AUTO_INCREMENT, `two` varchar (100) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB CHARACTER SET = utf8

First insert a few pieces of data casually, query and have a look

Select one.id,one.one,two.id,two.two from one,two where one.id=two.id

Select one.id,one.one,two.id,two.two from one join two on one.id=two.id

Comparing the two queries, there is almost no difference in query time, and there is no difference when viewing sql to run the analysis.

In order to highlight the performance difference between the two queries, insert 100w pieces of data into the one table and 10w pieces of data into the two table, in front of a large amount of data, the slightest difference will be infinitely magnified; at this time, let's compare the difference.

First use python to insert data into the database, why use python, because python is easy to write

Upper code

Import pymysqldb = pymysql.connect ("127.0.0.1", 'root', "123456", "bruce") cursor = db.cursor () sql = "INSERT INTO one (one) values (% s)" for i in range (1000000): cursor.executemany (sql) ['one' + str (I)]) if I% 10000 = 0: db.commit () print (str (I) +' sub-commit') db.commit () print ('insert one ok') sql2 = "INSERT INTO two (two) values (% s)" for i in range (100000): cursor.executemany (sql2, [' two' + str (I)]) if I% 10000 = = 0: db.commit () print (str (I) + 'sub commit') db.commit () print (' insert two ok')

Wait patiently for a while, it will take some time to insert

When the data insertion is complete, let's check some.

First use FROM two tables to query

Select one.id,one.one,two.id,two.two from one,two where one.id=two.id

It takes about 20.49.

Use JOIN query again to take a look.

Select one.id,one.one,two.id,two.two from one join two on one.id=two.id

It takes 19.45, and in 10w data, the error of 1 second is not big.

Take a look at the query when using id as a conditional constraint

There is no difference in query time.

Take another look at the sql execution analysis.

The result is still the same.

Summary

Using FROM to query multiple tables in mysql and using JOIN join (except LEFT JOIN,RIGHT JOIN), the query results and query efficiency are the same.

All right, that's all of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. If you have any questions, you can leave a message and exchange. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.