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-l...@logi.ca] > Sent: Tuesday, July 03, 2012 12:50 PM > To: mysql@lists.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
