At 09:15 AM 7/14/2006, Jeremiah Foster wrote:
Hello,
Nearly once or twice a day the mysql daemon on our FreeBSD server has to
be restarted since it is taking up most of the processor.
One of the entries in our slow_queries log looks like this;
# Query_time: 14074 Lock_time: 0 Rows_sent: 11 Rows_examined:
1020472928
SELECT pp.art_num, pp.showprice as price, pc.custom_price,
pc.custom_name, pc.custom_delivery, pd.instock, Stdnsv.Description FROM
product_tags pt, products_prices pp, products_dists pd,
filter_categories fc, product_linking pl LEFT JOIN cds_Stdnsv Stdnsv ON
(Stdnsv.ProdID = pp.art_num) LEFT JOIN products_custom pc ON (pc.art_num
= pp.art_num) WHERE pp.art_num = pl.art_num AND pp.art_num = pd.art_num
AND fc.filter_type_id = 1 AND fc.filter_id = pl.filter_id AND
(Stdnsv.Description != 'None' OR Stdnsv.Description != '') AND
(Stdnsv.Description LIKE '%Media%Center%' OR pl.art_num LIKE '%Media%
Center%') GROUP BY pp.art_num ORDER BY pp.showprice DESC LIMIT 100;
That is about 3 hours and a billion rows examined. Is it likely that
this is the source of my problems?
Thanks very much,
Jeremiah
Jeremiah,
I'd say YES. But first, how much time does it take if you remove the join
conditions for the LIKEs?
There are two difficulties there:
1. Use of LIKE with wildcard search forces a table scan, although
see [1] below.
2. The two LIKEs are joined by an OR so
Stdnsv will be scanned until the first condition is met,
and if it is fulfilled, then
pl is scanned until the second condition is met. Full scan
on each table.
Alternately ...
If the first condition for Stdnsv is not met, then OR has
failed, but you will have a full table scan on the left hand side of the OR
regardless.
Second, have you indexes on the columns which are being compared?
Your memory usage is probably pretty high as well, as MySQL tries to do as
much work as it can in memory.
Have you tried this:
SELECT pp.art_num, pp.showprice as price, pc.custom_price,
pc.custom_name, pc.custom_delivery, pd.instock, Stdnsv.Description
FROM product_tags pt, products_prices pp, products_dists pd,
filter_categories fc, product_linking pl, cds_Stdnsv Stdnsv
WHERE
Stdnsv.ProdID = pp.art_num AND
pc.art_num = pp.art_num AND
pp.art_num = pl.art_num AND
pp.art_num = pd.art_num AND
fc.filter_type_id = 1 AND
fc.filter_id = pl.filter_id AND
(Stdnsv.Description != 'None' OR Stdnsv.Description != '')
GROUP BY pp.art_num
ORDER BY pp.showprice DESC LIMIT 100;
If that gives weird or unexpected results, try whacking off AND conditions
until it's fast, then think about why the query slows when they are
added. Note the simple WHERE is an implicit LEFT JOIN.
Also formulate time query, and time it, with " (Stdnsv.Description LIKE
'%Media%Center%' OR pl.art_num LIKE '%Media%
Center%') " as the WHERE.
Apologies if this has been too pedantic.
Hope this is helpful - Miles Thompson
[1] MySQL 4.0 does another optimisation on LIKE. If you use ... LIKE
"%string%" and string is longer than 3 characters, MySQL will use the Turbo
Boyer-Moore algorithm to initialise the pattern for the string and then use
this pattern to perform the search quicker.
This is taken from http://mysqld.active-venture.com/MySQL_indexes.html
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.10.0/388 - Release Date: 7/13/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
