Timeline for MySQL Partitioning on event tables with start and end datetime columns
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 7, 2017 at 13:35 | comment | added | Rick James |
Ah, I had not thought about NULL. Yes, that should work -- but please see my added text.
|
|
| Nov 7, 2017 at 13:34 | history | edited | Rick James | CC BY-SA 3.0 |
B and NULL
|
| Nov 7, 2017 at 3:09 | vote | accept | kezsto | ||
| Nov 7, 2017 at 3:09 | comment | added | kezsto | Think I've just found it, it will go into the lowest partition, which is fine, I can deal with that. I think all of my options are at least clear now! Many thanks for your help. | |
| Nov 7, 2017 at 2:57 | comment | added | kezsto | Ah of course, Plan B will definitely help. The only downfall in performance will be when querying large time ranges, which I don't plan to do often, and could probably use summary tables if needs be. So my only question remaining would be what happens to the records that are "current" and have a NULL end datetime? What partition will they be in? The MAXVALUE one? | |
| Nov 7, 2017 at 2:33 | comment | added | Rick James | @kezsto - (1) I think "Plan B" will significantly help your case about "recent events". (2) The partitioning to speed up purging old data is an important part of Plan B. Here is more on "big deletes": mysql.rjweb.org/doc.php/deletebig | |
| Nov 7, 2017 at 2:28 | history | edited | Rick James | CC BY-SA 3.0 |
typo
|
| Nov 6, 2017 at 21:01 | comment | added | kezsto | Cheers for the help Rick. The issue I have with partitioning by a datetime is what you acknowledged in your first point. Most of my queries ask for recent events, which means nearly all of the partitions are searched. This is making my performance worse than if I was to partition by location_id, and just index the datetimes. General query performance is of a higher importance to me than data purging ease. Because of this, do you think I should just be smarter about the way I delete and call it a day? For example, delete in smaller batch sizes? | |
| Nov 6, 2017 at 17:19 | history | answered | Rick James | CC BY-SA 3.0 |