Setting an index and getting mysql to use it

I have been looking at this problem for a while now. I have a querry to
put together some colums from several tables. I have an ID index for all
of the tables. When I run explain on the query, it shows that the index
is not being used. I have tried to set it using the USE INDEX command
but the response is that the id isn't a colum. I am trying to use
nnnn.id as the name. Doesn't seem to like the ".id".

I keep getting duplicate records and I think the index setting would get
rid of them. I am running this using php. Any suggestions would be
appreciated.

select distinct part_number_title.id as id1, part_number_title.pn,
part_number_title.title, release_revision.releaserevision,
release_date.releasedate,
change_order_number.changeordernumber, release_memo_num.releasememonum,
retired.retired
from part_number_title inner join release_revision, release_date,
change_order_number, release_memo_num, retired


--
Robert Courtney
Cell: 714-791-3462
Home: 714-531-9607
bobsc1@earthlink.net



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Setting an index and getting mysql to use it

you are in nested loops,
where are the where clause for using such indexes ?

Mathias

Selon Robert Courtney :

> I have been looking at this problem for a while now. I have a querry to
> put together some colums from several tables. I have an ID index for all
> of the tables. When I run explain on the query, it shows that the index
> is not being used. I have tried to set it using the USE INDEX command
> but the response is that the id isn't a colum. I am trying to use
> nnnn.id as the name. Doesn't seem to like the ".id".
>
> I keep getting duplicate records and I think the index setting would get
> rid of them. I am running this using php. Any suggestions would be
> appreciated.
>
> select distinct part_number_title.id as id1, part_number_title.pn,
> part_number_title.title, release_revision.releaserevision,
> release_date.releasedate,
> change_order_number.changeordernumber, release_memo_num.releasememonum,
> retired.retired
> from part_number_title inner join release_revision, release_date,
> change_order_number, release_memo_num, retired
>
>
> --
> Robert Courtney
> Cell: 714-791-3462
> Home: 714-531-9607
> bobsc1@earthlink.net
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=mfatene@free.fr
>
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Setting an index and getting mysql to use it

Yes, as Mathias has said, your query does not make sense. You cannot do
an INNER JOIN without either a WHERE clause or an ON condition, and
without at least one of those (or ORDER BY) any indexes will be ignored.
You are asking MySQL to process all records in all tables so indexes are
no help.

I am sure this is not want you really want to do. If you want to know
how your query should be, you need to show us the structure of each
table (the output of SHOW CREATE TABLE ) and describe what
you are trying to achieve by your query.

John Bonnett

-----Original Message-----
From: mfatene@free.fr [mailto:mfatene@free.fr]=20
Sent: Wednesday, 1 June 2005 2:44 AM
To: Robert Courtney
Cc: mysql mailing list
Subject: Re: Setting an index and getting mysql to use it

you are in nested loops,
where are the where clause for using such indexes ?

Mathias

Selon Robert Courtney :

> I have been looking at this problem for a while now. I have a querry
to
> put together some colums from several tables. I have an ID index for
all
> of the tables. When I run explain on the query, it shows that the
index
> is not being used. I have tried to set it using the USE INDEX command
> but the response is that the id isn't a colum. I am trying to use
> nnnn.id as the name. Doesn't seem to like the ".id".
>
> I keep getting duplicate records and I think the index setting would
get
> rid of them. I am running this using php. Any suggestions would be
> appreciated.
>
> select distinct part_number_title.id as id1, part_number_title.pn,
> part_number_title.title, release_revision.releaserevision,
> release_date.releasedate,
> change_order_number.changeordernumber,
release_memo_num.releasememonum,
> retired.retired
> from part_number_title inner join release_revision,
release_date,
> change_order_number, release_memo_num, retired
>
>
> --
> Robert Courtney
> Cell: 714-791-3462
> Home: 714-531-9607
> bobsc1@earthlink.net
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: =
http://lists.mysql.com/win32?unsub=3Dmfatene@free.fr
>
>



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org