MySQL Forums
Forum List  »  German

Re: Abfrage in einer Hierarchie
Posted by: Gerald Koch
Date: September 20, 2010 04:11AM

Hallo Thomas,

danke dir für die antwort...
also der letzte stand ist der mit dem join von dir. dauert leider 1,7 sekunden :-(
SELECT * FROM kunde
WHERE kunde_TYPE_ID = 3               
AND kunde_CATEGORY_TYPE_ID = 3000     
AND kunde_HIDDEN = 0
AND kunde_ID IN 
(
    SELECT q1.kunde_ID 
    FROM
    (
        SELECT kunde_ID FROM waren_assoc_all
        WHERE warenkorb_TYPE_ID = 1
        AND ware_TYPE_ID=104 #gemüse
        AND (ware_STRING='Kartoffel' OR ware_STRING='Karotte')
    ) q1
     JOIN(
        SELECT kunde_ID FROM waren_assoc_all
        WHERE warenkorb_TYPE_ID = 1  
        AND ware_TYPE_ID=109 #obst
        AND (ware_STRING='Apfel')
    ) q2
    ON q1.kunde_ID=q2.kunde_ID
    
    JOIN(
        SELECT kunde_ID FROM waren_assoc_all
        WHERE warenkorb_TYPE_ID = 1            
        AND ware_TYPE_ID=113 #Haushaltswaren
        AND (ware_STRING='Topf' OR ware_STRING='Schüssel' OR ware_STRING='Messer')
    ) q3
    ON q2.kunde_ID=q3.kunde_ID
    
    JOIN(
        SELECT kunde_ID FROM waren_assoc_all
        WHERE warenkorb_TYPE_ID = 1             #Equipment
        AND ware_TYPE_ID=431 #Elektronikwaren
        AND (ware_STRING='Radio')
    ) q4
    ON q3.kunde_ID=q4.kunde_ID
)

Der hier kommt auf 80ms:
SELECT * FROM kunde
WHERE kunde_TYPE_ID = 3               
AND kunde_CATEGORY_TYPE_ID = 3000     
AND kunde_HIDDEN = 0
AND kunde_ID IN 
(
        SELECT kunde_ID FROM waren_assoc_all
        WHERE warenkorb_TYPE_ID = 1
        AND ware_TYPE_ID=104 #gemüse
        AND (ware_STRING='Kartoffel' OR ware_STRING='Karotte')  
) 
AND YP_INSTANCE_ID IN 
(
        SELECT kunde_ID FROM waren_assoc_all
        WHERE warenkorb_TYPE_ID = 1  
        AND ware_TYPE_ID=109 #obst
        AND (ware_STRING='Apfel')  
) 
AND YP_INSTANCE_ID IN 
(
        SELECT kunde_ID FROM waren_assoc_all
        WHERE warenkorb_TYPE_ID = 1            
        AND ware_TYPE_ID=113 #Haushaltswaren
        AND (ware_STRING='Topf' OR ware_STRING='Schüssel' OR ware_STRING='Messer')
) 
AND YP_INSTANCE_ID IN 
(
        SELECT kunde_ID FROM waren_assoc_all
        WHERE warenkorb_TYPE_ID = 1             #Equipment
        AND ware_TYPE_ID=431 #Elektronikwaren
        AND (ware_STRING='Radio')
)

sind die laufzeiten komisch?
vielleicht fällt mir beim mittagessen was ein. bis später!

Options: ReplyQuote


Subject
Views
Written By
Posted
2432
September 14, 2010 09:24AM
1379
September 17, 2010 02:13AM
1263
September 17, 2010 06:27AM
1242
September 17, 2010 11:11AM
1342
September 17, 2010 11:56AM
1204
September 20, 2010 03:08AM
1268
September 20, 2010 03:48AM
Re: Abfrage in einer Hierarchie
1157
September 20, 2010 04:11AM
1215
September 20, 2010 04:49AM
1207
September 20, 2010 05:22AM
1303
September 20, 2010 05:30AM
1319
September 20, 2010 05:54AM
1301
September 20, 2010 06:45AM
1584
September 20, 2010 07:07AM
1406
September 20, 2010 07:15AM
1291
September 20, 2010 07:24AM
1291
September 20, 2010 10:26AM
1254
September 20, 2010 10:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.