SELECT query slow when flag column is a constraint

Question

I have a fairly simple table called widgets. Each row holds an id, a description, and an is_visible flag:

CREATE TABLE `widgets` (
  `id` int auto_increment primary key, 
  `description` varchar(255), 
  `is_visible` tinyint(1) default 1
);

I'd like to issue a query that selects the descriptions of a subset of visible widgets. The following simple query does the trick (where n and m are integers):

  SELECT `description` 
    FROM `widgets` 
   WHERE (`is_visible`) 
ORDER BY `id` DESC 
   LIMIT n, m;

Unfortunately this query, as written, has to scan at least n+m rows. Is there a way to make this query scan fewer rows, either by reworking the query or modifying the schema?

score 2 · Accepted Answer · answered Feb 09 '10 at 04:09

2

Use indexes for faster query result:

ALTER TABLE `widgets` ADD INDEX ( `is_visible` )

answered Feb 09 '10 at 04:09

Emre Yazici

10,136
6
48
55

The index helps, but MySQL still needs to scan n+m rows. I was wondering if there was a way to have MySQL scan fewer rows (ideally m rows) but apparently given the way LIMIT is implemented n+m is the best MySQL can do. – jfpoole Feb 09 '10 at 15:57

score 1 · Answer 2 · edited May 23 '17 at 10:32

Is there a way to make this query scan fewer rows?

No, not really. Given that it's a binary flag, you wouldn't get much benefit from creating an index on that field.

I will elaborate, given the downvote.

You have to take into consideration the cardinality (# of unique values) of an index. From the MySQL Manual:

The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

On that field would be 2. It doesn't get much lower than that.

See also: Why does MySQL not use an index on a int field that's being used as a boolean?
Indexing boolean fields

Since the table has variable length records you can benefit from index. — Emre Yazici, Feb 09 '10 at 04:11

SELECT query slow when flag column is a constraint

2 Answers2