Greetings, all...
I'm having an issue with a SELECT in our system. We have an event log table,
with about 9 million rows in it. Inserts happen with some pretty high frequency,
and these selects happen periodically. The event_log table is MyISAM, the
rest of the tables are InnoDB.
What's happening is that, periodically, when this select gets run, the whole
damn thing locks up, and that pretty much shuts us down (since many things
insert events into the table, and the table gets locked, so all the inserts
hang).
The statement and the explain for it are below. the enduser table has about a
million rows in it, the event_type table 35 rows. The weird part is that, if
I strip down the query to use no joins, the explain wants to return about 17,000
rows, but the query itself does the table locking thing.
Should we perhaps change the event log to InnoDB to avoid table locking? Might
the table itself be corrupt in some way? Any thoughts?
thanks,
andy
EXPLAIN
SELECT EL.event_log_id,
EL.event_time,
DATE_FORMAT(EL.event_time, '%c-%d-%Y %H:%i:%s') as 'time_formatted',
ET.event_type_id,
ET.description,
EL.csr_name,
EL.enduser_acnt,
EL.csr_name,
EL.referer,
EL.mls_id,
EL.mls_no,
EL.ss_id,
EL.details,
E.fname,
E.lname,
E.email,
E.phone1
FROM event_log EL
JOIN event_type ET ON EL.event_type_id = ET.event_type_id
JOIN enduser E ON EL.enduser_acnt = E.enduser_acnt
WHERE EL.acnt = 'AR238156'
AND EL.enduser_acnt != ''
AND EL.event_type_id = 'EndUserLogin'
AND event_time BETWEEN DATE_SUB(CURDATE(), INTERVAL '7' DAY) AND NOW()
ORDER BY EL.event_time DESC
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ET
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 92
ref: const
rows: 1
Extra: Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: EL
type: index_merge
possible_keys: agent,enduser,event_log_ibfk_1
key: agent,event_log_ibfk_1
key_len: 62,92
ref: NULL
rows: 1757
Extra: Using intersect(agent,event_log_ibfk_1); Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: E
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: idx_acnt.EL.enduser_acnt
rows: 1
Extra: Using where
3 rows in set (0.00 sec)
--
Andy Wallace
iHOUSEweb, Inc.
[email protected]
(866) 645-7700 ext 219
--
There are two ways to build software:
Make it so simple that there are obviously no bugs,
or make it so complex that there are no obvious bugs.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
