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
