Kwang Chin Lee wrote:
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
To answer point 2:
Firstly, as previously stated by someone else you need to ditch the
UCASE, performing functions on indexes pretty much voids the index.
Secondly if you have a % before a word then you are going to see a
performance hit. In your case a fulltext index and query might be more
appropriate (I don't know without seeing the schema). Thirdly you need
to make sure all the group by clauses are indexed, or it will have to
use a temporary table and filesort.
Although for optimum performance you may find your entire strategy
behind this needs rethinking.
--
Andrew Hutchings - LinuxJedi - http://www.linuxjedi.co.uk/
Windows is the path to the darkside...Windows leads to Blue Screen. Blue
Screen leads to downtime. Downtime leads to suffering...I sense much
Windows in you...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
