Index if field equals specific value-CodePudding

I want to use a table for multiple types of data. For instance like a log table, that holds all types of different logs, but want to create an index that only indexes log_types = 'error_log'. Is that a possibility?

I know I can have an error_log table, but for the sake of not having too many tables (1 for each log type, potentially up to 10), I'd prefer to only have one table. I also know that I can create a index that can have log_types as the first parameter, but maybe one log might want to index by date created as the second parameter and another log would benefit by having user_id as the second parameter. So in that case it would be beneficial if I could create a different index for each and the index only contained the data from a single log_type, since the other wouldn't benefit from it.

For Example:

Using the above table. One query would be for log_type = 'error' from 1/1/2020 to 1/5/2020. And another query would be for log_type = 'auth' and user_id = 12. They would benefit from having different indexes. I could have an index that is indexed by log_type, created_at and another by log_type, user_id. But since the queries would be dependant on the log_type, it would make sense if there was a way to have the index only index when the log_type = 'error' and just not index any other log_type and same for 'auth'.

CodePudding user response：

You can store all the entries in the same, single table. Now, you can create multiple indexes for each search, as in:

create index ix1 on t (log_type, created_at);
create index ix2 on t (log_type, user_id);

Now, those two indexes will cover all the entries in the table, and not just 'error' or 'auth' respectively.

But... you want one index to only cover the 'error' entries, and another index to only cover the 'auth' entries. What you want is a "partial index". Unfortunately MySQL does not implement partial indexes [yet]. Oracle, DB2, PostgreSQL, and SQL Server do, by the way. Maybe a future version of MySQL will add them.

The syntax you are looking for adds the WHERE clause in the index creation statement. For example, in PostgreSQL you can do:

create index ix1 on t (log_type, created_at) where log_type = 'error';
create index ix2 on t (log_type, user_id) where log_type = 'auth';

Now, in many cases, having as many entries in an index won't really affect the index too much, if the index starts with the main selection column log_type. Not that big of a deal, I would say.