title: Drupal MySQL query optimizazion
body: <p>I'm trying to optimize a MySQL query to speed up a Drupal view.</p>
  
  <p>The tables are node, term_node and term_data. Node has about 500k rows, term_node about 800k and term_data about 300k.</p>
  
  <p>Below the query:</p>
  
  <pre><code>    SELECT SQL_NO_CACHE DISTINCT(node.nid) AS nid
      FROM drupal_node node
      LEFT JOIN drupal_term_node term_node ON node.vid = term_node.vid
      LEFT JOIN drupal_term_data term_data ON term_node.tid = term_data.tid 
      WHERE (node.status = 1) AND (node.type in ('foto_foto')) 
      AND (
        (UPPER(term_data.name) LIKE UPPER('%Hanaa%') OR 
        UPPER(term_data.name) LIKE UPPER('%Bouchaib%')))
      GROUP BY nid
      LIMIT 0, 10;
  </code></pre>
  
  <p>This query takes about 11s to show up result.
  All the columns involved in the join are indexed, as well as term_data.name.
  node.vid, term_node.vid, term_node.tid and term_data.tid are int(10), term_data.name is a varchar(255).</p>
  
  <p>The strange thing is, running the composing queries like:</p>
  
  <pre><code>SELECT SQL_NO_CACHE DISTINCT(term_node.tid)
  FROM drupal_term_data term_data
  LEFT JOIN drupal_term_node term_node ON term_node.tid = term_data.tid 
  WHERE ((UPPER(term_data.name) LIKE UPPER('%Hanaa%') 
    OR UPPER(term_data.name) LIKE UPPER('%Bouchaib%')))
  group by term_node.tid
  </code></pre>
  
  <p>and</p>
  
  <pre><code>SELECT SQL_NO_CACHE DISTINCT(node.nid) AS nid
  FROM drupal_node node
  LEFT JOIN drupal_term_node term_node ON node.vid = term_node.vid
  WHERE (node.status = 1) AND (node.type in ('foto_foto')) 
  GROUP BY nid
  </code></pre>
  
  <p>takes 0.23s for the firs and 0.12s for the last. So I'm expecting that the main query should run below 0.5s at least. It's like the optimizer had not ran on the query.</p>
  
  <p>I've already ran ANALYZE and OPTIMIZE on the involved tables, and below is the result of EXPLAIN EXTENDED.</p>
  
  <pre><code>*************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: node
           type: range
  possible_keys: vid,node_status_type,node_type
            key: node_status_type
        key_len: 102
            ref: NULL
           rows: 496217
       filtered: 100.00
          Extra: Using where
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: term_node
           type: ref
  possible_keys: PRIMARY,vid
            key: vid
        key_len: 4
            ref: drupal_foto.node.vid
           rows: 7
       filtered: 100.00
          Extra: Using where
  *************************** 3. row ***************************
             id: 1
    select_type: SIMPLE
          table: term_data
           type: eq_ref
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: drupal_foto.term_node.tid
           rows: 1
       filtered: 100.00
          Extra: Using where
  3 rows in set, 1 warning (0.00 sec)
  </code></pre>
  
  <p>The warning provide only a note level warning with code 1003.</p>