List:General Discussion« Previous MessageNext Message »
From:brian Date:July 3 2012 7:50pm
Subject:Re: alternative to slow query
View as plain text  
On 12-07-03 02:18 PM, Stillman, Benjamin wrote:
> Not sure why it  wouldn't show primary as a possible key then...

Yes, that seems rather strange.


>  From your first email:
>
> *************************** 1. row ***************************
>              id: 1
>     select_type: SIMPLE
>           table: e
>            type: ALL
> possible_keys: NULL
>             key: NULL
>         key_len: NULL
>             ref: NULL
>            rows: 95127
>           Extra:
>
>
> I'd be curious to see the explain from this:
>
> select id, lang, term from expression where id = (insert a random, valid id value
> here);
>
> Does it use a key then? Or at least show primary as a possible key?

mysql db_lexi > EXPLAIN SELECT id, term, lang_id FROM expression WHERE 
id = 223363\G
*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: expression
          type: const
possible_keys: PRIMARY
           key: PRIMARY
       key_len: 8
           ref: const
          rows: 1
         Extra:
1 row in set (0.00 sec)


Here's the query again, with some of the stuff I'd removed for clarity. 
There are still some other fields missing here but they involve 2 left 
joins on other tables.

mysql db_lexi > EXPLAIN SELECT e.id, e.lang_id, e.term
     -> FROM (expression AS e)
     -> LEFT JOIN expression_expression AS ee1
     -> ON ee1.expression1_id = e.id AND ee1.deleted_at = 0
     -> LEFT JOIN expression_expression AS ee2
     -> ON ee2.expression2_id = e.id AND ee2.deleted_at = 0
     -> WHERE (ee2.expression1_id = 223363 OR ee1.expression2_id = 223363)
     -> AND e.original_id IS NULL
     -> AND e.deleted_at = 0\G
*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: e
          type: ref
possible_keys: original_id_idx,deleted_at_idx
           key: original_id_idx
       key_len: 9
           ref: const
          rows: 60560
         Extra: Using where
*************************** 2. row ***************************
            id: 1
   select_type: SIMPLE
         table: ee1
          type: ref
possible_keys: expression1_id_idx
           key: expression1_id_idx
       key_len: 8
           ref: db_lexi.e.id
          rows: 1
         Extra:
*************************** 3. row ***************************
            id: 1
   select_type: SIMPLE
         table: ee2
          type: ref
possible_keys: expression2_id_idx
           key: expression2_id_idx
       key_len: 8
           ref: db_lexi.e.id
          rows: 1
         Extra: Using where
3 rows in set (0.00 sec)

I presume that e.id is not being used because I'm not specifically 
querying against it. Instead, I'm using expression_expression's FKs.

Which gives me an idea. I can add expression a 2nd time to the FROM clause:

mysql db_lexi > EXPLAIN SELECT e.id, e.lang_id, e.term
     -> FROM (expression AS e, expression AS e_pk)
     -> LEFT JOIN expression_expression AS ee1
     -> ON ee1.expression1_id = e.id AND ee1.deleted_at = 0
     -> LEFT JOIN expression_expression AS ee2
     -> ON ee2.expression2_id = e.id AND ee2.deleted_at = 0
     -> WHERE
     -> (ee1.expression2_id = e_pk.id AND ee2.expression1_id = 223363)
     -> OR (ee1.expression1_id = e_pk.id AND ee1.expression2_id = 223363)
     -> AND e.original_id IS NULL
     -> AND e.deleted_at = 0\G
*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: ee1
          type: ALL
possible_keys: expression2_id_idx,expression1_id_idx
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 106191
         Extra: Using where
*************************** 2. row ***************************
            id: 1
   select_type: SIMPLE
         table: e
          type: eq_ref
possible_keys: PRIMARY,original_id_idx,deleted_at_idx
           key: PRIMARY
       key_len: 8
           ref: db_lexi.ee1.expression1_id
          rows: 1
         Extra:
*************************** 3. row ***************************
            id: 1
   select_type: SIMPLE
         table: ee2
          type: ref
possible_keys: expression2_id_idx
           key: expression2_id_idx
       key_len: 8
           ref: db_lexi.ee1.expression1_id
          rows: 1
         Extra: Using where
*************************** 4. row ***************************
            id: 1
   select_type: SIMPLE
         table: e_pk
          type: ALL
possible_keys: PRIMARY
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 121120
         Extra: Range checked for each record (index map: 0x1)
4 rows in set (0.00 sec)


But this doesn't feel like an elegant solution. Regardless, I'm still 
seeing the query take ~2.5sec. I'm just looking into the "Range checked 
for each record" msg now. Perhaps this is the right direction but 
requires a little tweaking.


I don't understand why deleted_at_idx is also not used, though. Perhaps 
because I'm only looking for values of 0? Regardless, that doesn't seem 
to be the heart of the problem.

BTW, I inherited the DB, so can't be sure whether I've missed anything.
Thread
alternative to slow querybrian3 Jul
  • Re: alternative to slow queryyoku ts3 Jul
    • Re: alternative to slow querybrian3 Jul
      • RE: alternative to slow queryBenjamin Stillman3 Jul
        • Re: alternative to slow querybrian3 Jul
          • RE: alternative to slow queryBenjamin Stillman3 Jul
            • Re: alternative to slow querybrian3 Jul
              • RE: alternative to slow queryRick James16 Jul
                • Re: alternative to slow querybrian17 Jul