I have a table that joins on itself through a second table:
table expression:
id INT PRIMARY KEY,
lang_id INT
term VARCHAR(128)
table expression_expression:
id INT PRIMARY KEY
expression1_id INT
expression2_id INT
In order to find associated records, I had originally used a UNION,
which worked very well. However, the application is written in PHP and
uses PDO. PDOStatement::getColumnMeta() doesn't return anything for the
table name with a UNION and this is crucial to the application. So I've
come up with the following substitute:
SELECT e.id, e.lang_id, e.term
FROM expression AS e
LEFT JOIN expression_expression AS ee1
ON ee1.expression1_id = e.id
LEFT JOIN expression_expression AS ee2
ON ee2.expression2_id = e.id
WHERE
ee2.expression1_id = $ID
OR
ee1.expression2_id = $ID
This gives me the correct values but is rather (~2-4 sec) slow. Here's
the EXPLAIN output:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: e
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 95127
Extra:
*************************** 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)
Can someone suggest a better approach?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
