Index Not Being Used in Join 1

[UltraSmooth]

I'm trying to do a join between two tables, one with no more than a couple of hundred records and one with about 4 million. My issue is that mysql is ignoring my index on the large table thus making the query extremely slow.

Here's my query,

select fiscalyear,fiscalweek,his_key,his_loctn,his_salunit,his_salretl,his_salcost
from syinvhis
join weekly_periods
on his_tdate=clarion_date and weekly_periods.fiscalyear=2009 and weekly_periods.fiscalweek=1
and his_type='DC' and his_loctn in ('01','03','04','05') and his_period='W' and his_service='R';

- weekly_periods has a PRIMARY_KEY on the column clarion_date
- syinvhis has an index over the columns his_tdate,his_type,his_loctn,his_period,his_service,his_key

I have two similar tables to weekly_periods - monthly_periods and yearly_periods and these have PRIMARY_KEYS on clarion_date. When I do the same query as above but instead join to one of these other tables (with syinvhis) they index is used properly on the date column.

Any ideas what is going on here?

Also, what is the difference in specifiying selection conditions in the ON section of a JOIN as opposed to the WHERE section? My best guess is that the ON section limits when the records are actually joining and the WHERE filters out records after the join has taken place. Is there any difference in the effect it has on what indexes are chosen?

Thanks! This forum has been a fabulous resource.

 

[r937]

your understanding of the difference between conditions in the ON clause versus the WHERE clause is correct

it should make no difference at all for inner joins, but a ~huge~ difference in results for outer joins

here's your query --

SELECT fiscalyear
     , fiscalweek
     , his_key
     , his_loctn
     , his_salunit
     , his_salretl
     , his_salcost
  FROM syinvhis
INNER
  JOIN weekly_periods
    ON his_tdate = clarion_date 
   AND weekly_periods.fiscalyear = 2009 
   AND weekly_periods.fiscalweek = 1
   AND his_type = 'DC' 
   AND his_loctn IN ('01','03','04','05') 
   AND his_period = 'W' 
   AND his_service = 'R'

and here's how i would rewrite it --

SELECT syinvhis.his_key
     , syinvhis.his_loctn
     , syinvhis.his_salunit
     , syinvhis.his_salretl
     , syinvhis.his_salcost
  FROM weekly_periods
INNER
  JOIN syinvhis
    ON syinvhis.his_tdate = weekly_periods.clarion_date
   AND syinvhis.his_type = 'DC' 
   AND syinvhis.his_loctn IN ('01','03','04','05') 
   AND syinvhis.his_period = 'W' 
   AND syinvhis.his_service = 'R'
 WHERE weekly_periods.fiscalyear = 2009 
   AND weekly_periods.fiscalweek = 1

note that i removed a couple columns from the SELECT clause because you already know what those values are going to be

re-arranging the query the way i did emphasizes that it is the restriction on the weekly_periods table (see the WHERE clause) that is actually "driving" the results

you want to choose a specific weekly period, then retrieve all the history records that qualify for that period, rather than choosing all the history records that qualify and then filtering out all the ones that don't belong to the period you're interested in

can you see the logical difference there?

when you approach query-building from the point of view of starting the FROM clause with the most restrictive table, the one that's "driving" the logic of the retrieval, then many times this will automatically result in the most efficient execution plan as well







r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[UltraSmooth]

r937, thank you so much for taking the time to explain this to me. Your help has been fantastic once again and is greatly appreciated.

On a side note, before I read your post I was doing some playing and I did an 'optimize table' on the syinvhis table. After running the explain again it performed the join in the order you rewrote the query in as well as used the correct index.

Your query is cleaner and logically easier to understand so I'll certainly be writing it that way. I always assumed that mysql chose the best order for the join thus the order you specify your tables had little impact on that. Again, your insight into mysql has helped advance my understanding.

I have one last question regarding the ON and WHERE clause. You moved the syinvhis selection criteria to the ON clause but you have the weekly_periods selection criteria in the WHERE clause. What is the reason you did this rather than including all selection criteria for both tables in the ON clause?

Thanks!

 

[r937]

because the ON clause should give only the requirements for joining rows of the table being joined

you will see how much difference this mode of thinking makes when you apply it to outer joins



i make sure that every condition in the ON clause involves a column from the table being joined

thus, to join the syinvhis table, it looks like this --

INNER
  JOIN syinvhis
    ON syinvhis.his_tdate = weekly_periods.clarion_date
   AND syinvhis.his_type = 'DC' 
   AND syinvhis.his_loctn IN ('01','03','04','05') 
   AND syinvhis.his_period = 'W' 
   AND syinvhis.his_service = 'R'

where every condition in the ON clause involves a column from that table, and the table prefix on the columns nicely lines up with the table name, making it all that much clearer

this is also why i write [blue]FROM x INNER JOIN y ON y.col = x.col[/blue] instead of [blue]FROM x INNER JOIN y ON x.col = y.col[/blue] -- because when you align the clauses using line breaks and indentation, it always shows the joining condition under the table name --

[blue]  FROM x
INNER
  JOIN y 
    ON y.col = x.col[/blue]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[UltraSmooth]

Everything makes so much sense when you explain it! I appreciate you taking the time to respond.

Cheers!