Drupal MySQL query optimizazion

Question

I'm trying to optimize a MySQL query to speed up a Drupal view.

The tables are node, term_node and term_data. Node has about 500k rows, term_node about 800k and term_data about 300k.

Below the query:

    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;

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).

The strange thing is, running the composing queries like:

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

and

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

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.

I've already ran ANALYZE and OPTIMIZE on the involved tables, and below is the result of EXPLAIN EXTENDED.

*************************** 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)

The warning provide only a note level warning with code 1003.

Answer 1

I gant gaurantee results but here are some things to try:

1. SInce the query has a comparison filter on term_data it shouldn't be using left join. Change left joins to inner join as it only will return rows in both tables in each, and this will allow it to go by the term table first.

2. Distinct and group by are redundant. You shouldn't need both. Use one or the other.