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]
