From: Rick James (rjamesyahoo-inc.com)
Date: Mon Jul 16 2012 - 17:57:32 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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-listlogi.ca]
> Sent: Tuesday, July 03, 2012 12:50 PM
> To: mysqllists.mysql.com
> 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]