Inner Join / Indexes Hell

From: Ike (rxv_at_hotmail.com)
Date: 10/14/04 

Date: Thu, 14 Oct 2004 12:52:01 GMT

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,upcards.lastname,originkey.ori
gin,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
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

Relevant Pages

  • Result of subquery sum based on fields of same record
    ... I use a query to display purchases by origin, ... Then it sums sales from all origins. ... CustomerCounter and PurchCompID to particular record in "main" query? ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)