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 <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
