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;
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
