Adding index to Mysql tables

Question

Which fields should be indexed to speed up the following query?

SELECT *
FROM sms_report
WHERE R_uid = '159'
AND R_show = '1'
ORDER BY R_timestamp DESC , R_numbers

now its using filesort, i need to optimize that.

Ron Davis
– Ron Davis

2010-12-18 13:42:29 +00:00
Commented Dec 18, 2010 at 13:42 — Ron Davis
– Ron Davis, Commented Dec 18, 2010 at 13:42

Mchl · Accepted Answer · 2010-12-18 13:15:47Z

3

Basing on this information, a composite index on (R_uid, R_show) should be good.

http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html

To be able to tell anything more, you'd need to show us results of EXPLAIN

http://dev.mysql.com/doc/refman/5.5/en/explain.html

and tell which storage engine is being used.

answered Dec 18, 2010 at 13:15

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

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

1 Comment

Ron Davis Over a year ago

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE sms_report ref R_uid,R_show R_uid 4 const 756276 Using where; Using filesort

Collectives™ on Stack Overflow

Adding index to Mysql tables

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related