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. When running the queries outline below CPU usage is only around
15% or so.
Each table contains an id which is key. I've copied and pasted results
from some EXPLAIN table queries below.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
mysql> explain call_notes;
+------------------+------------+------+-----+---------------------+--------
--------+
| Field | Type | Null | Key | Default | Extra
|
+------------------+------------+------+-----+---------------------+--------
--------+
| noteid | int(10) | | PRI | NULL |
auto_increment |
| ticketid | int(10) | | | 0 |
|
| userid | int(5) | | | 0 |
|
| contactid | int(5) | | | 0 |
|
| call_start | datetime | | | 0000-00-00 00:00:00 |
|
| call_end | datetime | | | 0000-00-00 00:00:00 |
|
| call_elapsed | time | | | 00:00:00 |
|
| call_seconds | int(10) | | | 0 |
|
| entered_statusid | tinyint(2) | | | 0 |
|
| datemodified | datetime | | | 0000-00-00 00:00:00 |
|
+------------------+------------+------+-----+---------------------+--------
--------+
10 rows in set (0.00 sec)
mysql> explain call_notes_text;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| noteid | int(10) | | PRI | NULL | auto_increment |
| note_text | text | | | | |
+-----------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> explain facility_contact;
+--------------+-------------+------+-----+---------------------+-----------
-----+
| Field | Type | Null | Key | Default | Extra
|
+--------------+-------------+------+-----+---------------------+-----------
-----+
| contactid | int(10) | | PRI | NULL |
auto_increment |
| fnum | varchar(8) | | | |
|
| contact | varchar(30) | | | |
|
| contactadded | datetime | | | 0000-00-00 00:00:00 |
|
+--------------+-------------+------+-----+---------------------+-----------
-----+
4 rows in set (0.00 sec)
mysql> explain users;
+--------------+--------------+------+-----+---------------------+----------
------+
| Field | Type | Null | Key | Default | Extra
|
+--------------+--------------+------+-----+---------------------+----------
------+
| userid | int(10) | | PRI | NULL |
auto_increment |
| username | varchar(50) | | | |
|
| password | varchar(50) | | | |
|
| deptid | tinyint(4) | | | 0 |
|
| jobtitleid | int(2) | | | 0 |
|
| namefirst | varchar(50) | | | |
|
| namelast | varchar(50) | | | |
|
| ext | int(5) | | | 0 |
|
| email | varchar(100) | | | |
|
| level | int(1) | | | 1 |
|
| senior | char(3) | | | |
|
| dateadded | datetime | | | 0000-00-00 00:00:00 |
|
| datemodified | datetime | | | 0000-00-00 00:00:00 |
|
| active | tinyint(1) | | | 1 |
|
+--------------+--------------+------+-----+---------------------+----------
------+
14 rows in set (0.00 sec)
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
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 (17.61 sec)
Alternately, if I just pull some data from the call_notes table:
mysql> SELECT * FROM call_notes WHERE noteid='1';
(results snipped)
1 row in set (0.19 sec)
And here's another that is interesting:
mysql> select * from call_notes_text limit 1;
(results snipped)
1 row in set (0.21 sec)
Finally, 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.
But, if I run this:
mysql> EXPLAIN select * from call_notes_text where noteid='1';
+-----------------+-------+-------------------------+---------+---------+---
----+------+-------+
| table | type | possible_keys | key | key_len |
ref | rows | Extra |
+-----------------+-------+-------------------------+---------+---------+---
----+------+-------+
| call_notes_text | const | PRIMARY,noteid,noteid_2 | PRIMARY | 4 |
const | 1 | |
+-----------------+-------+-------------------------+---------+---------+---
----+------+-------+
1 row in set (0.03 sec)
It only returns one row (not 14682 rows).
It appears to be a problem with the query, as opposed to the actual table,
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
