I was wondering if there is a performance gain between a SELECT query with a not very specific WHERE clause and another SELECT query with a more specific WHERE clause.
For instance is the query:
SELECT * FROM table1 WHERE first_name='Georges';
slower than this one:
SELECT * FROM table1 WHERE first_name='Georges' AND nickname='Gigi';
In other words is there a time factor that is link to the precision of the WHERE clause ?
I'm not sure to be very understandable and even if my question takes into account all the components that are involved in database query (MYSQL in my case)
My question is related to the Django framework because I would like to cache an evaluated queryset, and on a next request, take back this cached-evaluated queryset, filter it more, and evaluate it again.
D'Arcy Rittich :
There is no hard and fast rule about this.\n\nThere can be either an increase or decrease in performance by adding more conditions to the WHERE clause, as it depends on, among other things, the:\n\n\nindexing \nschema \ndata quantity\ndata cardinality\nstatistics \nintelligence of the query engine\n\n\nYou need to test with your data set and determine what will perform the best.",
2012-07-19T15:00:52
rogal111 :
MySql server must compare all columns in your WHERE clause (if all joined by AND ).\n\nSo if you don't have any index on column nickname second query will by slightly slower.\n\nHere you can read how column indexes works (with examples similar to your question): http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html",
2012-07-19T15:02:29
Guido Lo Spacy :
I think is difficult to answer this question, too many aspects (e.g.: indexes) are involved. I would say that the first query is faster than the first one, but I can't say for sure.\n\nIf this is crucial for you, why don't you run a simulation (e.g.: run 1'000'000 of queries) and check the time? ",
2012-07-19T15:03:09