optimize sql query

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


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

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

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. =)

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

-Scott

OK, thanks for the advise.

/Oualid