0

I have this table:

CREATE TABLE `location` (
  `id` int(11) NOT NULL,
  `lat` DOUBLE NOT NULL,
   #...
  `date` datetime NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  KEY `pk` (`id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (MONTH(date))
(PARTITION part0 VALUES LESS THAN (2),
 PARTITION part1 VALUES LESS THAN (3),
 PARTITION part2 VALUES LESS THAN (4),
 PARTITION part3 VALUES LESS THAN (5),
 PARTITION part4 VALUES LESS THAN (6),
 PARTITION part5 VALUES LESS THAN (7),
 PARTITION part6 VALUES LESS THAN (8),
 PARTITION part7 VALUES LESS THAN (9),
 PARTITION part8 VALUES LESS THAN (10),
 PARTITION part9 VALUES LESS THAN (11),
 PARTITION part10 VALUES LESS THAN (12),
 PARTITION part11 VALUES LESS THAN MAXVALUE);

I use partition by Month function to speed up the queries because I have a huge amount of data in this table.

But when I tried a query like this:

Explain SELECT * FROM test.new_table where date between '2017-02-02' and '2017-04-25';

I got this result:

id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
1 | SIMPLE  | new_table | part0,part1,part2,part3,part4,part5,part6,part7,part8,part9,part10,part11  | ALL      |           8   | 12.50 |   Using where

I see that I can't benefit from Pruning feature of MySQL.

What I can do else to enhance incoming queries ?

Edit

The most used case to get data is Between 2 dates.

Examples of queries:

SELECT 
    l.*,
    u.name
FROM
    locations l
    LEFT JOIN `user` `u` ON `l`.`user` = `u`.`id`
WHERE
    l.`date` BETWEEN `u`.`date_start` AND `u`.`date_end`
 OR (ISNULL(`u`.`date_end`) AND (`l`.`date` >= `u`.`date_start`))

2

SELECT 
    l.*,
    u.name
FROM
    locations l
    LEFT JOIN `user` `u` ON `l`.`user` = `u`.`id`
WHERE
    l.id in (...)
  • It is a common misconception that partitioning is used to speed up queries. It is not (except for some very specific conditions, which, with 99,24% certainty, are not relevant here). Indexes are used for that. For some queries, partioning will be equally fast as not partitioning. For every other query, it will be slower. So, as I said: the solution is to use indexes. So add an index on (just) the date-column (without the id first). It will speed up your query (despite your partitioning). To make it even faster, you could remove your partitioning (but I don't know the rest of your setup). – Solarflare Mar 22 '17 at 16:28
  • @Solarflare thanks for this information, Actually the table used to store latlngs data, So it contains a huge number of record. As I read if I partition it for every month the queries will be more faster. – wajih Mar 22 '17 at 17:04
  • 1
    Add the index on date. It is the really important step. Unless you get more than like 20%-25% of all your rows with your filter, it will speed up your query. Otherwise a full table scan might still be faster (although a covering index with date as the first column and all other columns after that will still be faster, but will require more space); in that case (and no covering index) you can speed it up by manually specifying partitions, but you have to list them yourself in the query - you can do that in you app/client if you analyze the input range and then list the required partitions. – Solarflare Mar 22 '17 at 17:57
  • @Solarflare, I edited my question, I managed to limit the query 3 months ago only, The most used queries will be between 1 day period. – wajih Mar 23 '17 at 7:20
  • 1
    Well, these are completely different queries, and both cannot use your partitioning at all (and will actually be a bit slower when you keep the partitioning). Your first new query needs the index location(user, date). Also replace left join with a join and your where with where l.date >= u.date_start and (isnull(u.date_end) or l.date <= u.date_end). Your second new query is fine. – Solarflare Mar 23 '17 at 10:32
1

BY RANGE(MONTH(date)) does not prune.

Even if id did prune, it would probably not provide any performance benefit over having a non-PARTITIONed table with a suitable index. May we see your queries?

BY RANGE(TO_DAYS(date)) does prune. But you need to add a new partition periodically, and (optionally) DROP an old partition.

Blog on partitioning.

  • I edited my question, I managed to do Slave database for every year, So I think divide the table by month is a good idea. – wajih Mar 23 '17 at 5:52
  • Yes. You can still partition by month as given below: – SQL.RK Mar 23 '17 at 14:46
  • By months, yes; by MONTH(), no. – Rick James Mar 23 '17 at 15:08
1

Table can be partitioned monthly with pruning as given below:

In addition, Index needs to be created to retrieve specific day's data from the partition holding one month's data. However this index scan will be smaller and efficient when compared to the Index scan on Non-Partitioned tables.

PARTITION BY RANGE COLUMNS(dt) (
PARTITION p20170101 VALUES LESS THAN ('2017-01-01'),
PARTITION p20170201 VALUES LESS THAN ('2017-02-01'),
PARTITION p20170301 VALUES LESS THAN ('2017-03-01'),...
  • I like to name the partitions by the month it contains instead of the month after. – Rick James Mar 23 '17 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.