MariaDB: Querying a timestamp partition I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do &gt; sometime &gt; sometime and between sometime and anothertime. However, if I do anything other than a straight equality check, MariaDB uses all the partitions.

This is how I have my table setup:

CREATE TABLE IF NOT EXISTS events
(
  time TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
  id BIGINT NOT NULL AUTO_INCREMENT,
  CONSTRAINT id PRIMARY KEY (id, time)
)
-- Need to take the FLOOR. See  https://mariadb.com/kb/en/mariadb/unix_timestamp/
PARTITION BY RANGE (FLOOR(UNIX_TIMESTAMP(time))) (
  PARTITION p20150801 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-01 00:00:00'))),
  PARTITION p20150807 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-07 00:00:00'))),
  PARTITION p20150814 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-14 00:00:00'))),
  PARTITION p20150821 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-21 00:00:00'))),
  PARTITION p20150828 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-28 00:00:00'))),
  PARTITION p20150830 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-30 00:00:00')))
);


And this is how my query is using the partions.

MariaDB [events]&gt; explain partitions select * from events where time &gt; TIMESTAMP("2015-08-27T01:02:03");                                                                                                           
+------+-------------+--------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | partitions                                                  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | events | p20150801,p20150807,p20150814,p20150821,p20150828,p20150830 | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+------+-------------+--------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [events]&gt; explain partitions select * from events where time = TIMESTAMP("2015-08-27T01:02:03");                                                                                                           
+------+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | events | p20150828  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


Any suggestions would be greatly appreciated. This has me tearing out my hair.
Your expression is too complex.  There are many severe limitations of what can go into BY RANGE.  Use simply UNIX_TIMESTAMP(time).  Even if it would work, FLOOR does not add anything to the expression.

BTW, the trailing hh:mm:ss is optional in this situation.  These are identical:

UNIX_TIMESTAMP('2015-08-01 00:00:00')
UNIX_TIMESTAMP('2015-08-01')


Now, on to my favorite question:  Why are you using PARTITIONing?  PARTITIONingdoes not inherently provide any performance.  There are only a few use cases I know of where you can trick PARTITION into providing more performance than a carefully chosen index.