Hello,
add index to expression1_id and expression2_id on expression_expression.
it doesn't use index,following,
> WHERE
> ee2.expression1_id = $ID
> OR
> ee1.expression2_id = $ID
regards,
2012/7/3 brian <mysql-list@stripped>
> 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
>
>
