CODEWITHSUNDEEP
mysqlperformanceindexing
iss42
iss42

Reputation: 2816

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

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!

Upvotes: 0

Views: 69

Answers (1)

Sashi Kant
Sashi Kant

Reputation: 13455

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

Upvotes: 1

Related Questions