Time complexity of SQL select query with multiple conditions
What is the time complexity of an SQL select query with multiple conditions?
SELECT *
FROM products
WHERE price > 100
AND width > 100
AND rating > 100
For example, how does a database engine (InnoDB) handle this query with a price, width, and rating index?
Will the engine process the price first and then filter the results by width and rating? This means that first O (log (n) + k) with k number of results and n number of entries in the product table, then O (n) and then O (n), n - the number of results of each last filtering operation.
source to share
You are basically asking how the SQL optimizer works and as pointed out it is SQL version dependent and dependent on it.
In general (very broad) optimizers store metadata about tables so that they can choose which index makes sense. For example, if a table contains gender for students and GPA, you might expect the optimizer to always use an index for GPA. However, if you ran your query in a panel-wide school looking for a woman, the optimizer might figure out that it is faster to look for the gender column first (since very few records will be returned). Also, if your table is very small, the optimizer might say "heck with the indexes, I'm just scanning the entire ink table" ....
In your example, consider how many different values there are. Are all entire columns? If so, the optimizer might ask for the metadata and say "hmmm, there are only 300 rows rated over 100 and 10,000 rows rated over 100, I think I'll use the rating to start with" ....
But as OMG ponies points out, it depends ...
source to share