•  

Announcement

Announcement Module
Collapse
No announcement yet.

Query with many WHERE clause question

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query with many WHERE clause question

    #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,12338 32318,1238 904508,1249089491,1282863051,1312494447,1376282521 ,138160486 1,1385005897,1398891934,1402842598,1468859471,1529 349460,154 3114389,1548353079,1549054617,1571914454,165907087 1,16792789 87,1710759448,1730623872,1828437160,1832984639,185 4336685,19 05636426,1954649947,1968225540,2104630051,21206404 91,2135502 244,2137025828,2237617232,2239168055,2261941941,23 20472868,2 419062188,2450748621,2451715591,2513198664,2561373 602,261542 5018,2619967012,2625382898,2665924945,2681411117,2 730829516, 2758953497,2808142619,2880570142,2886001685,289836 0362,29007 59488,2925588951,2987536684,2999878127,3001889291, 3019677265 ,3031201704,3135304259,3181534725,3265244011,32728 00226,3286 774688,3290666357,3299691500,3327119216,3348536029 ,341292498 6,3416765413,3436263261,3490927984,3557766570,3561 494533,356 2545987,3606791845,3614305201,3739342454,386861759 7,38769940 02,3894695880,3904528983,3936052192,3947685092,395 5749448,39 61975961,3976397517,4025756315,4117134136,41253087 31,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
    Tags: None

  • #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].

    Comment


    • #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,t est | 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,t est | 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

      Comment


      • #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 ?

        Comment


        • #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.

          Comment

          Working...
          X