Not the answer you need?
Register and ask your own question!

optimize sql query

OualidOualid EntrantInactive User Role Beginner
I have the following query that takes almost 30s to execute.

The table techno_tire_hjul has around 1700 entries.

I am using inner join extensively.

Any tips on how to modify the sql to improve the execution time?
select
                `techno_tire_hjul`.`id`,
                `techno_tire_hjul`.`datum`,
                `techno_tire_lager`.`namn` as Lager,
                `techno_tire_hjul`.`lagerplats`,
                `techno_tire_hjul`.`vinter_sommar_dack`,
                `techno_tire_kategori`.`namn` as Kategori,
                `techno_tire_dacktyp`.`namn` as Dacktyp1,
                `techno_tire_dacktyp_1`.`namn` as Dacktyp2,
                `techno_tire_dacktyp_2`.`namn` as Dacktyp3,
                `techno_tire_dacktyp_3`.`namn` as Dacktyp4,
                `techno_tire_fabrikat`.`namn` as Fabrikat1,
                `techno_tire_fabrikat_1`.`namn` as Fabrikat2,
                `techno_tire_fabrikat_2`.`namn` as Fabrikat3,
                `techno_tire_fabrikat_3`.`namn` as Fabrikat4,
                `techno_tire_hjul`.`dimension_diameter1` as Diameter1,
                `techno_tire_hjul`.`dimension_diameter2` as Diameter2,
                `techno_tire_hjul`.`dimension_diameter3` as Diameter3,
                `techno_tire_hjul`.`dimension_diameter4` as Diameter4,
                `techno_tire_hjul`.`dimension_profil1` as Profil1,
                `techno_tire_hjul`.`dimension_profil2` as Profil2,
                `techno_tire_hjul`.`dimension_profil3` as Profil3,
                `techno_tire_hjul`.`dimension_profil4` as Profil4,
                `techno_tire_hjul`.`dimension_bredd1` as Bredd1,
                `techno_tire_hjul`.`dimension_bredd2` as Bredd2,
                `techno_tire_hjul`.`dimension_bredd3` as Bredd3,
                `techno_tire_hjul`.`dimension_bredd4` as Bredd4,
                
                `techno_tire_hjul`.`lo1` as LoadIndex1,
                `techno_tire_hjul`.`lo2` as LoadIndex2,
                `techno_tire_hjul`.`lo3` as LoadIndex3,
                `techno_tire_hjul`.`lo4` as LoadIndex4,
                
                `techno_tire_si`.`namn` as SpeedIndex1,
                `techno_tire_si_2`.`namn` as SpeedIndex2,
                `techno_tire_si_3`.`namn` as SpeedIndex3,
                `techno_tire_si_4`.`namn` as SpeedIndex4,
                
                `techno_tire_hjul`.`rof1` as RunOnFlat1,
                `techno_tire_hjul`.`rof2` as RunOnFlat2,
                `techno_tire_hjul`.`rof3` as RunOnFlat3,
                `techno_tire_hjul`.`rof4` as RunOnFlat4,
                
                `techno_tire_hjul`.`monsterdjup1`,
                `techno_tire_hjul`.`monsterdjup2`,
                `techno_tire_hjul`.`monsterdjup3`,
                `techno_tire_hjul`.`monsterdjup4`,
                `techno_tire_hjul`.`regnr`,
                `techno_tire_hjul`.`kommentar1`,
                `techno_tire_hjul`.`brukare_fornamn`,
                `techno_tire_hjul`.`brukare_efteramn`,
                `techno_tire_hjul`.`brukare_telefon`,
                `techno_tire_hjul`.`brukare_epost`,
                `techno_tire_falgtyp`.`namn` as Falgtyp1,
                `techno_tire_falgtyp_1`.`namn` as Falgtyp2,
                `techno_tire_falgtyp_2`.`namn` as Falgtyp3,
                `techno_tire_falgtyp_3`.`namn` as Falgtyp4,
                `techno_tire_service_paket`.`namn` as Service_paket,
                `techno_tire_hjul`.`lagerplats_datum`,
                `techno_tire_hjul`.`lagerplats_status`,
                `techno_tire_hjul`.`orderno`,
                `techno_tire_hjul`.`kund_nr`,
                `techno_tire_hjul`.`namn`,
                `techno_tire_hjul`.`utleverans_datum_forslag`,
                `techno_tire_hjul`.`utleverans_datum_forslag_status`,
                `techno_tire_hjul`.`utleverans_datum`,
                `techno_tire_hjul`.`transportsparning`
                 
            from
            
                `techno_tire_hjul` `techno_tire_hjul` 

                inner join `techno_tire_dacktyp` `techno_tire_dacktyp_1` 
                on `techno_tire_hjul`.`dacktyp1` = `techno_tire_dacktyp_1`.`id` 
                    
                inner join `techno_tire_kategori` `techno_tire_kategori` 
                on `techno_tire_hjul`.`kategori_id` = `techno_tire_kategori`.`id` 
                
                inner join `techno_tire_falgtyp` `techno_tire_falgtyp_2` 
                on `techno_tire_hjul`.`falgtyp3` = `techno_tire_falgtyp_2`.`id` 
                
                inner join `techno_tire_dacktyp` `techno_tire_dacktyp_3` 
                on `techno_tire_hjul`.`dacktyp4` = `techno_tire_dacktyp_3`.`id` 
                
                inner join `techno_tire_falgtyp` `techno_tire_falgtyp_1` 
                on `techno_tire_hjul`.`falgtyp2` = `techno_tire_falgtyp_1`.`id` 
                
                inner join `techno_tire_fabrikat` `techno_tire_fabrikat` 
                on `techno_tire_hjul`.`fabrikat1` = `techno_tire_fabrikat`.`id` 
                
                inner join `techno_tire_lager` `techno_tire_lager` 
                on `techno_tire_hjul`.`lager_id` = `techno_tire_lager`.`id` 
                
                inner join `techno_tire_falgtyp` `techno_tire_falgtyp` 
                on `techno_tire_hjul`.`falgtyp1` = `techno_tire_falgtyp`.`id` 
                
                inner join `techno_tire_fabrikat` `techno_tire_fabrikat_1` 
                on `techno_tire_hjul`.`fabrikat2` = `techno_tire_fabrikat_1`.`id` 
                
                inner join `techno_tire_falgtyp` `techno_tire_falgtyp_3` 
                on `techno_tire_hjul`.`falgtyp4` = `techno_tire_falgtyp_3`.`id` 
                
                inner join `techno_tire_dacktyp` `techno_tire_dacktyp_2` 
                on `techno_tire_hjul`.`dacktyp3` = `techno_tire_dacktyp_2`.`id` 
                
                inner join `techno_tire_fabrikat` `techno_tire_fabrikat_2` 
                on `techno_tire_hjul`.`fabrikat3` = `techno_tire_fabrikat_2`.`id` 
                
                inner join `techno_tire_fabrikat` `techno_tire_fabrikat_3` 
                on `techno_tire_hjul`.`fabrikat4` = `techno_tire_fabrikat_3`.`id` 
                
                inner join `techno_tire_service_paket` `techno_tire_service_paket` 
                on `techno_tire_hjul`.`service_paket_id` = `techno_tire_service_paket`.`id` 
                
                inner join `techno_tire_dacktyp` `techno_tire_dacktyp` 
                on `techno_tire_hjul`.`dacktyp2` = `techno_tire_dacktyp`.`id`


                inner join `techno_tire_si` `techno_tire_si` 
                on `techno_tire_hjul`.`si1` = `techno_tire_si`.`id`

                inner join `techno_tire_si` `techno_tire_si_2` 
                on `techno_tire_hjul`.`si2` = `techno_tire_si_2`.`id`

                inner join `techno_tire_si` `techno_tire_si_3` 
                on `techno_tire_hjul`.`si3` = `techno_tire_si_3`.`id`

                inner join `techno_tire_si` `techno_tire_si_4` 
                on `techno_tire_hjul`.`si4` = `techno_tire_si_4`.`id`
                                                                            
                where `techno_tire_hjul`.`hjul_status` = 1 and `techno_tire_hjul`.`aterforsaljare_id` = 1000

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi Oualid;

    The query in general is straight forward, it just seems that you are attempting to pull back data from a bunch of tables. Is this for a report or some sort of data warehouse work? You may want to split it up into smaller pieces depending on what the query is for.

    That aside, you should be able to optimize the query easily with an index on `techno_tire_hjul`.`hjul_status` and `techno_tire_hjul`.`aterforsaljare_id`. Get a count of distinct values for each of those, and put the one that is most selective first in the index. Also make sure that each of the columns you are joining on are indexed if they are not already.

    -Scott
  • OualidOualid Entrant Inactive User Role Beginner
    Thanks for the answer.
    Well, I found the solution that did the trick.
    I changed "inner join" to "left outer join" and it improved the result dramatically.

    /Oualid
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi Oualid;

    Are you still getting the results you want? INNER JOIN and LEFT JOIN are not exactly interchangeable; they have different uses and different results (generally). So if this happens to work in your case great, but just keep in mind for the future that this is not a performance enhancement, and might get you into trouble with unexpected results. =)

    http://dev.mysql.com/doc/refman/5.5/en/join.html

    -Scott
  • OualidOualid Entrant Inactive User Role Beginner
    OK, thanks for the advise.

    /Oualid
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.