Query with many WHERE clause question

MySQL & MariaDB Other MySQL® Questions
1

confused: I have a InnoDB table look like this with (5,000,000 rows)

±-----------------------±-----------------±-----±----±--------------------±---------------+| Field | Type | Null | Key | Default | Extra |±-----------------------±-----------------±-----±----±--------------------±---------------+| item_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | service_id | int(10) unsigned | NO | | NULL | | | publish_date | timestamp | NO | MUL | 0000-00-00 00:00:00 | | | guid | binary(16) | NO | MUL | NULL | | | user_id | int(10) unsigned | NO | MUL | NULL | | | service_type_id | int(10) unsigned | NO | | NULL | | | item_content_id | int(10) unsigned | NO | MUL | NULL | | | parent_item_content_id | int(10) unsigned | NO | MUL | 0 | | | source | char(31) | YES | | NULL | | | source_guid | binary(16) | NO | | NULL | | | service_item_guid | binary(16) | NO | | NULL | | ±-----------------------±-----------------±-----±----±--------------------±---------------+

I’m trying to execute this query
SELECT item_id,item_content_id,service_id,user_id,service_type_id,H EX(guid) AS guid,publish_date,source FROM tblitems WHERE parent_item_content_id=0 AND service_type_id=101 AND user_id in (174131135,180679365,192202991,214776398,234247197,314202862 ,366489655,447894579,526731760,556098048,556652039,561040515 ,638419537,651980840,668107789,701655633,706950612,782231381 ,801508556,861750775,883262712,910940633,938219324,996426916 ,1053063122,1086695272,1115537464,1184584418,1233832318,1238 904508,1249089491,1282863051,1312494447,1376282521,138160486 1,1385005897,1398891934,1402842598,1468859471,1529349460,154 3114389,1548353079,1549054617,1571914454,1659070871,16792789 87,1710759448,1730623872,1828437160,1832984639,1854336685,19 05636426,1954649947,1968225540,2104630051,2120640491,2135502 244,2137025828,2237617232,2239168055,2261941941,2320472868,2 419062188,2450748621,2451715591,2513198664,2561373602,261542 5018,2619967012,2625382898,2665924945,2681411117,2730829516, 2758953497,2808142619,2880570142,2886001685,2898360362,29007 59488,2925588951,2987536684,2999878127,3001889291,3019677265 ,3031201704,3135304259,3181534725,3265244011,3272800226,3286 774688,3290666357,3299691500,3327119216,3348536029,341292498 6,3416765413,3436263261,3490927984,3557766570,3561494533,356 2545987,3606791845,3614305201,3739342454,3868617597,38769940 02,3894695880,3904528983,3936052192,3947685092,3955749448,39 61975961,3976397517,4025756315,4117134136,4125308731,4255684 738) ORDER BY publish_date DESC, item_id DESC LIMIT 0,20;

I’ve tried the following indexes:

  • (parent_item_content_id,publish_date)
  • (user_id)

The query runs in 200ms.

Is there a ways to speed up the query?

THANKS IN ADVANCE

2

Chop query to the simplier form:
SELECT * FROM tblitems WHERE parent_item_content_id = 0 AND service_type_id = 101 AND user_id in (174131135)

Check what explain says:
Explain SELECT * FROM tblitems WHERE parent_item_content_id = 0 AND service_type_id = 101 AND user_id in (174131135)

What about index (parent_item_content_id, service_type_id, user_id ) ?

Check also how many records there are in Your primary query : select count(*) from tblitems where [that huge condition].

3

januzi, Thank you for you quick reply

I added the index (parent_item_content_id , service_type_id , user_id)

Explain SELECT * FROM tblitems WHERE parent_item_content_id = 0 AND service_type_id = 101 AND user_id in (174131135)

±—±------------±---------±-----±------------------------------------------------------±-----±--------±------------------±-----±------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±---------±-----±------------------------------------------------------±-----±--------±------------------±-----±------+| 1 | SIMPLE | tblitems | ref | tblitems_user_id,tblitems_parent_item_content_id,test | test | 12 | const,const,const | 1 | | ±—±------------±---------±-----±------------------------------------------------------±-----±--------±------------------±-----±------+

The results looks very good. However, if I run the original query, explain gives me:

±—±------------±---------±------±------------------------------------------------------±-----±--------±-----±-----±----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±---------±------±------------------------------------------------------±-----±--------±-----±-----±----------------------------+| 1 | SIMPLE | tblitems | range | tblitems_user_id,tblitems_parent_item_content_id,test | test | 12 | NULL | 6949 | Using where; Using filesort | ±—±------------±---------±------±------------------------------------------------------±-----±--------±-----±-----±----------------------------+

and the query takes 180ms

It’s much better, but is it possible to get under 50ms?

Thanks again

4

So, we have got ~7000 matching records. Mysql has to fetch them all, order by specified columns and return only 20 newest.

  1. Now mysql needs 180ms to return right data and application/script does something with those records (like showing them).
  2. How about: return everything without order and sort data in application/script ?

Could You check the time needed in both cases ?

5

Thanks again januzi. I’ll try your solution.

Well, I’m wondering if it is possible to use indexes such as
(parent_item_content_id, service_type_id, user_id, publish_date).

What i found out was that when i use the composite index
(parent_item_content_id, publish_date), the ‘Using filesort’ won’t be there in Extra.