does mysql join first or limit first?

[Xuefer]

SELECT ...
FROM table1
LEFT JOIN table2 ON table2.key2=table1.key_part2
WHERE table1.key_part1=<constant>
ORDER BY table1.key_part2
LIMIT 100,100

both WHERE and LIMIT don't refer to table2

steps:
1. use "where" condition to search(i meant via key) on table1
2. join table2 using "ON ***" to get result
3. stop as soon as 100 rows found

does mysql do join first(123) or do limit first(132)?

[jollymcfats]

LIMIT is limiting the return of the entire expression, regardless of how which tables are referenced. Furthermore, you've got an ORDER BY, which requires ordering the entire set before a limit can be applied.

Modulo query optimizations, it looks like the ordering would be pretty much exactly as the expression is written.

1) Left join
2) Restrict (where)
3) Order
4) Limit

[Xuefer]

hrm... thanks

but i need your more confirm.

the key pont is: both WHERE and LIMIT don't refer to table2

1. when i do "limit 100,100" or even "limit 10000,10"
mysql will join first(10000 rows from table1 and join 10000rows from table2), than limit(stop)
right?

2. when i do "SELECT table1.id FROM table1..."
then do "SELECT ... FROM table1 LEFT JOIN table2 ... WHERE
table1.id IN ($ids)"
it's faster than my 1st sample.
right?

i did it and it seem take effect, but i need real confirm and well explained theory(mysql manual say not even 1 bit about this)

note: there's so many joins to big tables in my real-life sql

[jollymcfats]

the key pont is: both WHERE and LIMIT don't refer to table2

They actually both do, just not directly. Here's the query in English:

"Take the product of table1 and table2, and restrict it to only rows where key_part1 equals some constant. Order the restricted rows by key_part2, and of that set return rows 100 through 200."

None of the later steps can happen before the first: the joining of table1 and table2. Everything that comes after is some manipulation of the product of those two tables.

If you make the WHERE clause more restrictive (as in your table1.id IN ($ids)), you'll shrink the set of rows even further, making less work for further WHERE clauses and the ORDER BY.

but i need real confirm and well explained theory (mysql manual say not even 1 bit about this)

The MySQL

EXPLAIN

command is useful to find how the engine will process queries, including how many rows need to be examined. The "slow query log" is also really handy, though it's not enabled by default.

The theory of relational algrebra and how databases think about queries is explained well and simply in the book "SQL Clearly Explained". Highly recommended.

[Xuefer]

i just couldn't image why mysql Optimizer don't have any better solution to this situation

i'm going to separate the JOIN which table is not in where clause for "LIMIT VERY_BIG,m"
many thanks

btw, is there any better way to write in 1 query but let LIMIT first?
i guess no, if yes u would have told me :(

[lorax]

You might find your answer here [dev.mysql.com]. It's worth reading in any case.

[Xuefer]

i read doc more than 10 times

If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count lines rather than sorting the whole table.

it's not telling me join must be done Before limit
but
it's not telling me join can be done After limit

the point is: things not said is not optimized :P