SELECT * FROM table_one t1 INNER JOIN table_two t2 ON t1.column_one = t2.column_one LEFT JOIN table_three t3 ON t3.column_two = t1.column_three AND t3.column_four = t1.column_five WHERE column_six LIKE '%dsc%' AND column_seven LIKE '%aaa%';
There is no need for a derived table. Also, using LIKE '%xxx%' prohibits indexes on column_six and column_seven from being used. Also, typically, when doing a LEFT JOIN to a table, that table is used on the *right* side of the ON expression. In your SQL, it is on the left side, which doesn't make much sense. I think you mean for it to be on the right... Cheers, Jay wangxu wrote: > sql: > select * > from table_one inner join table_two on table_two.column_one = > table_one.column_one > left join (SELECT * from table_three) table_four > on table_four.column_two = table_one.column_three and > table_four.column_four= table_one.column_five > where column_six like '%dsc%' and column_seven like '%aaa%' > > explain: > > *************************** 1. row *************************** > id: 1 > select_type: PRIMARY > table: table_one > type: ALL > possible_keys: > key: NULL > key_len: NULL > ref: NULL > rows: 481 > Extra: Using where > *************************** 2. row *************************** > id: 1 > select_type: PRIMARY > table: table_two > type: ref > possible_keys: idx_column_one > key: idx_column_one > key_len: 153 > ref: table_one.column_one > rows: 1 > Extra: Using where > *************************** 3. row *************************** > id: 1 > select_type: PRIMARY > table: <derived2> > type: ALL > possible_keys: NULL > key: NULL > key_len: NULL > ref: NULL > rows: 2297 > Extra: > *************************** 4. row *************************** > id: 2 > select_type: DERIVED > table: table_three > type: ALL > possible_keys: NULL > key: NULL > key_len: NULL > ref: NULL > rows: 2250 > Extra: > > > > Can I optimize this sql ? > thanks! > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
