I have a very large MySQL (~10M rows) database, currently using the InnoDB engine, which stores log events. I am working on a front-end that allows searching through this log database, and one of the features of this front-end is the ability to filter by certain values of the event. For the visual, the filter is a select-multiple element that lists both the possible values and the number of events currently matching that value:
However the queries I'm using to count these values are causing a significant performance hit. Part of the issue I'm having is I currently have 7 different parameters I allow filtering on, as well as more general filters (e.g. time window), so for each of these 7 parameters I'm running something like:
SELECT parameter1, count(parameter1) FROM table WHERE log_time > 'Some Date' AND parameter2 = 'Something' AND parameter3 = ... GROUP BY parameter1
...where parameter 2, 3, etc. are other conditions that have already been set. So I'm applying the same where condition to all 7 count queries, which seems redundant to me. I know I can reformat this to use a subquery instead of the where condition, but is it possible to then count all 7 filters off the same subquery?
Another idea I had was to create a temporary table which gets populated with the results of the subquery, and is then interrogated for the 7 filter counts. In testing this did overall save some time, but seems like overkill to me (especially from a disk IOP perspective).
I also tried switching the table to MyISAM, since
count() operations are supposed to be faster there, but didn't notice a significant difference in query time. I'm guessing this is because I'm using where conditions that force full table scans anyway.
== Edit 1 ==
Here is the table structure of one of my test tables (condensed version of the actual table, only including values that are filtered on):
Create Table: CREATE TABLE `temp_3` ( `client` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `record_type` tinyint DEFAULT NULL, `creation_time` datetime DEFAULT NULL, `operation` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `result_status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `user_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `client_ip` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `source_country_code` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `source_organization` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, KEY `idx_creation_time` (`creation_time`), KEY `idx_client` (`client`), KEY `idx_record_type` (`record_type`), KEY `idx_operation` (`operation`), KEY `idx_result_status` (`result_status`), KEY `idx_user_id` (`user_id`), KEY `idx_client_ip` (`client_ip`), KEY `idx_source_country_code` (`source_country_code`), KEY `idx_source_organization` (`source_organization`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
I was testing with and without index on all the counted values, they help but not massively (maybe 20-30% faster with indexes). I have not tried any composite indexes yet.