What columns do you have indexed on your event_log table? Can you post the output from SHOW CREATE TABLE? How long does the query run for?
-----Original Message----- From: Andy Wallace [mailto:awall...@ihouseweb.com] Sent: Friday, February 04, 2011 10:29 AM To: mysql list Subject: Table/select problem... 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. awall...@ihouseweb.com (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/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
