List:General Discussion« Previous MessageNext Message »
From:Rick James Date:July 16 2012 10:57pm
Subject:RE: alternative to slow query
View as plain text  
Please provide SHOW CREATE TABLE for the two tables.

Plan A:
Would the anti-UNION problem be solved by hiding the UNION in a subquery?  The outer query
would simply return what the UNION found.

Plan B:
Insert every row twice into expression_expression -- (e1,e2) and also (e2,e1).
Then, you need only one index into that table, and you don't need to UNION (or the LEFT
JOINs).

Plan C:
Do something with a VIEW.  Caution:  Performance _may_ be even worse.

> -----Original Message-----
> From: brian [mailto:mysql-list@stripped]
> Sent: Tuesday, July 03, 2012 12:50 PM
> To: mysql@stripped
> Subject: Re: alternative to slow query
> 
> 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.
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

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