In mysql we many times need to know the first id of current or last partition to feed it in select queries. For instance, if you want to scan last 2-3 partitions for given conditions. This is what my requirement was when I wrote this query to fetch the starting (first) id of the given partition.

SELECT floor(max(col_name) / 10000000) * 10000000 - 10000000 * n from table_name;


col_name is the name of the column on which our table is partitioned.

n is the partition number

0 → current partition 
1 → last partition 
2 → second last partition and so on

table_name: is the partitioned table

10000000 is the size of every partition (This query works when size of every partition is equal)

For instance
I want to fetch the rows having status = 2 from last week's entries.

select * from table_name where status = 2 and created_at > '04-01-2016'

This query is not using any partition hence will be taking huge time, even when created_at is indexed.


(Scanning 3 partitions as I know 3 partitions are used in a week)

select * from table_name where status = 2 and column_name > (SELECT floor(max(col_name) / 10000000) * 10000000 - 10000000 * 3 from table_name;);

Using partitions instead of index will make your query a lot faster.

Creating stored function

CREATE FUNCTION partitionFirstId (partitionId INT(12) UNSIGNED) RETURNS INT(12) DETERMINISTIC RETURN (SELECT floor(max(order_id) / 10000000) * 10000000 - 10000000 * partitionId from fulfillment_recharge)

So that you don't have to write the whole sub query every time but just a function call-

select * from table_name where status = 2 and column_name > (select partitionFirstId(?))

And to view list of stored functions/procedure created in your mysql, you can refer: information_schema.routines


Stayed tuned for more mysql hacks.