CODEWITHSUNDEEP
mysqlquery-optimization
Jaswinder
Jaswinder

Reputation: 33

Need Help to Optimize Query

Can someone please help me optimize following query? Its to search for a search term and result data which will be used in an auto complete form. Data will be sent as JSON (probably) or HTML (). But for now, my concern is how I can optimize this query. There will be around 20000 users per day on the site (maybe all at same time) and I am hoping to optimize this as much as I can.

As some might have guessed, these are all Drupal tables and I am generating a custom query.

    EXPLAIN SELECT n.nid AS nid, fcs.field_call_sign_value AS field_call_sign_value, old.field_r_13_n_old_value AS field_r_13_n_old_value,
            new.field_r_13_n_new_value AS field_r_13_n_new_value,fn.field_name_value AS field_name_value
        FROM node n
            INNER JOIN field_data_field_call_sign fcs ON n.nid = fcs.entity_id and n.vid=fcs.revision_id
            INNER JOIN field_data_field_name fn ON n.nid = fn.entity_id and n.vid=fn.revision_id
            INNER JOIN field_data_field_r_13_n_old old ON n.nid = old.entity_id and n.vid=old.revision_id
            INNER JOIN field_data_field_r_13_n_new new ON n.nid = new.entity_id and n.vid=new.revision_id
        WHERE  (n.title LIKE '%APTNHD%' ESCAPE '\\') 
        ORDER BY n.created DESC
        LIMIT 5 OFFSET 0

Explain Result:

id  select_type table   type    possible_keys           key             key_len ref     rows    Extra
1   SIMPLE      fcs     ALL     entity_id,revision_id   NULL            NULL    NULL    11  Using temporary; Using filesort
1   SIMPLE      old     ALL     entity_id,revision_id   NULL            NULL    NULL    11  Using where; Using join buffer
1   SIMPLE      new     ALL     entity_id,revision_id   NULL            NULL    NULL    11  Using where; Using join buffer
1   SIMPLE      fn      ALL     entity_id,revision_id   NULL            NULL    NULL    11  Using where; Using join buffer
1   SIMPLE      n       eq_ref  PRIMARY,vid PRIMARY 4   DB.new.entity_id                1   Using where

Please let me know if you need any more information

EDIT: New EXPLAIN result after "Stephan"'s answer Original Query took "0.0010 sec" (and same on multiple executes) New Query took "0.0012 sec" (and "0.0007 sec" after running it again)

id  select_type table   type    possible_keys           key         key_len ref         rows    Extra
1   SIMPLE      n       ALL     NULL                    NULL        NULL    NULL        44      Using where; Using filesort
1   SIMPLE      fcs     ref     entity_id,revision_id   entity_id   4       DB.n.nid    1   
1   SIMPLE      fn      ref     entity_id,revision_id   entity_id   4       DB.n.nid    1   
1   SIMPLE      old     ref     entity_id,revision_id   entity_id   4       DB.n.nid    1   
1   SIMPLE      new     ref     entity_id,revision_id   entity_id   4       DB.n.nid    1

Upvotes: 1

Views: 67

Answers (1)

Stephan
Stephan

Reputation: 8090

The mysql query optimizer has decided to switch the order the tables are joined in order to have less scanned rows but not in all cases this is good.

I see that for the joined table you have index on entity_id which is good , so you need to maintain the join table order using STRAIGHT_JOIN and LEFT JOIN:

EXPLAIN SELECT STRAIGHT_JOIN n.nid AS nid, fcs.field_call_sign_value AS field_call_sign_value, old.field_r_13_n_old_value AS field_r_13_n_old_value,
            new.field_r_13_n_new_value AS field_r_13_n_new_value,fn.field_name_value AS field_name_value
        FROM node n
            LEFT JOIN field_data_field_call_sign fcs ON n.nid = fcs.entity_id and n.vid=fcs.revision_id
            LEFT JOIN field_data_field_name fn ON n.nid = fn.entity_id and n.vid=fn.revision_id
            LEFT JOIN field_data_field_r_13_n_old old ON n.nid = old.entity_id and n.vid=old.revision_id
            LEFT JOIN field_data_field_r_13_n_new new ON n.nid = new.entity_id and n.vid=new.revision_id
        WHERE  (n.title LIKE '%APTNHD%' ESCAPE '\\') 
        ORDER BY n.created DESC
        LIMIT 5 OFFSET 0

Furthermore you can add a FULLTEXT INDEX on n.title column because then you can use fulltext-searchs which are faster then (n.title LIKE '%APTNHD%' ESCAPE '\\')

Upvotes: 1

Related Questions