We are currently evaluating the use of MySQL partitions for our small application. The application basically just sits at the end of the message queue and logs our API requests (which include the timestamp) into the database using Hibernate. Unfortunately, we get a lot of queries and database queries have become very slow.
What we need to do is split the table into a timestamp (per month), since our usual query pattern is similar to "get specific queries between time A and B". If A and B are in two consecutive months, which will be mostly true, then it will just hit two sections.
Since MySQL range partitions must be created manually, I would like to add this maintenance task to our Java application, where it can be done automatically. An idea is something like:
- You have a Thread utility that runs regularly (using
ScheduledExecutorServiceor something else) - In the stream, check if there is a section for the next month
- If not, create it
This is all fine, but I'm stuck trying to get MySQL markup information with Hibernate and create partitions. What is the best way to do this (I'm fine if it will be a specific MySQL)?
- Is there a specific API in Hibernate for getting MySQL partitioning information for a table, as well as for creating partitions?
- Should I use raw SQL (
SHOW CREATE TABLE ..., ALTER TABLE ... ADD PARTITION) and parse the output myself?
EDIT:
( , ):
CREATE TABLE `request` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`apikey` varchar(32) NOT NULL,
`timestamp` datetime NOT NULL,
`rows` int(11) DEFAULT NULL,
`user_id` varchar(15) DEFAULT NULL
PRIMARY KEY (`id`),
KEY `apikey_idx` (`apikey`),
KEY `timestamp_idx` (`timestamp`),
KEY `apikey_timestamp_rows_idx` (`apikey`,`timestamp`,`rows`)
) ENGINE=InnoDB AUTO_INCREMENT=2190385211 DEFAULT CHARSET=utf8
( Doctrine, ):
SELECT
r0_.user_id AS user_id0, COUNT(r0_.id) AS sclr1
FROM
request r0_
WHERE
r0_.apikey = 'XXX' AND r0_.rows > 0 AND r0_.timestamp >= '2015-09-15 00:00:00' AND r0_.timestamp < '2015-10-15 00:00:00'
GROUP BY r0_.user_id
HAVING sclr1 > 0
ORDER BY sclr1 DESC
LIMIT 500
EXPLAIN MySQL apikey_timestamp_rows_idx.
: , API, rows > 0 .
2.2bn .