List:General Discussion« Previous MessageNext Message »
From:brian Date:July 3 2012 1:26am
Subject:alternative to slow query
View as plain text  
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?
Thread
alternative to slow querybrian3 Jul
  • Re: alternative to slow queryyoku ts3 Jul
    • Re: alternative to slow querybrian3 Jul
      • RE: alternative to slow queryBenjamin Stillman3 Jul
        • Re: alternative to slow querybrian3 Jul
          • RE: alternative to slow queryBenjamin Stillman3 Jul
            • Re: alternative to slow querybrian3 Jul
              • RE: alternative to slow queryRick James16 Jul
                • Re: alternative to slow querybrian17 Jul