On Fri, 2006-07-14 at 09:47 -0300, Miles Thompson wrote:
At 09:15 AM 7/14/2006, Jeremiah Foster wrote:
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.
That is about 3 hours and a billion rows examined. Is it likely that
this is the source of my problems?
I'd say YES. But first, how much time does it take if you remove the join
conditions for the LIKEs?
The time it takes after removing the two likes is 7 hours;
100 rows in set (7 hours 23 min 43.04 sec)
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?
Yes, I believe when I ran EXPLAIN it showed the tables were indexed. I
will run explain again to make sure.
Your memory usage is probably pretty high as well, as MySQL tries to do as
much work as it can in memory.
I will look into investing in more 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
Miles, this has been extremely helpful, not at all pedantic. Thank you.
[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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
