Inner Join / Indexes Hell

[Ike]

Oh I have a nasty query which runs incredibly slowly. I am running MySQL
4.0.20-standard. Thus, in trying to expedite the query, I am trying to set
indexes in my tables.
My query requires four inner joins, as follows :

SELECT DISTINCT
upcards.id,statuskey.status,upcards.firstname,upca rds.lastname,originkey.ori
gin,associatekey.username,associatekey2.username,u pcards.deleted
FROM upcards,status,origins,associates
INNER JOIN status statuskey on upcards.statuskey=statuskey.id
INNER JOIN origins originkey on upcards.originkey=originkey.id
INNER JOIN associates associatekey on upcards.associatekey=associatekey.id
INNER JOIN associates associatekey2 on
upcards.associatekey2=associatekey2.id
WHERE (upcards.deleted is null OR upcards.deleted = 0) AND
(associatekey.branch IS NULL OR associatekey.branch LIKE '' OR
associatekey.branch LIKE '%');

The tables structure is:

table upcards
id
firstname
lastname
deleted
statuskey
originkey
associatekey
associatekey2

table status
id
status

table origins
id
origins

table associates
id
username
branch

Thus, to expedite my query, I am setting my indexes as follows: (id is a
PRIMARY key on all tables)
for the table upcards I set as indexes:
deleted
statuskey
originkey
associatekey
associatekey2

and for associates:
branch

In doing so, I have every column thus indexed, you would think this query
would run faster, but it does not. Is there a better means of setting my
indexes here to speed up this query? Perhaps there is a more efficient way
to write this query, i.e. perhaps the problem is not so much with my
indexing, as with the query itself? Thanks, Ike

[2metre]

Ike wrote:

SELECT DISTINCT
upcards.id,statuskey.status,upcards.firstname,upca rds.lastname,originkey.ori
gin,associatekey.username,associatekey2.username,u pcards.deleted
FROM upcards,status,origins,associates
INNER JOIN status statuskey on upcards.statuskey=statuskey.id
INNER JOIN origins originkey on upcards.originkey=originkey.id
INNER JOIN associates associatekey on upcards.associatekey=associatekey.id
INNER JOIN associates associatekey2 on
upcards.associatekey2=associatekey2.id ...
1/ I would recommend using different aliases. Isnt the whole point of
aliases to reduce the amount of code to type and make the query easier
to read? Not only are your aliases longer than the original tablenames
but they're also confusing because they duplicate the names of fields!
How about using a and a2?
WHERE (upcards.deleted is null OR upcards.deleted = 0) AND
(associatekey.branch IS NULL OR associatekey.branch LIKE '' OR
associatekey.branch LIKE '%');

2/ What other value could associatekey.branch take? You seem to have
matched on null, empty and any value?

3/ Your 'from' clause is replicating tables that are already joined
within your INNER JOIN statements.

[Ike]

Ah.....when I take your point #3 into account, below....things run
beautifully. //Ike

"2metre" <2m****@xxxhersham.net> wrote in message
news:ck**********@titan.btinternet.com...

Ike wrote:

SELECT DISTINCT
upcards.id,statuskey.status,upcards.firstname,upca rds.lastname,originkey.ori gin,associatekey.username,associatekey2.username,u pcards.deleted
FROM upcards,status,origins,associates
INNER JOIN status statuskey on upcards.statuskey=statuskey.id
INNER JOIN origins originkey on upcards.originkey=originkey.id
INNER JOIN associates associatekey on upcards.associatekey=associatekey.id INNER JOIN associates associatekey2 on
upcards.associatekey2=associatekey2.id ...

1/ I would recommend using different aliases. Isnt the whole point of
aliases to reduce the amount of code to type and make the query easier
to read? Not only are your aliases longer than the original tablenames
but they're also confusing because they duplicate the names of fields!
How about using a and a2?

> WHERE (upcards.deleted is null OR upcards.deleted = 0) AND
> (associatekey.branch IS NULL OR associatekey.branch LIKE '' OR
> associatekey.branch LIKE '%');

2/ What other value could associatekey.branch take? You seem to have
matched on null, empty and any value?

3/ Your 'from' clause is replicating tables that are already joined
within your INNER JOIN statements.

[justaguy]

Ike wrote:

SELECT DISTINCT upcards.id, statuskey.status,
upcards.firstname, upcards.lastname, originkey.origin,
associatekey.username, associatekey2.username,
upcards.deleted
FROM upcards, status, origins, associates
INNER JOIN status statuskey on upcards.statuskey = statuskey.id
INNER JOIN origins originkey on upcards.originkey = originkey.id
INNER JOIN associates associatekey
on upcards.associatekey = associatekey.id
INNER JOIN associates associatekey2
on upcards.associatekey2 = associatekey2.id
...

"2metre" wrote: ...
3/ Your 'from' clause is replicating tables that are already joined
within your INNER JOIN statements.

Ike wrote: Ah.....when I take your point #3 into account, ...
things run beautifully. //Ike

But, of course! The FROM clause with four tables listed makes a cross
join of all four, which itself is then joined with each of the other
four views. This is huge and slow.

Changing the FROM ... JOIN ... clauses to what you really meant
eliminates the cross product, simplifies the query and speeds it up
geometrically.

SELECT DISTINCT upcards.id, status.status,
upcards.firstname, upcards.lastname, origins.origin,
associates.username, a2.username, upcards.deleted
FROM upcards
INNER JOIN status on upcards.statuskey = status.id
INNER JOIN origins on upcards.originkey = origins.id
INNER JOIN associates on upcards.associatekey = associates.id
INNER JOIN associates a2 on upcards.associatekey2 = a2.id
;

Others have addressed the logic of table aliases.

A google on SQL CROSS JOIN found, for example,
http://www.sql-server-performance.com/tuning_joins.asp

excerpt:
.... I ran across a slow-performing query from an ERP program. ... I
noticed that instead of creating a LEFT JOIN, the developer had
accidentally created a CROSS JOIN instead. In this particular example,
less than 10,000 rows should have resulted from the LEFT JOIN, but
because a CROSS JOIN was used, over 11 million rows were returned
instead. Then the developer used a SELECT DISTINCT to get rid of all the
unnecessary rows created by the CROSS JOIN. As you can guess, this made
for a very lengthy query.