mysql index optimization problem

Question

I have a query like below.

SELECT a,b,c,d 
FROM table_name  
WHERE e=1 AND f=0 
GROUP BY d 
ORDER BY id DESC LIMIT 5

when I indexed (e,f,d) query time : 4 sec.

when I indexed (d,e,f) query time : 20 sec.

So that I understand that index colums ordering is important. Which index would be better for mysql?

I don't understand a little: you are grouping by d and then you are trying to sort everything by id. But the problem is that id is undefined in this case. So I believe the query can't use the index for sorting. Your query is not correct. — Karolis
– Karolis, Commented Jul 27, 2011 at 13:03

Maxim Krizhanovsky · Accepted Answer · 2011-07-27 11:32:33Z

1

Indexes should first satisfy the where clause, then the group or order clause. Always use EXPLAIN on your query to see how it is performed

answered Jul 27, 2011 at 11:32

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

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

7 Comments

user865238 Over a year ago

when I indexed (e,f,d) query time : 4 sec. 4 sec. is not long for this query? i have 500.000 records.

Maxim Krizhanovsky Over a year ago

show output of EXPLAIN. See how query performs without order by or without group by. Usually group by creates temporary tables and is much slower. But still 4 seconds is too much.

user865238 Over a year ago

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table_name ref index_2,index,index_3,index_4,index_5 index_5 2 const,const 493261 Using where; Using temporary; Using filesort

user865238 Over a year ago

without group and order Query took 0.0008 sec.

user865238 Over a year ago

only with group Query took 0.0014 sec only with order Query took 4.0751 sec i think order section is occure slow.

|

Collectives™ on Stack Overflow

mysql index optimization problem

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related