CODEWITHSUNDEEP
mysqlsql
VB_
VB_

Reputation: 45702

Mysql: weird behaviour with aggregation function

I have: table with about 100 million rows:

my_table
  |-id
  |-created
  |-..

I need: to select minimum created value;

The first approach: (extra slow, about infinity) 

SELECT MIN(created) FROM my_table;

The second approach: (extra fast, about 100 milliseconds) 

SELECT MIN(id) FROM my_table INTO @min_id;
SELECT MAX(id) FROM my_table INTO @max_id;
SELECT MIN(created) FROM my_table WHERE id IN (@min_id, @max_id); //WHY THIS SELECT IS SO FAST?

Question: WHY?! Why the first approach is so slow and the second so fast? It seems the same operation?

Upvotes: 0

Views: 45

Answers (2)

Konerak
Konerak

Reputation: 39773

You are confusing IN and BETWEEN

SELECT MIN(id) FROM my_table INTO @min_id;
SELECT MAX(id) FROM my_table INTO @max_id;
SELECT MIN(created) FROM my_table WHERE id IN (@min_id, @max_id); //WHY THIS SELECT IS SO FAST?

That last query will only read the first row and the last row, and take the minimum 'created' value of those two rows, since WHERE id IN (@min_id, @max_id) can also be written as WHERE id = @min_id or id = @max_id

Now, if your table is ordered, this might actually give the correct solution. But if it isn't, it will produce the wrong results.

Upvotes: 1

StanislavL
StanislavL

Reputation: 57421

You have no index on the created so to find min it must scan all the records. And obviously you have index on id (looks like it's primary key) so it finds min and max very fast. Then it just choose min of 2 values.

Upvotes: 1

Related Questions