Query optimization - order of expressions in WHERE clause

Lets say I have a table named numbers:

userID  ColA  ColB
------------------
25      10     11
25      10     16
28      10     11
28      10     16
29      12     14  
29      10     16

I want to find all rows which have userID 28 and colA = 10 and colB = 16

My question is of query structure. If I look for the userID first, and then the colA and colB values e.g.

select * from numbers where userID=28 AND (colA=10 AND colB=16)

is this faster than looking for values first e.g.

select * from numbers where (colA=10 AND colB=16) AND userID=28

Or is there really not much difference? I guess I'm asking about how the engine reads the query results as there are more results for (colA=10 AND colB=16) than there are userID=28. Therefore I would assume least possibles first?

I understand about indexes etc too.

Answers


Your RDBMS will figure out the best execution plan for that query. You won't be able to change the order of those clauses to improve performance because the RDBMS will already consider things like that when coming up with a plan.

You can use the EXPLAIN command to see more information about the actual execution plan the RDBMS decided to use.


Your whole question doesn't make sense:

My question is of query structure. If I look for the userID first, and then the colA and colB values e.g.

Absent indexes or partitions, the database is simply scanning the table row-by-row. It looks up the values within a row and does all the comparisons on a row "at the same time". It is possible -- but by no means guaranteed -- that the order of expression will be the order of evaluation. That would be deep in the internals of the MySQL code, and could change from version to version. SQL definitely does not specify the order of evaluations. I know that MySQL explicitly does not specify the order of evaluation in a select clause. But I don't know about the where.

The order of evaluation could have a microscopic effect on performance, if we assume short-cutting. That is, first a comparison is made. If it is false, then no more comparisons are made. I'm not aware that MySQL does shortcuts. Even if it did, the effect on performance would be minuscule on a where clause with three simple comparisons.. The time to evaluate the condition is nothing compared to the loading of the data into the page table.

The situation becomes a bit more complicated with indexes. In theory, you could have a separate index on each column and MySQL would need to select which, if any, of the indexes to use. In that case, the query engine would be looking at statistics to estimate how selective each condition is. It would not be using your order of evaluation.


Need Your Help

converting convention node.js function with callback to generators and yield

javascript node.js koa co

I am new to koa.js and liked it very much, started a project with it. i need to use twilio for sms sending.

CouchBase Member Login

nosql couchbase

Could anyone provide me with a great explination or tutorial around a user management couchbase database.