Hi,

I've been playing this afternoon with my queries checking them for
optimisation.

If I run this query:

explain
select basket.*, companies.Company_Name
from basket
inner join companies on basket.Company_ID=companies.Company_ID
where
basket.`Status`='Open'

I'm told that only the index on basket.Status is used. However if I
remove the companies.Company_Name (the only field pulling from the
companies table) the index on companies.Company_ID is used.

There are definitely companies to join with the inner join but
interestingly if I change it to a left join for some reason the index
gets used. If I use FORCE INDEX (Company_ID) on companies it will also
use the index.

My Q is why therefore would MySQL not use an index when it should do
and why does the fields that get selected affect if an index is used?
I would have thought they were not linked (pos stupid question).

Thanks

A
p.s a similar query but on another table uses both indexes:

explain
select artists.*, companies.Company_Name
from artists
inner join companies on artists.Company_ID=companies.Company_ID
where
artists.Artist_ID=2

On Jan 29, 4:46 pm, UKuser <spiderc...@yahoo.co.uk> wrote:
> Hi,
>
> I've been playing this afternoon with my queries checking them for
> optimisation.
>
> If I run this query:
>
> explain
> select basket.*, companies.Company_Name
> from basket
> inner join companies on basket.Company_ID=companies.Company_ID
> where
> basket.`Status`='Open'
>
> I'm told that only the index on basket.Status is used. However if I
> remove the companies.Company_Name (the only field pulling from the
> companies table) the index on companies.Company_ID is used.
>
> There are definitely companies to join with the inner join but
> interestingly if I change it to a left join for some reason the index
> gets used. If I use FORCE INDEX (Company_ID) on companies it will also
> use the index.
>
> My Q is why therefore would MySQL not use an index when it should do
> and why does the fields that get selected affect if an index is used?
> I would have thought they were not linked (pos stupid question).
>
> Thanks
>
> A
> p.s a similar query but on another table uses both indexes:
>
> explain
> select artists.*, companies.Company_Name
> from artists
> inner join companies on artists.Company_ID=companies.Company_ID
> where
> artists.Artist_ID=2

Please allow me to add:

explain
select basket.*, companies.Company_ID as Company_ID
from basket
inner join companies on basket.Company_ID=companies.Company_ID
where
basket.`Status`='Open'

This query accesses the index no problem but this won't

explain
select basket.*, companies.Company_ID as Company_ID,
companies.Company_Name as cname
from basket
inner join companies on basket.Company_ID=companies.Company_ID
where
basket.`Status`='Open'

WHY??? And why is the Select statement affecting the index used?