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:[EMAIL PROTECTED]]
Sent: Thursday, March 15, 2001 11:21 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
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:[EMAIL PROTECTED]]
Sent: Wednesday, March 14, 2001 6:29 PM
To: [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
