One more suggestion is, using LIKE to search (whatever your column is indexed or not) is definitely slow than using "=". I try it in 0.38 M records before.

-----Original Message----- From: Tony Shiu [mailto:to...@cyberbox.com] Sent: Thursday, March 15, 2001 11:21 AM To: hwy...@ywesee.com; mys...@lists.mysql.com Subject: RE: Very (excruciatingly) slow select

Hi Hannes

Several approaches to achieve your work. One is upgrade your resource i.e. CPU, memory... I do not take this. Another is check all columns used inside WHERE clause whether they are indexed. If not, pls create indexes for them. it will definitely smooth better.

Programmatically, I suggest you try to divide your query into several small queries. According to your query below, you are likely to join about 10 tables. As data grows, join those tables will produce undetermined large set of data.

Tony

-----Original Message----- From: Hannes Wyss [mailto:hwy...@ywesee.com] Sent: Wednesday, March 14, 2001 6:29 PM To: mys...@lists.mysql.com Subject: Very (excruciatingly) slow select

Hi List, I'm new to the list and I have a BrainTeaser for all of you.

I have yet lots to learn about sql and mySQL so any suggestions are very welcome!

Thanx in advance

Hannes

I have following select over a Database that I cannot setup myself, but have to take on as is:

SELECT COMP.CompKey, COMP.NameLong, COMP.Internet, COMP.EMAIL, ACCOMP.Phar, ACCOMP.CompKey, ACCOMP.Role, AC.Phar, AC.IKSCat, AC.GenericCode, AC.InsCode, AC.GrdFrCode, AC.WWW, AC.NumOfPce, AC.SaleCode, ACMED.Phar, ACMED.ATCKey, ACMED.FormCode, ACNAM.Phar, ACNAM.LangCode, ACNAM.Name50, ACSC.Phar, ACSC.StoLNr, ACSC.StoKeyG, ACSC.Quantity, ACSC.QuantityUnit, ACSC.CodeWHK, SC.StoKeyG, SC.StoName, ACPRICEALG.Phar, ACPRICEALG.PriceType, ACPRICEALG.Price, CODES.CodeType, CODES.CodeValue, CODES.LangCode, CODES.CodeDesc AS GalenicForm, CLASS.CodeType, CLASS.CodeValue, CLASS.LangCode, CLASS.CodeDesc AS ATCClass FROM AC, ACMED, ACNAM, ACCOMP, COMP, ACSC, SC, ACPRICEALG, CODES, CODES AS CLASS WHERE COMP.NameLong LIKE '<<<<<<<<<<Here Come the SearchTerms>>>>>>>>>>>>>>>%' AND ACCOMP.CompKey=COMP.CompKey AND ACCOMP.Role='H' AND AC.Phar=ACCOMP.Phar AND AC.SaleCode!='H' AND ACMED.Phar=ACCOMP.Phar AND ACNAM.Phar=ACCOMP.Phar AND ACNAM.LangCode='D' AND ACSC.Phar=ACCOMP.Phar AND ACSC.CodeWHK='W' AND SC.StoKeyG=ACSC.StoKeyG AND ACPRICEALG.Phar=ACMED.Phar AND ACPRICEALG.PriceType='PPUB' AND CODES.CodeType=5 AND CODES.CodeValue=ACMED.FormCode AND CODES.LangCode='D' AND CLASS.CodeType=3 AND CLASS.CodeValue=ACMED.ATCKey AND CLASS.LangCode='D' ORDER BY ACMED.ATCKey, ACPRICEALG.Price, ACMED.Phar, ACSC.StoLNr

EXPLAIN says: +-Table------+-Type---+-Possible Keys---+-Key----------+-Key-len---+-References---+-Rows---+-Extra------- + +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | COMP | range | PRIMARY, | NameLong | | | 1 | | | | | NameLong | | | | | | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | CLASS | ref | PRIMARY | PRIMARY | 4 | ??? | 159 | where used | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | SC | ALL | PRIMARY | | | | 6710 | | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | ACSC | ref | PRIMARY,Phar, | StoKeyG | 5 | SC.StoKeyG, | 14 | | | | | StoKeyG | | | W | | | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | ACCOMP | eq_ref | PRIMARY,CompKey | PRIMARY | 9 | ACSC.Phar, | 1 | where used | | | | | | | COMP.CompKey,| | | | | | | | | H | | | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | AC | eq_ref | PRIMARY | PRIMARY | 4 | ACCOMP.Phar | 1 | where used | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | ACNAM | eq_ref | PRIMARY | PRIMARY | 5 | ACCOMP.Phar, | 1 | where used | | | | | | | D | | | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | ACMED | eq_ref | PRIMARY,ATCKey | PRIMARY | 4 | ACCOMP.Phar | 1 | where used | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | CODES | range | PRIMARY | PRIMARY | | | 178 | | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+ | ACPRICEALG | eq_ref | PRIMARY | PRIMARY | 8 | ACMED.Phar, | 1 | where used | | | | | | | PPUB | | | +------------+--------+-----------------+--------------+-----------+---- ----------+--------+-------------+

That did not look too bad to me, but still the query takes so long to execute, the client-browser times out. So far I'm not even sure I'll get results, apart from the fact that I don't get a Syntax Error...

Table Sizes: table Records AC 83674 ACCOMP 242924 ACMED 13605 ACNAM 167348 ACPRICEALG 191212 ACSC 53514 CODES 15974 COMP 2065 SC 6710

Expected Result-Sets: Anything from 2 to 1000

suggestions?

-- mit freundlichen Grüssen / best regards

Hannes Wyss XML Developer

+41 1 350 85 86

www.ywesee.com > intellectual capital connected > www.faeh-wuest.ch

--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <mysq...@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-tonyshiu=dotc...@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

To request this thread, e-mail <mysq...@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-tonyshiu=dotc...@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php