CODEWITHSUNDEEP
mysqlindexing
Seb
Seb

Reputation: 25147

MySQL: create key for non existing column

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?

Upvotes: 2

Views: 372

Answers (1)

Sir Rufo
Sir Rufo

Reputation: 19096

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.

Upvotes: 1

Related Questions