How to optimize mysql query?

Question

This query is taking long time to execute. Anybody tell me how to optimize this query?

SELECT distinct(node.nid) AS nid ,
       CAST((LENGTH(node_data_field_pdfextract.field_pdfextract_value) - LENGTH(REPLACE(LOWER(node_data_field_pdfextract.field_pdfextract_value) , 'test', ''))) / LENGTH('test') AS UNSIGNED ) AS name_match ,
       node.title AS node_title,
       node_data_field_first_name.field_cj_article_author_name_value,
       node_data_field_first_name.field_first_name_value
FROM ram_node node
LEFT JOIN
  (SELECT td.tid,
          tn.vid AS revision
   FROM ram_term_data td
   INNER JOIN ram_term_node tn ON tn.tid = td.tid
   WHERE td.vid IN (12,
                    15,
                    14)) term_data_node ON node.vid = term_data_node.revision
LEFT JOIN ram_content_type_cj_article node_data_field_first_name ON node.vid = node_data_field_first_name.vid
LEFT JOIN ram_node_revisions node_revisions ON node.vid = node_revisions.vid
LEFT JOIN ram_term_node term_node ON node.vid = term_node.vid
LEFT JOIN ram_term_data term_data ON term_node.tid = term_data.tid
LEFT JOIN ram_content_field_pdfextract node_data_field_pdfextract ON node.vid = node_data_field_pdfextract.vid
LEFT JOIN ram_uc_products uc_products ON node.vid = uc_products.vid
WHERE (node.status = 1)
  AND (node.type IN ('cj_article'))
  AND ((UPPER(node_revisions.body) LIKE UPPER('%test%'))
       OR UPPER(node_data_field_pdfextract.field_pdfextract_value) LIKE UPPER('%test%'))
  AND (UPPER(node.title) LIKE UPPER('%%'))
ORDER BY name_match DESC;

It would be better if you give us your schema and tell us your goal.. — Bla...
– Bla..., Commented Jul 15, 2014 at 7:01

Paul Maxwell · Accepted Answer · 2014-07-15 07:31:18Z

stage 1, remove all unnecessary joins. You are only using node and node_data_field_pdfextract in the select clause and the where clause.

stage 2, because you are referencing node_data_field_pdfextract in the where clause there isn't any point in using a left join on that table, use the more efficient INNER JOIN instead.

step 3, is distinct really required? it requires a lot of resources. if it isn't required get rid of it.

step 4, are all the uses of UPPER() required in the where clause? when applying functions to a field use of any index on that field is denied causes full table scans in most cases. Do away with those functions if this is at all possible. Consider storing the data in UPPER and indexing that perhaps.

for steps 1,2 & 3:

SELECT
      node.nid                                                                                                            AS nid
    , CAST((LENGTH(node_data_field_pdfextract.field_pdfextract_value) 
     - LENGTH(REPLACE(LOWER(node_data_field_pdfextract.field_pdfextract_value), 'test', ''))) / LENGTH('test') AS UNSIGNED) AS name_match
    , node.title                                                                                                            AS node_title
    , node_data_field_first_name.field_cj_article_author_name_value
    , node_data_field_first_name.field_first_name_value
FROM ram_node node
      INNER JOIN ram_content_field_pdfextract node_data_field_pdfextract
                  ON node.vid = node_data_field_pdfextract.vid
WHERE node.status = 1
      AND node.type IN ('cj_article')
      AND (
             UPPER(node_revisions.body) LIKE UPPER('%test%')
            OR 
             UPPER(node_data_field_pdfextract.field_pdfextract_value) LIKE UPPER('%test%')
           )
      AND UPPER(node.title) LIKE UPPER('%%')
ORDER BY
      name_match DESC;

By the way SELECT distinct(node.nid)

those parentheses are just ignored by the parser. Distinct is NOT a function.

DISTINCT qualifies SELECT (i.e. think of "SELECT DISTINCT" as a pair of words) plus:

SELECT DISTINCT is a "row operator"; it is performed over all parts of the row and certainly not just the field you placed in parentheses.

I suggest you stop using those parentheses because it is making you think it does things which it simply will not do.

Collectives™ on Stack Overflow

How to optimize mysql query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related