List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:February 4 2011 5:29pm
Subject:Table/select problem...
View as plain text  
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.
awallace@stripped
(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.
Thread
Table/select problem...Andy Wallace4 Feb
  • Re: Table/select problem...Steve Musumeche4 Feb
    • Re: Table/select problem...Johan De Meersman4 Feb
      • Re: Table/select problem...Andy Wallace4 Feb
  • RE: Table/select problem...Travis Ard5 Feb