Index for one column only for specific other column value

I' ve got table logs, where there are, among others, two fields: action (VARCHAR 45) and info (VARCHAR 10000).

There are multiple things logged to this table, and one of them is user ip when visiting page. For this situation action='ip', info='IP.ADD.RE.SS'.

Because info can have some big amount of text for specific things logged, I would like to only create INDEX that works for info field for action='ip' only so I can search for IP's quickly and do not have overgrown index with "actions".

I've already tried creating INDEX for first 15 characters, but still IP entries are about 1% of all stuff, and it seems a bit overkill for me. This entire solution has been inherited from someone else, and unfortunately there is little I can do right now to change entire architecture

Any sugestion how to do it right way? Is it even possible?

Solution

Some RDBMS products support what you're describing. It's called partial or filtered indexes by different products.

PostgreSQL has partial indexes
Microsoft SQL Server as filtered indexes
SQLite has partial indexes

MySQL does not implement this idea (they are under no obligation to implement it, since it's a nonstandard feature). There has been a request for this as a new feature: https://bugs.mysql.com/bug.php?id=76631

One workaround you can do in MySQL 5.7 to simulate a partial index is to create a virtual column where the value is NULL unless the action is 'ip'. Then index that virtual column:

ALTER TABLE logs
  ADD COLUMN ip_info VARCHAR(12) 
    AS (CASE `action` WHEN 'ip' THEN LEFT(info, 12) END),
  ADD KEY (ip_info);

Strictly speaking, that still indexes every row, but at least it doesn't store any of your values in the index except where the action is 'ip'.

P.S.: I haven't tested the above example, so apologies if there are syntax errors.