[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: How to optimize this long query
From:       82.2.138.93
Date:       2007-06-28 16:32:21
Message-ID: 20070628174145.26240.qmail () lists ! mysql ! com
[Download RAW message or body]

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/mysql?unsub=mysql-marcsub@progressive-comp.com



[prev in list] [next in list] [prev in thread] [next in thread]