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:

enter image description here

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.

  • in mysqö8 you have ctes, so you could first select alle the data and use the result to count, you stoill need a combined index on all parameters. – nbk Mar 2 at 22:15
  • 1. Post DDL of the table or provide the approximate row size in bytes. 2. How often is the table updated? – bbaird Mar 2 at 22:38
  • @bbaird: 1. Edited to include table structure 2. Constantly, during peak work hours the table averages ~1.5-2k new events per minute. – Eric Mar 2 at 22:52
  • @nbk CTEs may work here, I'm currently looking at this post for how to reuse the same CTE for all 7 count queries – Eric Mar 2 at 22:56
  • the link is for sql server but it works in mysql too, still don't forget to make a combined index – nbk Mar 2 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.