View Full Version : Avoiding mysql slow queries

03-20-2007, 02:11 PM
I have a mysql database
Our server is: 64 bit processor server running 4 CPUs with 4 GB of RAM...

when I checked mysql slow queries log, I found so many lines like this with query _time around 6 to 12 for most of the queries:

# Tue Mar 20 03:02:33 2007
# Query_time: 12 Lock_time: 0 Rows_sent: 1 Rows_examined: 1
use trials_data;
SELECT id, site, rank, title, description, keywords, category, reviews, ratings, clicks FROM table WHERE site = 'firstsite' LIMIT 1
# Tue Mar 20 05:01:21 2007
# Query_time: 4 Lock_time: 0 Rows_sent: 10 Rows_examined: 5210
use trials_data;
SELECT DISTINCT site FROM table WHERE ( site='shopping' OR title LIKE '%shopping%' OR keywords LIKE '%shopping%' OR description LIKE '%shopping%') ORDER BY rank LIMIT 40, 10
# Tue Mar 20 05:08:36 2007
# Query_time: 17 Lock_time: 7 Rows_sent: 1 Rows_examined: 5012
use trials_data;
SELECT COUNT(DISTINCT site) FROM cb_urls15 WHERE ( site='recipes' OR title LIKE '%recipes%' OR keywords LIKE '%recipes%' OR description LIKE '%recipes%')
id=primary key

title, description, keyword, reviews are 'text' with around 300 characters each.
others are of VARCHAR

I dont understand why I am getting these queries slow though Rows_sent: 1 and Rows_examined: 1

Please inform me how to optimize these queries.

Thank you.

03-22-2007, 10:25 PM
site='recipes' OR title LIKE '%recipes%' OR keywords LIKE '%recipes%' OR description LIKE '%recipes%'

That's bad right there.

When you use LIKE on a text field that has a lot of text in it (300 is a good chunk), with 5000 rows it has to go through each field, in each row, for each LIKE.

If you're trying to do searches, google for 'mysql fulltext'.

03-23-2007, 01:29 PM
Thank you Aedrin,
I have implemented your suggestion, and now my site is running well without considerable slow queries.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum