MySQL query - why doesn't it use the index in this case?

Question

MySQL query (ver 5.1.61)

SELECT alerts.*,
       devices.user_id
  FROM alerts
       left JOIN devices
          ON alerts.device_id = devices.id
 WHERE    devices.name = 'myval'

There is an index on "alerts.device_id" MySQL in Explain does not use the index, it shows type "ALL" and rows being the full count of rows in the table.

I can't understand why it doesn't use this index. What am I missing?

Thanks!

Sorry I over simplified the actual query and missed out the problem bits! Will update this when I find it again. — iss42
– iss42, Commented Dec 5, 2012 at 15:50

Sashi Kant · Accepted Answer · 2012-12-05 13:06:32Z

1

The index are not getting picked, because you have not used it, neither in the select nor in the where clause.

You need to add an index on device.name

You can do one thing to force the engine to consider the index if it is a primary key and its value if is more than 0, like :

SELECT alerts.*,
       devices.user_id
  FROM alerts
       left JOIN devices
          ON alerts.device_id = devices.id
 WHERE    devices.name = 'myval' and alerts.deviceid>0

answered Dec 5, 2012 at 13:06

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

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

Collectives™ on Stack Overflow

MySQL query - why doesn't it use the index in this case?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related