0

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.

4
  • What are the full indexes on this? You have some large INs there, which will have to drill down the indexes for those many times, maybe you'd do better to make sure you're hitting indexes which focus on the more limiting fields first? Maybe even remove indexes which key first on new_id or product_status_cat? Commented Feb 8, 2018 at 15:32
  • I am more of a SQL Server guy. I bet the IN gets applied as OR and has an ugly query plan. In SQL Server I would make them a join on a PK via temp tables.
    – paparazzo
    Commented Feb 8, 2018 at 17:39
  • Try an index on every column in the where ?
    – paparazzo
    Commented Feb 8, 2018 at 17:44
  • Give us a clue -- what is the PRIMARY key that it picked?
    – Rick James
    Commented Feb 15, 2018 at 1:55

2 Answers 2

0

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.

0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.