INDEX(result, event_type, event_timestamp) obviates the need for INDEX(result) and INDEX(result, event_type).

Blindly using (255) hurts indexes and queries. Trim back to realistic limits.

Splitting up the table the way you suggested helps nothing and hurts most queries. In particular, then you would not be able to effectively use the multiple indexes, nor would you be able to use a 'composite' index (since it would involve more than one table). On the other hand, if your 'log' gets to be too huge, then such 'normalization' will drastically shrink the disk footprint. This, itself, has some positive impact on performance.

Don't use LEFT unless you need the 'right' table to be optional. LEFT sometimes implies that the 'left' table must be scanned first. In your example, that would lead to a full scan of events.

If you change LEFT JOIN to JOIN (in the last example), then the Optimizer would pick among the table to decide which one to start with. This would be equivalent (but slower) than the original case of single-column indexes on the relevant columns.

"Low cardinality" columns are virtually useless to index by themselves. They can be effective when the first column of a 'composite' index.

Most tables have a limited number of queries that will realistically be applied. If you are saying that your table begs for lots of different queries, then my advice is as follows:

• Monitor what queries people want. Keep track of the typical combinations of columns.
• Implement a few 2- and 3-column indexes.
• Be sure to have the first column(s) in the index be column(s) that are tested (in WHERE) with =. If there is also a "range" (as in your example with timestamp), then put it last. (Punt on multiple range tests.)

MySQL does not implement "bitmap" indexes; they are rarDitto for whately worth the effort. MySQL does implement "index merge intersect" (for ANDs) which is a clumsy way to simulate a composite index. "index merge union" (for ORs) is sometimes handy for OR; but UNION is likely to be as good.

Yours seems like a "Data Warehouse" application. The best speedup for such is to build and maintain Summary table. For your one example, a summary of daily counts broken down by result and event_type would be much smaller and much faster to query. (10x speedup is quite possible.) Furthermore, it is practical to have different indexes on the Summary table, thereby somewhat breaking the log jam you currently have.

INDEX(result, event_type, event_timestamp) obviates the need for INDEX(result) and INDEX(result, event_type).

Blindly using (255) hurts indexes and queries. Trim back to realistic limits.

Splitting up the table the way you suggested helps nothing and hurts most queries. In particular, then you would not be able to effectively use the multiple indexes, nor would you be able to use a 'composite' index (since it would involve more than one table). On the other hand, if your 'log' gets to be too huge, then such 'normalization' will drastically shrink the disk footprint. This, itself, has some positive impact on performance.

Do not normalize 'continuous' values such as timestamp. This severely hurts performance because it makes it impractical to have an index on a "range" value.

Don't use LEFT unless you need the 'right' table to be optional. LEFT sometimes implies that the 'left' table must be scanned first. In your example, that would lead to a full scan of events.

If you change LEFT JOIN to JOIN (in the last example), then the Optimizer would pick among the tables to decide which one to start with. This would be equivalent (but slower) than the original case of single-column indexes on the relevant columns.

"Low cardinality" columns (status and result) are virtually useless to index by themselves. They can be effective when the first column of a 'composite' index.

Most tables have a limited number of queries that will realistically be applied. If you are saying that your table begs for lots of different queries, then my advice is as follows:

• Monitor what queries people want. Keep track of the typical combinations of columns.
• Implement a few 2- and 3-column indexes.
• Be sure to have the first column(s) in the index be column(s) that are tested (in WHERE) with =. If there is also a "range" (as in your example with timestamp), then put it last. (Punt on multiple range tests.)
• Remember that the order of ANDing things in WHERE does not matter, but the order of columns in an INDEX does.
• More on creating optimal indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

MySQL does not implement "bitmap" indexes; they are rarely worth the effort. MySQL does implement "index merge intersect" (for ANDs) which is a clumsy way to simulate a composite index. "index merge union" (for ORs) is sometimes handy for OR; but UNION is likely to be as good.

Yours seems like a "Data Warehouse" application. The best speedup for such is to build and maintain Summary tables. For your one example, a summary of daily counts broken down by result and event_type would be much smaller and much faster to query. (10x speedup is quite possible.) Furthermore, it is practical to have different indexes on the Summary table, thereby somewhat breaking the log jam you currently have. (You would SUM the subtotals to get the total COUNT.)