MySQL : Why is comparing primary key to a randomly generated number not using the index?

Question

Trying to select a random row from a table, based on autoincremented primary key with no holes.

The table schema :

CREATE TABLE IF NOT EXISTS `testTable` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `data` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

INSERT INTO `testTable` (`id`, `data`) VALUES
(1, 'hello'),
(2, 'world'),
(3, 'new'),
(4, 'data'),
(5, 'more and more'),
(6, 'data '),
(7, 'more rows here'),
(8, 'most rows here'),
(9, 'testing'),
(10,'last');

Queries:

1/ explain select * from testTable where id = ceil(Rand()*10) limit 1 ;

http://sqlfiddle.com/#!2/6e2b1/1

Result :

| ID | SELECT_TYPE |     TABLE | TYPE | POSSIBLE_KEYS |    KEY | KEY_LEN |    REF | ROWS |       EXTRA |
--------------------------------------------------------------------------------------------------------
|  1 |      SIMPLE | testTable |  ALL |        (null) | (null) |  (null) | (null) |   10 | Using where |

2/ explain select * from testTable where id = 7 limit 1 ;

http://sqlfiddle.com/#!2/6e2b1/2

Result:

| ID | SELECT_TYPE |     TABLE |  TYPE | POSSIBLE_KEYS |     KEY | KEY_LEN |   REF | ROWS | EXTRA |
---------------------------------------------------------------------------------------------------
|  1 |      SIMPLE | testTable | const |       PRIMARY | PRIMARY |       4 | const |    1 |       |

Why is query#1 not using the index, when ceil(rand()*10) should ideally evaluate to a constant which can then be compared to the primary key ? Shouldn't the optimizer work that way ? Or am I missing something obvious here.

Answer 1

The key can't be used with that query because RAND() is called for each row and returns a different value each time.

You may try this code instead:

SET @rand_value := CEIL(RAND()*10);
EXPLAIN SELECT * FROM testTable WHERE id = @rand_value;

It first computes a random value and assigns it to a variable, then uses it in the query.
As pointed out by aneroid, the LIMIT 1 is useless: since the condition applies to the primary key, the query will never return more than one row.

With this query, the output is:

| ID | SELECT_TYPE |     TABLE |  TYPE | POSSIBLE_KEYS |     KEY | KEY_LEN |   REF | ROWS | EXTRA |
---------------------------------------------------------------------------------------------------
|  1 |      SIMPLE | testTable | const |       PRIMARY | PRIMARY |       4 | const |    1 |       |

Answer 2

From the MySQL documentation for RAND():

RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.

So it's not comparing the Primary Key with a constant, it's a changing value every time (in this case, for each row). If you remove the LIMIT 1 in your query, you will see more rows coming up with the different PKs matched -- which shows the "re-evaluated every time" behaviour.

Edit: See Jocelyn's example as a way to generate the random number first and then get a row with the matching PK id (the LIMIT 1 is not required, btw). Similarly stated in Najzero's comment.