MySQL index for non-empty string query

Question

I have the table posts and its column descr in my application. And I need to query posts where description is not empty, but the table has too many rows, so I need to add an index. What is the best way to add this index?

Table structure (simplified):

CREATE TABLE posts (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255),
    descr VARCHAR(1024),
    PRIMARY KEY(id)
) engine=InnoDB DEFAULT CHARSET=utf8;

Example of query:

SELECT * FROM posts WHERE descr <> '';

I don't want to create an index on the whole descr column, because it will be huge overhead.

Also I know variant about adding another column is_empty_descr BOOLEAN and add index to it. This solution I will use if no other variants would be found.

I tried to add INDEX( descr(1) ), but I couldn't find the way how to use it:

desrc <> '' - index is not used
LEFT(desrc, 1) = '' - index is not used
SUBSTR(desrc, 0, 1) = '' - index is not used
desrc LIKE 'a%' - index is used! But this is totally different case

In all my examples I see something like this:

mysql> EXPLAIN SELECT * FROM posts WHERE descr <> '';
+------+---------------+------+---------+------+------+-------------+
| type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+---------------+------+---------+------+------+-------------+
| ALL  | descr_1       | NULL | NULL    | NULL |   42 | Using where |
+------+---------------+------+---------+------+------+-------------+

(I omited some result columns, because the table is too wide for this site)

Even if I pass FORCE index (descr_1) result will be the same.

"but I couldn't find the way how to use it." - eh, by performing your query using EXPLAIN to see whether the index is actually used would be a way. — Ja͢ck
– Ja͢ck, Commented Feb 26, 2015 at 11:57
Of course, but mysql does not use this index in almost all cases that i test: desrc <> '' - index is not used; LEFT(desrc, 1) = '' - index is not used; SUBSTR(caption, 0, 1) = '' - index is not used; — Yuriy Novikov
– Yuriy Novikov, Commented Feb 26, 2015 at 13:42
Is that because there's another index on that table which is used instead? — Ja͢ck
– Ja͢ck, Commented Feb 26, 2015 at 13:43
No, mysql simply ignoring this index, see example I added above. — Yuriy Novikov
– Yuriy Novikov, Commented Feb 26, 2015 at 14:02
Well, that's rather unfortunate then; the workaround by using a boolean value it is then (unless someone comes up with something brilliant), you could use triggers to make sure the values don't go out of sync. — Ja͢ck
– Ja͢ck, Commented Feb 26, 2015 at 14:04

Gordon Linoff · Accepted Answer · 2015-02-26 11:56:53Z

2

In MySQL, you can add a description on a prefix of a string using this syntax:

create index idx_posts_descr1 on posts(descr(1));

You should test this to see if the index is used for that particular where clause, though.

answered Feb 26, 2015 at 11:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL index for non-empty string query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related