CODEWITHSUNDEEP
mysqldatabase-performance
bellwether
bellwether

Reputation: 81

WHERE condition avoids full table scan somehow

When evaluating the performance of creating a SQL view query, I noticed a significant improvement in performance when adding a WHERE clause at the end. The output difference is not much (~3% less rows).

e.g.

SELECT x,y,z  
FROM (multiple table joins/sub queries)

vs

SELECT x,y,z  
FROM (multiple table joins/sub queries)  
WHERE x >= 0 (x is not indexed)

Checking the execution plans, it seems that the biggest difference is the WHERE condition avoids a full table scan which explains the difference in speed. Why is that?

EDIT: Screenshots showing execution operation with WHERE condition vs without

enter image description here

enter image description here

Upvotes: 1

Views: 277

Answers (1)

Rick James
Rick James

Reputation: 142433

(Not enough info, even with the screenshots, to intelligently reply. But I can make a guess...)

When faced with JOIN(s), the Optimizer often (but not always) uses these rules to decide which table to start with:

  • Start with the table that seems to have the best WHERE filtering. That may have led it to pick the table with x in it. Even it that were a full table scan, picking that table may be better.

  • Start with the 'smallest' table.

Note: It scans the 'first' table; for each row in that table, it reaches into the 'next' table, then the next, etc. The Optimizer is free to reorder the tables as it sees fit (within constraints such as LEFT).

In reality, the later incarnations of the Optimizer use a "cost-based" analysis. However, the above two 'rules' are what effectively happens.

Also, the Optimizer may be misled by statistics, or lack of such, on which it bases the query plan.

Upvotes: 1

Related Questions