Query slow - not using relevent indexes

Question

The table has around 7 million rows. The following query takes around 7 seconds and it seems to use none of the related indexes:

select 
lead_id as id , 
user_id , 
cmol_status_id , 
crm_status_id , 
product_status_id , 
callmeback , 
followup_date , 
lead_attempt_id , 
allocated_to , 
new_id , 
leadpriority 
  from gen_que 
 where lead_attempt_id > '1' 
   and followup_date <= curdate() 
   and lead_type in ('User', 'LenderOffer') 
   and crm_status_id in ('147', '180', '181', '182') 
   and product_family_id in ('1', '2', '21', '23', '19') 
   and product_status_cat in ('01', '02', '05', '06', '07','021', '022', '023') 
   and new_id in ('11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '29', '30', '31', '32', '33', '35', '36', '37', '39', '40', '41', '42') 
order by lead_id desc limit 5;

Explain extended result:

**id select_type table       type
  1  SIMPLE      gen_queview index

possible_keys
igen_queview_attemptallocated, igen_queview_followup_date, igen_queview_newcmol_id,  
igen_queview_crmstatus_id, igen_queview_prodstatuscat, igen_queview_prodfamiliyid

key     key_len ref rows filtered Extra 
PRIMARY 8       \N  1023 2921.7   Using where**

Instead of checking for multiple values in 'In' clause, if I use one value, then the result is in .03 secs.

Can anyone help out? Thanks in advance.

Answer 1

According to my knowledge of indexes (i haven't work with MySql lately), first: whatever field you have in your LIST of SELECT should also be indexed.

But i noticed another probable behavior that could be happening in your given query: in your INs you are looking for strings ('1', '2', 'etc'), are those fields Varchars, Chars?.

If not, try to use INT instead of STRING, if your "product_family_id" is INT and you are doing "product_family_id IN ('1','2') the engine executes an implicit conversion, resulting in more executon time.

If "product_family_id" is string and you are storing numbers as text. and if you only have numbers stored in there (as string though) then try to cast to int

cast(product_family_id as int) IN (1,2,3,4,5)

Try both solutions. Add an Index to the select list members, use numbers instead of text. and try casting as well.

Answer 2

MySQL rarely uses "index merge union" (probably the same as what SQL Server is more happy to do). This is because it is inefficient.

Usually the best way to speed up a query with WHERE ... AND ... is to have a composite index (multiple columns). But that is practical only if the first columns are tested with = and only the last one may be a range or IN.

So, the best MySQL do for that query is to use a single-column index (or, equivalently) use only the first column of a multi-column index).

Please provide SHOW CREATE TABLE. Meanwhile, I will guess it did a "table scan" because even the ranges were not very selective, and it decided that it would be faster to simply scan the entire table than to bother bouncing between an index and the data.

Since you have ORDER BY ... LIMIT ..., the optimizer might hope that avoiding the "sort" normally required by ORDER BY might lead to scanning only a few rows before satisfying the LIMIT. If so, it might help to add

INDEX(lead_id)

(Or was that the PRIMARY KEY??)

Is lead_id a BIGINT? Do you really expect to have a zillion ids?