Hello, I have several tables storing item information, keyword (mainly for searching), category and subcategory (also for searching). The query I am using now is:SELECT i.*, it.*, ic.*, c.*, cs.*, s.*, st.* FROM item i LEFT JOIN iteminfo it ON i.id = it.id LEFT JOIN itemkeyword ik ON i.id = ik.id LEFT JOIN state st ON it.state = st.id LEFT JOIN itemcategory ic ON i.id = ic.id LEFT JOIN subcategory s ON ic.sid = s.id LEFT JOIN catsubcat cs ON cs.sid = s.id LEFT JOIN category c ON c.id = cs.cid WHERE (((UCASE(i.english) LIKE UCASE('% bank %') OR UCASE(i.desceng) LIKE UCASE('% bank %') OR UCASE(s.english) LIKE UCASE('% bank %') OR UCASE(c.english) LIKE UCASE('% bank %') OR UCASE(it.street1) LIKE UCASE('% bank %') OR UCASE(it.street2) LIKE UCASE('% bank %') OR UCASE(it.garden) LIKE UCASE('% bank %'))) OR ((UCASE(i.english) LIKE UCASE('bank %') OR UCASE(i.desceng) LIKE UCASE('bank %') OR UCASE(s.english) LIKE UCASE('bank %') OR UCASE(c.english) LIKE UCASE('bank %') OR UCASE(it.street1) LIKE UCASE('bank %') OR UCASE(it.street2) LIKE UCASE('bank %') OR UCASE(it.garden) LIKE UCASE('bank %'))) OR ((UCASE(i.english) LIKE UCASE('% bank') OR UCASE(i.desceng) LIKE UCASE('% bank') OR UCASE(s.english) LIKE UCASE('% bank') OR UCASE(c.english) LIKE UCASE('% bank') OR UCASE(it.street1) LIKE UCASE('% bank') OR UCASE(it.street2) LIKE UCASE('% bank') OR UCASE(it.garden) LIKE UCASE('% bank'))) OR (UCASE(ik.keyword) LIKE UCASE('%bank%'))) AND i.duedate > 1182339575 GROUP BY it.iid ORDER BY i.credit DESC, it.hits DESC, i.english LIMIT 0, 10;
You can drop all the UCASE's as LIKE is case insensitive. That should help a little bit as it won't have to upper case all the fields in your where clause.
Here is the EXPLAIN table: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE i ALL NULL NULL NULL NULL 1820 Using where; Using temporary; Using filesort 1 SIMPLE it ref id id 8 item.i.id 19 1 SIMPLE ik ref id id 8 item.i.id 19 1 SIMPLE st eq_ref PRIMARY PRIMARY 2 item.it.state 1 1 SIMPLE ic ref id id 8 item.i.id 19 1 SIMPLE s eq_ref PRIMARY PRIMARY 4 item.ic.sid 1 1 SIMPLE cs ref sid sid 4 item.s.id 2 1 SIMPLE c eq_ref PRIMARY PRIMARY 3 item.cs.cid 1 Using where Now I have the questions below: 1) Is it possible to shorten the WHERE clause? 2) Sometimes, the keyword that I use to search takes a long time ( over 6 seconds). What is the main problem causing this problem? 3) If I would like to sort the data by the `category.english` (if the keyword found in category english name) and then following by the other criteria, how do I write the ORDER BY clause? Thank you very much for your help~ 22-06-2007
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
