24

How to create an index to filter a specific range or subset of the table in MySQL? AFAIK it's impossible to create directly but I think it's possible to simulate this feature.

Example: I want to create an index for NAME column just for rows with STATUS = 'ACTIVE'

This functionality would be called a filtered index in SQL Server and a partial index in Postgres.

9

MySQL currently doesn't support conditional indexes.

To achive what you are asking (not that you should do it ;) ) you can start creating an auxiliary table:

CREATE TABLE  `my_schema`.`auxiliary_table` (
   `id` int unsigned NOT NULL,
   `name` varchar(250), /* specify the same way as in your main table */
   PRIMARY KEY (`id`),
   KEY `name` (`name`)
);

Then you add three triggers in the main table:

delimiter //

CREATE TRIGGER example_insert AFTER INSERT ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   END IF;
END;//

CREATE TRIGGER example_update AFTER UPDATE ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   ELSE
      DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
   END IF;
END;//

CREATE TRIGGER example_delete AFTER DELETE ON main_table
FOR EACH ROW
BEGIN
   DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
END;//

delimiter ;

We need delimiter // because we want to use ; inside the triggers.

That way, the auxiliary table will contain exactly the IDs corresponding to the main table rows that contain the string "ACTIVE", being updated by the triggers.

To use that on a select, you can use the usual join:

SELECT main_table.* FROM auxiliary_table LEFT JOIN main_table
   ON auxiliary_table.id = main_table.id
   ORDER BY auxiliary_table.name;

If the main table already contains data, or in case you make some external operation that changes data in an unusual way (E.G.: outside MySQL), you can fix the auxiliary table with this:

INSERT INTO auxiliary_table SET
   id = main_table.id,
   name = main_table.name,
   WHERE main_table.status="ACTIVE";

About the performance, probably you'll have slower inserts, updates and deletes. This can make some sense only if you really deal with few cases where the condition desired is positive. Even that way, probably only testing you can see if the space saved really justifies this aproach (and if you are really saving any space at all).

7

If I understand the question correctly, I think what would accomplish what you're trying to do is to create an index on both columns, NAME and STATUS. That would efficiently allow you to query where NAME='SMITH' and STATUS='ACTIVE'

  • 1
    Ok, but this is not space efficient if you has relatively few lines with status ACTIVE. – Maniero Jan 4 '11 at 19:49
  • No, it is not, but that was not a requirement in the question, and it wasn't stated that the table was heavily weighted to one of the values. For that I would create a materialized view of the STATUS you're looking for, but MySQL doesn't support those. – BlackICE Jan 4 '11 at 19:53
  • and disk space is cheap... – BlackICE Jan 4 '11 at 19:54
  • 2
    Yes, it's not a direct requirement, so I started the comment with an OK. I looking for some professional alternatives. And professional alternatives always looking for most efficient way to do your tasks. Your answer probably is the most obvious one. No problem with that. But I totally disagree with "disk space is cheap", not because it's expensive, of course is cheap however memory is not so cheap, memory has low limits and index should live primarily on memory to be efficient. Disk access is not so cheap.Your answer certainly is one correct way to achieve the goal but I doubt it is the best. – Maniero Jan 4 '11 at 20:13
  • I would disagree on the memory also, it is pretty cheap these days as well (certainly not as cheap as disk space, but at $10/gig for some of it, I'd say you can splurge a bit :) – BlackICE Jan 5 '11 at 13:40
6

You cannot do conditional indexing, but for your example, you can add a multi-column index on (name,status).

Even though it will index all the data in those columns, it will still help you find the names you are looking for with the status "active".

4

You could do this by splitting the data between two tables, using views to union the two tables when all the data is needed, and indexing only one of the tables on that column - but I think this would cause performance problems for queries that need to run over the whole table unless the query planner is more clever than I give it credit for. Essentially you would be manually partitioning the table (and appying the index to only one of the partitions).

Unfortunately the built-in table partitioning feature will not help you in your quest as you can not apply an index to a single partition.

You could maintain an extra column with an index and only have a value in that column when the condition you want the index to be based upon is true, but this is likely to be labour intensive and of limited (or negative) value in terms of query efficiency and space saving.

  • I would NOT have two tables just to have better indexing, as the join will still be expensive won't it? – jcolebrand Jan 5 '11 at 5:53
  • @jcolebrand: it would be more expensive for general queries (over the views doing a union), you would need to specifically select from the partition table to use the index. Built-in partitioning would do this for you efficiently, but only the way Bigown wants (to save space) if it supported partition specific indexes. I said he could do it, not that he would want to! – David Spillett Jan 5 '11 at 10:10
0

MySQL now has virtual columns, which can be used for indexes.

  • 3
    How can this feature be used to simulate a filtered index? – ypercubeᵀᴹ Dec 17 '17 at 12:28
  • 1
    @yper-trollᵀᴹ, druud62 might be thinking of Oracle: dbfiddle.uk/… — MySQL doesn't seen to treat NULLs the same way though: dbfiddle.uk/… – Jack Douglas Dec 17 '17 at 20:42
  • @JackDouglas perhaps. (isn't that just an index optimization that saves space by the way? In other words could select count(*) from foo where id is null ; use an index?) – ypercubeᵀᴹ Dec 17 '17 at 20:46
  • @yper-trollᵀᴹ Oracle doesn't index rows where all the indexed columns are NULL (use-the-index-luke.com/sql/where-clause/null/index) — and a virtual column could be on decode(status,'ACTIVE',name,null) for example. – Jack Douglas Dec 17 '17 at 20:50
  • Thnx, I thought that had changed in recent versions (and nulls were indexed). – ypercubeᵀᴹ Dec 17 '17 at 20:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.