Summary of the difference between using from and join to query two tables in MySQL

Preface

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

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

1. First create 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 and check them out;

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. There is no difference when checking the SQL run analysis.

In order to highlight the performance difference between the two queries, insert 1 million records into table one and 100,000 records into table two. In the face of a large amount of data, the slightest difference will be infinitely magnified. Now let's compare the differences.

First use Python to insert data into the database. Why use Python? Because Python writes simple

On the code

import pymysql

db = 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) + '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) + 'commit')
db.commit()
print('insert two ok')

Be patient and wait for a while, the insertion will take some time;

After the data is inserted, let's query 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 seconds;

Let’s use JOIN query again

select one.id,one.one,two.id,two.two from one join two on one.id=two.id; 

It took 19.45 seconds. Among 100,000 data items, a 1-second error is not a big deal.

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

There is no difference in query time. Let's take a look at the SQL execution analysis.

The result is still the same

Summarize

In MySQL, using FROM to query multiple tables and using JOIN connection (except LEFT JOIN and RIGHT JOIN) have the same query results and query efficiency.

Well, the above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.