MySQL Forums
Forum List  »  Newbie

SQL long Join statement and performance
Posted by: daniel futurebase
Date: February 17, 2010 11:24AM


i have a table with id, participant_id, varname, varname_value (1, 1, 'servwww', '1')

where a bunch of variables and values of variables per participant are stored.
Variable names are 'servhotel' or 'servwww' for example.

Now i have to reformat it to a table like

participant_id, servwww, servhotel ... and so on...
(1, '1', '1' .... )

i use the following sql statement to do the reformating with 9 variables. That works quite good for 9 variables (12 sec. for 4 records per variable). But if i use a longer sql statement for 10 variables it takes 230 sec. .. why? Can someone give me a hint you i can avoid that?

SELECT varX.IsParticipant_id, varX.CSVData as 'servhotel' , 
                              var0.CSVData as 'servpers' , 
                              var1.CSVData as 'servwww' , 
                              var2.CSVData as 'servprice' , 
                              var3.CSVData as 'servoffers' , 
                              var4.CSVData as 'servshopping' , 
                              var5.CSVData as 'servamb' , 
                              var6.CSVData as 'servalternative' , 
                              var7.CSVData as 'servgastronom' , 
                              var8.CSVData as 'servnature' , 
                              var9.CSVData as 'servcosi' FROM base_data as varX  
INNER JOIN `base_data` AS var0 ON ( varX.IsParticipant_id = var0.IsParticipant_id ) 
INNER JOIN `base_data` AS var1 ON ( varX.IsParticipant_id = var1.IsParticipant_id ) 
INNER JOIN `base_data` AS var2 ON ( varX.IsParticipant_id = var2.IsParticipant_id ) 
INNER JOIN `base_data` AS var3 ON ( varX.IsParticipant_id = var3.IsParticipant_id ) 
INNER JOIN `base_data` AS var4 ON ( varX.IsParticipant_id = var4.IsParticipant_id ) 
INNER JOIN `base_data` AS var5 ON ( varX.IsParticipant_id = var5.IsParticipant_id ) 
INNER JOIN `base_data` AS var6 ON ( varX.IsParticipant_id = var6.IsParticipant_id ) 
INNER JOIN `base_data` AS var7 ON ( varX.IsParticipant_id = var7.IsParticipant_id ) 
INNER JOIN `base_data` AS var8 ON ( varX.IsParticipant_id = var8.IsParticipant_id ) 
INNER JOIN `base_data` AS var9 ON ( varX.IsParticipant_id = var9.IsParticipant_id ) 
INNER JOIN `base_participant` ON ( varX.IsParticipant_id = ) 

WHERE varX.Varname = 'servhotel' 
AND var0.Varname = 'servpers'
AND var1.Varname = 'servwww'
AND var2.Varname = 'servprice'
AND var3.Varname = 'servoffers'
AND var4.Varname = 'servshopping'
AND var5.Varname = 'servamb'
AND var6.Varname = 'servalternative'
AND var7.Varname = 'servgastronom'
AND var8.Varname = 'servnature'
AND var9.Varname = 'servcosi' 
AND base_participant.IsProject_id = 3  

ORDER BY varX.IsParticipant_id DESC

Edited 1 time(s). Last edit at 02/17/2010 11:26AM by daniel futurebase.

Options: ReplyQuote

Written By
SQL long Join statement and performance
February 17, 2010 11:24AM

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.