From: Rick James Date: July 16 2012 10:57pm Subject: RE: alternative to slow query List-Archive: http://lists.mysql.com/mysql/227835 Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148892B773@SP2-EX07VS01.ds.corp.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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,e= 1). 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 >=20 > On 12-07-03 02:18 PM, Stillman, Benjamin wrote: > > Not sure why it wouldn't show primary as a possible key then... >=20 > Yes, that seems rather strange. >=20 >=20 > > 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 =3D (insert a random, > > valid id value here); > > > > Does it use a key then? Or at least show primary as a possible key? >=20 > mysql db_lexi > EXPLAIN SELECT id, term, lang_id FROM expression WHERE > id =3D 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) >=20 >=20 > 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. >=20 > 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 =3D e.id AND ee1.deleted_at =3D 0 > -> LEFT JOIN expression_expression AS ee2 > -> ON ee2.expression2_id =3D e.id AND ee2.deleted_at =3D 0 > -> WHERE (ee2.expression1_id =3D 223363 OR ee1.expression2_id =3D > 223363) > -> AND e.original_id IS NULL > -> AND e.deleted_at =3D 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) >=20 > 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. >=20 > Which gives me an idea. I can add expression a 2nd time to the FROM > clause: >=20 > 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 =3D e.id AND ee1.deleted_at =3D 0 > -> LEFT JOIN expression_expression AS ee2 > -> ON ee2.expression2_id =3D e.id AND ee2.deleted_at =3D 0 > -> WHERE > -> (ee1.expression2_id =3D e_pk.id AND ee2.expression1_id =3D 223363= ) > -> OR (ee1.expression1_id =3D e_pk.id AND ee1.expression2_id =3D > 223363) > -> AND e.original_id IS NULL > -> AND e.deleted_at =3D 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) >=20 >=20 > 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. >=20 >=20 > 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. >=20 > BTW, I inherited the DB, so can't be sure whether I've missed anything. >=20 > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql