List:General Discussion« Previous MessageNext Message »
From:Erv Young Date:June 3 2002 6:08pm
Subject:Re: All records returne with JOIN
View as plain text  
Michael,

In Oracle, with which I am much more familiar, only primary keys are 
indexed automatically.  Foreign keys are not indexed automatically.  You 
have to create the indexes on the foreign key columns explicitly.  My guess 
is that MySQL works the same way, and that this is the source of your 
difficulty.  Guessing that call_notes.note_id, users.userid, and 
facility_contact.contactid are primary keys, you must still explicitly 
create indexes on all 4 columns on the left side of the = signs in the 
where clause.  Omitting those indexes can cause exactly the sort of 
difficulty you are experiencing.  I know this first-hand.

Of course, I'm also assuming that all your tables are InnoDB tables.

Hope this helps.

--Erv Young

At 12:35 PM 6/3/2002 -0400, Stembridge, Michael wrote:
>The following query takes a very long time to process.  It finally returns
>only the row I'm wanting, but when I run an EXPLAIN SELECT on it, it looks
>like all 14687 rows are being examined for the record -- instead of mysql
>honing in on the desired row immediately.
>
>         SELECT
>                 call_notes.call_elapsed,
>                 call_notes.call_seconds,
>                 call_notes.call_start,
>                 call_notes.call_end,
>                 call_notes_text.note_text,
>                 users.username,
>                 users.namefirst,
>                 users.email,
>                 facility_contact.contactid,
>                 facility_contact.contact
>         FROM
>                 call_notes,
>                 call_notes_text,
>                 users,
>                 facility_contact
>         WHERE
>                 call_notes.ticketid = '1' &&
>                 call_notes.userid = users.userid &&
>                 call_notes.contactid = facility_contact.contactid &&
>                 call_notes_text.noteid = call_notes.noteid
>         ORDER BY
>                 call_notes.call_start DESC;
>
>         (results snipped)
>         1 row in set (17.89 sec)
>
>A variable called $ticketid is passed to this query (and is placed where the
>'1' is above).  The ticketid is the key  I use across several tables.   It
>is a field in call_notes and that is how I'm getting the noteid (which is
>the primary key of call_notes and call_notes_text).   I will post details on
>the tables if needed.
>
>Any help or guidance appreciated.
>
>---------------------------------------------------------------------
>Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread110831@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-res04ft9=gte.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
All records returne with JOINMichael Stembridge3 Jun
  • Re: All records returne with JOINErv Young3 Jun