mysql query optimization

Question

I have the following query:

SELECT * 
  FROM posts USE INDEX(abc) 
 WHERE a=3 and b=123 and c>4012 and d>5212300 and e!=661 
 LIMIT 10;

INDEX(abc) is an index of column a, b and c.

The query worked well until recently when the database gets big. It now has about 10million rows.

I have been trying to find alternatives to optimize it without much success. I hope someone can give me some clues. Thanks.

BTW, my table engine is innodb.

Can you give us some insight as to how these conditions are generated? — Sam Dufel
– Sam Dufel, Commented Jul 15, 2011 at 3:27
@Dan: adding d and e to index would be useless because it will have to do a full scan because the query for column c is not absolute. — Vern
– Vern, Commented Jul 15, 2011 at 3:39
@Sam: This query is to generate related posts, something like where category = 3, group = 123, domain > 4012, articleID > 5212300 and writer != 661. — Vern
– Vern, Commented Jul 15, 2011 at 3:43
Please post the result from EXPLAIN, and table structure as well — Maxim Krizhanovsky
– Maxim Krizhanovsky, Commented Jul 15, 2011 at 8:27
@Vern I may be wrong, but according to your explanation it seems that b is more unique in this table than a. So, if this is true then you may try index (b, a, c). By the way I would not be so confident that adding d to the index is useless because you are limiting the result to 10 rows only. — Karolis
– Karolis, Commented Jul 15, 2011 at 8:42

Pablo Santa Cruz · Accepted Answer · 2011-07-15 03:30:10Z

1

I would begin my optimization by removing the index hint you are using. Query Optimizers do a pretty good job on guessing what's the best execution plan for your query, and providing index hints in the long run usually contribute negatively to query performance.

SELECT * 
  FROM posts 
 WHERE a=3 and b=123 and c>4012 and d>5212300 and e!=661 
 LIMIT 10;

I also think that independent indexes on columns a, b, c, d and e might help since the query planner will be able to combine indexes to resolve the query. (Not sure about MySQL but PostgreSQL would be able to combine them to resolve the query if it helps).

answered Jul 15, 2011 at 3:30

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vern Over a year ago

I don't think independent indexes can help. Check out this article: mysqlperformanceblog.com/2009/09/19/…

Vern Over a year ago

I just tried removing index hint. It's bad. All the queries suddenly jam up the process list. :(

Pablo Santa Cruz Over a year ago

@Vern: very interesting link indeed. Thanks a lot for sharing!

Collectives™ on Stack Overflow

mysql query optimization

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related