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
