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-l...@logi.ca> > 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 > >
