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.
PRIMARY
key that it picked?