MySQL Forums
Forum List  »  Newbie

Re: SQL long Join statement and performance
Posted by: Rick James
Date: February 20, 2010 10:59AM

1. Key-Value is a very flexible what to design a schema. But it is terribly inefficient.

2. SHOW CREATE TABLE -- I need to see what indexes you have to see if they can be improved. Probably need compound INDEX(IsParticipant_id, Varname) and INDEX(IsProject_id, IsParticipant_id).

3. Normalization will only make things worse (by doubling the number of JOINs).

4. Subqueries might work faster:
SELECT 
   varX.IsParticipant_id,
   varX.CSVData as 'servhotel' , 
   var0.CSVData as 'servpers' , 
   ( SELECT CSVData FROM base_data
           WHERE CSVData as 'servpers'
             AND varX.IsParticipant_id = IsParticipant_id) AS var0,
   ( SELECT ... ) AS var1,
   ...
   FROM base_participant
   WHERE IsProject_id = 3  
   ORDER BY IsParticipant_id DESC

Options: ReplyQuote


Subject
Written By
Posted
Re: SQL long Join statement and performance
February 20, 2010 10:59AM


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.