1

I have a query like this for finding the max/min values of primary key, which satisfy the where condition. 

SELECT id 
FROM mytable 
WHERE date_created >= '2015-01-27 00:00:00' 
  AND date_created <= '2015-01-27 23:59:59' 
LIMIT 1

and EXPLAIN 

+----+-------------+------------------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+------------------------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | mytable | ALL  | NULL          | NULL | NULL    | NULL | 44726469 | Using where |
+----+-------------+------------------------+------+---------------+------+---------+------+----------+-------------+

and for maximum, 

SELECT id 
FROM mytable 
WHERE date_created >= '2015-01-27 00:00:00' 
  AND date_created <= '2015-01-27 23:59:59' 
ORDER BY id DESC 
LIMIT 1

and EXPLAIN will give

+----+-------------+------------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table                  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | mytable | index | NULL          | PRIMARY | 4       | NULL |    1 | Using where |
+----+-------------+------------------------+-------+---------------+---------+---------+------+------+-------------+

As I am running this queries against a very large table, the query is getting time out. Is there any alternative method for finding these values?

id is the primary key and I already created index for date_created. But doesn't have any effect on the performance.

Happy Coder
  • 4,255
  • 13
  • 75
  • 152
  • 1
    "As I am running this queires against a very large table, the query is getting timedout" --- mysql does not timeout queries - it runs them regardless how terrible they are. – zerkms Jan 29 '15 at 09:05
  • Now objectives: 1. you missed `ORDER BY` in the first query 2. You didn't provide `EXPLAIN` and the actual `CREATE TABLE` – zerkms Jan 29 '15 at 09:05
  • You are selecting with TWO columns in your `where` clause - `date_created` and `created_at`. So you should at least make a single index which covers BOTH these columns, not just `date_created`. This may not solve your problem entirely, but should be a step towards better performance. And since you're only needing to get `id`, you could even make an index with three columns (`date_created`, `created_at`, and `id`) so the query can use only index to get the values, without any need to even read the data file. – Tomas M Jan 29 '15 at 09:18
  • @a_horse_with_no_name what if it's innodb? – zerkms Jan 29 '15 at 09:19
  • @TomasMatejicek for a range comparison the `created_at, id` part of the suggested index will **never** be used. So effectively your index is no different from a single column `date_created` – zerkms Jan 29 '15 at 09:20
  • @zerkms you're right. However I'm unsure about the situation where id is selected with an aggregate function like MIN() or MAX(), as like SELECT MIN(id) FROM mytable WHERE date_created >= '2015-01-27 00:00:00' AND created_at <= '2015-01-27 23:59:59' – Tomas M Jan 29 '15 at 09:22
  • It's too late to use index for `MIN` as soon as `WHERE` contains predicates that don't use index – zerkms Jan 29 '15 at 09:23
  • I tried with MIN(id), MAX(id) and it is also taking lot of time. I am getting the error Lost connection to mysql server and I think this is timeout. – Happy Coder Jan 29 '15 at 09:25
  • @a_horse_with_no_name because in innodb the clustered primary key is implicitly added to every key. So whenever you create an index for `created_at` column - it in fact is created as `(created_at, id)` implicitly. If you create such manually, then `id` will be added twice. – zerkms Jan 29 '15 at 09:27
  • I have added EXPLAIN results. – Happy Coder Jan 29 '15 at 09:34
  • And `CREATE TABLE`? **AN IMPORTANT NOTE**: all the queries and the `CREATE TABLE` must be **ORIGINAL, NOT MODIFIED IN ANY WAY** (yep, I see you're modifying it already, could you please not do that?) – zerkms Jan 29 '15 at 09:37
  • @zerkms while mysql does not timeout queries before 5.7 a query could fail because of lock acquisition timeouts. – Nick Hristov Nov 20 '17 at 17:19

2 Answers2

0

creating a trigger after insertion and deletion is a possible solution and maintaining a two row table for storing max,min value for primary key.

Prinz Km
  • 323
  • 1
  • 12
  • Trying to optimize such a trivial (see fast) operation following your advice in 99% of cases is a non-win way – zerkms Jan 29 '15 at 09:18
-1

can you try this
SELECT MIN(id),MAX(id) FROM mytable WHERE date_created >= '2015-01-27 00:00:00' AND created_at <= '2015-01-27 23:59:59

abdelaziz maroc
  • 117
  • 1
  • 2
  • 14