CODEWITHSUNDEEP
mysqldjangoperformancedjango-querysetdatabase-performance
renard
renard

Reputation: 1468

Mysql SELECT query and performance

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.

Upvotes: 0

Views: 1005

Answers (4)

Andy
Andy

Reputation: 1618

Yes, it can be slower. It will all depend on indexes you have and data distribution. Check the link Understanding the Query Execution Plan

for information on how to know what MySQL is going to do when executing your query.

Upvotes: 1

Guido Lo Spacy
Guido Lo Spacy

Reputation: 439

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.

If this is crucial for you, why don't you run a simulation (e.g.: run 1'000'000 of queries) and check the time?

Upvotes: 1

rogal111
rogal111

Reputation: 5933

MySql server must compare all columns in your WHERE clause (if all joined by AND ).

So if you don't have any index on column nickname second query will by slightly slower.

Here 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

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

There is no hard and fast rule about this.

There 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:

  • indexing
  • schema
  • data quantity
  • data cardinality
  • statistics
  • intelligence of the query engine

You need to test with your data set and determine what will perform the best.

Upvotes: 1

Related Questions