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.
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.
> 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