After reading Slow SQL query, not sure how to optimize, it got me thinking about the general performance of queries. Surely, we need the results of the first table (when other tables are joined) to be as small as possible before joining (inner joins for this question) in order to make our queries that tiny bit faster.
Example, should this:
SELECT * FROM ( SELECT * FROM table1 WHERE col = @val ) t INNER JOIN table2 ON col = col2
Be better/faster than:
SELECT * FROM table1 INNER JOIN table2 ON col = col2 WHERE table1.col = @val
My theory is as follows (this might not be the correct implementation, I am trying to remember from a SQL Server 2008 internals book I read (MSFT Press)):
- The query processor first gets the left table (table1)
- Joins the second table (table2) and forms a cartesian product before filtering out the necessary rows (if applicable)
- Then performs the WHERE, ORDER BY, GROUP BY, HAVING clauses with the SEELCT statement last.
So if in statement #1 above, the table is smaller, the SQL engine has less work to do when forming the cartesian products. Then when you reach the where statement, you have a reduced result set from which to filter in memory.
I could be so far off the mark it's unreal. Like I said, it's a theory.
Note: I've only just thought of this question and haven't had chance to run any tests my self yet.
Note 2: Tagged as SQL Server as I don't know anything about the implementation of MySql etc. Please feel free to answer/comment anyway