Why query do not use keys corectly?

Question

I have this complicated query:

SELECT * FROM `s` 
LEFT JOIN `users` AS `iu` ON iu.user_id = s.s_iu_id 
LEFT JOIN `users` AS `uu` ON uu.user_id = s.uu_id 
LEFT JOIN `sic` ON sic.s_id = s.s_id 
LEFT JOIN `users` AS `ppu` ON ppu.user_id = sic.pure_price_user_id 
LEFT JOIN `users` AS `spu` ON spu.user_id = sic.spu_id 
LEFT JOIN `us` ON f_user_id = 8714 AND users.f_s_id = s.s_id 
LEFT JOIN `c` ON c.c_id = sic.c_id 
LEFT JOIN `countries` ON countries.country_id = s.country_id 
LEFT JOIN `countries` AS `countries2` ON countries2.country_id = countries.parent_id 
LEFT JOIN `continents` ON continents.continent_id = countries.continent_id 
LEFT JOIN `countryperiods` ON countryperiods.countryPeriod_id = s.countryPeriod_id 
LEFT JOIN `currencies` ON currencies.currency_id = countryperiods.currency_id 
LEFT JOIN `serrations` AS `s1` ON s.serration_width_id = s1.id 
LEFT JOIN `serrations` AS `s2` ON s.serration_height_id = s2.id 
LEFT JOIN `colors` ON colors.color_id = s.color_id 
LEFT JOIN `colors` AS `c2` ON c2.color_id = s.background_id 
LEFT JOIN `colors` AS `c3` ON c3.color_id = s.color2_id 
WHERE (s.fsis_id = '141518') AND (sic.c_id = '1') AND 
((s.s_enabled = 1) OR (s.s_iu_id = '8714')) AND 
(s.s_exists = 1) 
ORDER BY `c_number` ASC, `cn_prepend` ASC, `cn_append` ASC 
LIMIT 10000

Explain:

id  s_type  table          type       possible_keys            key            key_len   ref                            rows    Extra 
1   SIMPLE  sic            range      catNum,catalog_s_id      catalog_s_id   10        NULL                           132480  Using index condition; Using temporary; Using filesort
1   SIMPLE  ppu            eq_ref     PRIMARY                  PRIMARY        4         data.sic.pure_price_user_id    1       Using where
1   SIMPLE  spu            eq_ref     PRIMARY                  PRIMARY        4         data.sic.stamped_price_user_id 1       Using where
1   SIMPLE  s              eq_ref     PRIMARY,s_insertUser_id  PRIMARY        4         data.sic.s_id                  1       Using where
1   SIMPLE  us             ref|filter us,f_s_id,f_user_id      f_s_id|f_user_id  5|5    data.sic.s_id                  1 (0%)  Using where; Using rowid filter
1   SIMPLE  c              ALL        PRIMARY                  NULL           NULL  NULL                               2       Using where; Using join buffer (flat, BNL join)
1   SIMPLE  countries      ALL        PRIMARY                  NULL           NULL     NULL                            494     Using where; Using join buffer (incremental, BNL join)
1   SIMPLE  countries2     eq_ref     PRIMARY                  PRIMARY        4        data.countries.parent_id        1       Using where
1   SIMPLE  continents     eq_ref     PRIMARY                  PRIMARY        4        data.countries.continent_id     1       Using where
1   SIMPLE  countryperiods eq_ref     PRIMARY                  PRIMARY        4        data.s.countryPeriod_id         1       Using where
1   SIMPLE  iu             eq_ref     PRIMARY                  PRIMARY        4        data.s.s_insertUser_id          1       Using where
1   SIMPLE  uu             eq_ref     PRIMARY                  PRIMARY        4        data.s.uploadUser_id            1       Using where
1   SIMPLE  currencies     ALL        PRIMARY                  NULL           NULL     NULL                            281     Using where; Using join buffer (flat, BNL join)
1   SIMPLE  s1             eq_ref     PRIMARY                  PRIMARY        4        data.s.serration_width_id       1       Using where
1   SIMPLE  s2             eq_ref     PRIMARY                  PRIMARY        4        data.s.serration_height_id      1       Using where
1   SIMPLE  colors         eq_ref     PRIMARY                  PRIMARY        4        data.s.color_id                 1       Using where
1   SIMPLE  c2             eq_ref     PRIMARY                  PRIMARY        4        data.s.background_id            1       Using where
1   SIMPLE  c3             eq_ref     PRIMARY                  PRIMARY        4        data.s.color2_id                1       Using where

Why tables c, countries and currencies do not use primary index when is the same join as with others? Ca I optmize it? Query durate 1.3883s.

Answer 1

Move sic.c_id = 1 from the where clause to the join condition; putting it in the where clause forces the join to sic to be an INNER join.

Also change all conditions on ids to be compared to integers, not strings, eg s.fsis_id = 141518 not s.fsis_id = '141518'.

Ensure there are indexes on all "id" columns (columns you're looking up on, which all seem to end with "id").

SELECT *
FROM `s` 
LEFT JOIN `users` AS `iu` ON iu.user_id = s.s_iu_id 
LEFT JOIN `users` AS `uu` ON uu.user_id = s.uu_id 
LEFT JOIN `sic` ON sic.s_id = s.s_id AND sic.c_id = 1
LEFT JOIN `users` AS `ppu` ON ppu.user_id = sic.pure_price_user_id 
LEFT JOIN `users` AS `spu` ON spu.user_id = sic.spu_id 
LEFT JOIN `us` ON f_user_id = 8714 AND users.f_s_id = s.s_id 
LEFT JOIN `c` ON c.c_id = sic.c_id 
LEFT JOIN `countries` ON countries.country_id = s.country_id 
LEFT JOIN `countries` AS `countries2` ON countries2.country_id = countries.parent_id 
LEFT JOIN `continents` ON continents.continent_id = countries.continent_id 
LEFT JOIN `countryperiods` ON countryperiods.countryPeriod_id = s.countryPeriod_id 
LEFT JOIN `currencies` ON currencies.currency_id = countryperiods.currency_id 
LEFT JOIN `serrations` AS `s1` ON s.serration_width_id = s1.id 
LEFT JOIN `serrations` AS `s2` ON s.serration_height_id = s2.id 
LEFT JOIN `colors` ON colors.color_id = s.color_id 
LEFT JOIN `colors` AS `c2` ON c2.color_id = s.background_id 
LEFT JOIN `colors` AS `c3` ON c3.color_id = s.color2_id 
WHERE s.fsis_id = 141518
AND (s.s_enabled = 1 OR s.s_iu_id = 8714)
AND s.s_exists = 1
ORDER BY `c_number`, `cn_prepend`, `cn_append` 
LIMIT 10000

(unnecessary brackets removed for clarity)