Test the user and user_detail tables for 100w data each

The following is a common pagination SQL for linked table queries

SELECT * FROM user u LEFT JOIN user_detail ud ON u.id = ud.user_id LIMIT 800000, 10
Copy the code

The execution time is 3.323 seconds

I could write it like this

SELECT * FROM (SELECT * FROM user LIMIT 800000, 10) u LEFT JOIN user_detail ud ON u.id = ud.user_id 
Copy the code

The execution time is 0.020 seconds

This is because the FROM clause is always the first to be executed in an SQL statement, so the primary table is queried before joining the two tables

10000000:10 efficiency difference

You can optimize it even more

SELECT * FROM (SELECT * FROM user WHERE id > 800000 LIMIT 10) u LEFT JOIN user_detail ud ON u.id = ud.user_id
Copy the code

The execution time is 0.015 seconds

When limit 800000 is set, mysql filters 80W rows of data and then 10 rows of data

With WHERE ID, the primary key index is selected and 10 entries are selected

Please comment on any fallacies