MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query optimisation advice
Posted by: Rick James
Date: January 10, 2015 01:22AM

You have
Please use aliases and tag each field so we know which field comes from which table:
SELECT  lga_name,lga_code,lga.postcode,
        s.ratio,
        COUNT(DISTINCT s.participant_id),
        COUNT(DISTINCT s.participant_id)*lga.ratio AS postcode_total,
        COUNT(DISTINCT CASE WHEN s.`year` = 2010 THEN s.participant_id END)*lga.ratio as postcode_total_start_year,
        COUNT(DISTINCT CASE WHEN s.`year` = 2013 THEN s.participant_id END)*lga.ratio as postcode_total_end_year
    FROM  ssa_participant AS s
    LEFT JOIN  abs_postcode_lga AS lga ON lga.postcode = s.postcode
    WHERE  s.sport_id_fk = 18
      AND  s.`year` >= 2010
      AND  s.`year` <= 2013
      AND  lga.postcode IS NOT NULL
    GROUP BY  lga_name,lga_code,lga.postcode, lga.ratio
Remove the "LEFT" and "AND lga.postcode IS NOT NULL" since a plain JOIN means just that.

Add a compound index that matches the WHERE clause:
INDEX(sport_id_fk, `year`)
(and DROP the index on sport_id_fk because it will not longer be useful.)

Add a PRIMARY KEY to ssa_participant. Is there some 'natural' combination of fields that is UNIQUE?

Use the YEAR datatype, not VARCHAR.

`age` should be computed, not stored. (assuming it changes daily!)

How many distinct values are there for `year`? Probably so few that the index on that field will never be used; DROP it.

What version of MySQL are you using?

What is the value of innodb_buffer_pool_size? How much RAM do you have?

Options: ReplyQuote


Subject
Views
Written By
Posted
2289
January 08, 2015 11:02PM
Re: Query optimisation advice
1183
January 10, 2015 01:22AM
1102
January 11, 2015 05:50PM
1181
January 12, 2015 10:35PM


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.