List:General Discussion« Previous MessageNext Message »
From:yoku ts Date:July 3 2012 1:33am
Subject:Re: alternative to slow query
View as plain text  
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
>
>

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