First of all, sorry for the multiple mailings of the same message. I was receiving bounce messages, so I thought they weren't going through.
On to the reply... I saw that a while ago and decided to try reversing, but I still have the same delay. Mike > -----Original Message----- > From: Brent Baisley [mailto:[EMAIL PROTECTED]] > Sent: Monday, June 03, 2002 2:18 PM > To: Stembridge, Michael; [EMAIL PROTECTED] > Subject: Re: Sluggish performance on medium sized table.. EXPLAIN > SELECT's i ncluded. > > > Try changing your WHERE clause from: > WHERE > call_notes.ticketid = '1' && > call_notes.userid = users.userid && > call_notes.contactid = facility_contact.contactid && > call_notes_text.noteid = call_notes.noteid > > To > WHERE > call_notes.ticketid = '1' && > call_notes.userid = users.userid && > call_notes.contactid = facility_contact.contactid && > call_notes.noteid = call_notes_text.noteid > > Note only the last line is changed and is reversed. > > > > A database is being used to log support calls for a call > center. There are > > around 25,000 clients in the database. The existing call > notes were imported > > from flat text files. > > > > One table (call_notes) contains the call time, ticketid, > noteid, ect.. > > Another table (call_notes_text) only contains noteid and note_text. > > > > When I query the call_notes table for a specific noteid, the info is > > returned instantly. However when I query the > call_notes_text table for a > > specific noteid it takes 15-17 seconds to return the data. > The table has > > around 15,000 rows with each row containing a field about > the size of a > > small newspaper article. > > > > The server is a dual processor intel333 with 256k ram > (RH7.2) running MySQL > > 3.23.41. > > > > Each table contains an id which is key. I've copied and > pasted results > > from some EXPLAIN table queries below. > > > > Here is the actual query that is being used in the script. > (I've broken > > down so it's easier to read) > > > > 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) > > > > Now I go to command line and run this query: > > > > mysql> SELECT * FROM call_notes_text WHERE noteid='1'; > > (results snipped) > > 1 row in set (0.00 sec) > > > > I ran an EXPLAIN SELECT on my main query. (fyi, ticketid > 1 has a noteid of > > 1) > > > > EXPLAIN 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; > > > > > +------------------+--------+-------------------------+------- > --+---------+- > > -----------------------+-------+---------------------------------+ > > | table | type | possible_keys | key > | key_len | > > ref | rows | Extra | > > > +------------------+--------+-------------------------+------- > --+---------+- > > -----------------------+-------+---------------------------------+ > > | call_notes_text | ALL | PRIMARY,noteid,noteid_2 | > NULL | NULL | > > NULL | 14682 | Using temporary; Using filesort | > > | call_notes | eq_ref | PRIMARY,noteid,noteid_2 | > PRIMARY | 4 | > > call_notes_text.noteid | 1 | where used | > > | users | eq_ref | PRIMARY,userid | > PRIMARY | 4 | > > call_notes.userid | 1 | | > > | facility_contact | eq_ref | PRIMARY,contactid | > PRIMARY | 4 | > > call_notes.contactid | 1 | | > > > +------------------+--------+-------------------------+------- > --+---------+- > > -----------------------+-------+---------------------------------+ > > 4 rows in set (0.00 sec) > > > > Note the number of rows returned from call_notes_text. > This matches the > > total number of rows in that table. > > > > It appears to be a problem with the query, but I can't see > where the problem > > is. Thanks for any help you can provide. > > > > - Mike > > > > > --------------------------------------------------------------------- > > 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 > > > > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > --------------------------------------------------------------------- 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
