Subject: Grouper Users - Open Discussion List
- From: Colin Hudler <>
- Subject: question about grouper_audit_entry table indexes
- Date: Fri, 28 May 2010 13:27:30 -0500
Grouper 1.5.2 on mysql 5.0.77. It takes about a minute to load audits for a single group using the UI, even when there are no audits for that group. The table has about 2 million entries, however. I watched the query log and it makes a query similar to this:
select ... from grouper_audit_entry where ((audit_type_id='42e325db85c64f91bd231058c8fa5447' and string01='da4909547bb046a6ade95366417a41de')
string06='da4909547bb046a6ade95366417a41de') or (audit_type_id='cf3e9a639f444ef5a35b7680e9cdb25a' and
string02='da4909547bb046a6ade95366417a41de')) order by...;
(some of this was redacted for clarity).
There is no index on string06. These are the indexes on that table:
KEY `audit_entry_act_as_idx` (`act_as_member_id`),
KEY `audit_entry_type_idx` (`audit_type_id`),
KEY `audit_entry_context_idx` (`context_id`),
KEY `audit_entry_logged_in_idx` (`logged_in_member_id`),
KEY `audit_entry_string01_idx` (`string01`(255)),
KEY `audit_entry_string02_idx` (`string02`(255)),
KEY `audit_entry_string03_idx` (`string03`(255)),
KEY `audit_entry_string04_idx` (`string04`(255)),
KEY `audit_entry_string05_idx` (`string05`(255))
Adding a string06 index makes the query very fast again, less than 1 second. I also note that string07 and string08 are not indexed either, although this query does not include them. (I also had to include a "use index" to the query, but I think this is a failure of the mysql query plan.)
My question is, is this an intentional omission?
- question about grouper_audit_entry table indexes, Colin Hudler, 05/28/2010
Archive powered by MHonArc 2.6.16.