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
