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
