MySQL: create key for non existing column

Question

I have the following table with 1,000,000+ records:

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What I'd like to do is be able to fetch products with null description quickly - forget about empty string.

If I add a key by description, the whole description will be indexed and I don't want such a big index. I could indeed do:

ALTER TABLE products ADD KEY has_description (description(1));

This will create an index with only the first character. This is much better than having a "complete" index, but I'd like to know if there's a way to create a proper index - e.g. a boolean index, true/false depending on the product having a description or not respectively.

Additional requirement is not adding a new column with this value - that is trivial, but it's duplicated information I don't want to have in the table.

Already tried stuff like

ALTER TABLE products ADD KEY has_description (description IS NULL);

... but didn't work.

Can this be done at all?

Have you seen this http://stackoverflow.com/questions/1528665/does-mysql-5-0-index-null-values ? — mindas, Dec 20 '12 at 18:11
Thanks mindas. I know IS NULL searches take into consideration existing indexes. What I'd like to avoid is having a big index in the first place and build an index with only 2 possible values: 0 and 1 without creating a new field for this. — Seb, Dec 20 '12 at 18:13

score 1 · Answer 1 · edited Dec 20 '12 at 19:55

1

You can only have Index on existing fields.

The answer is: no way

But you can add another Field (and Index for that) containing the description state (empty or not). Use Insert and Update trigger to have that field always synced with description data.

edited Dec 20 '12 at 19:55

Seb

24,920
5
67
85

answered Dec 20 '12 at 19:28

Sir Rufo

18,395
2
39
73

MySQL: create key for non existing column

1 Answers1