Organize IDB tables in subfolders in MySQL data folder
Analeea last edited by
I can't seem to find the answer elsewhere, and i apologize if the question has been done before, but is it a way to force a MySQL server (community edition) to organize the idb files into subfolders ?
Currently, all the files are located into the data in programData (for Windows). I have 1 table (1 idb file) per week, but i'd like them to be automatically organized by year or month of creation. This to avoid having a folder with +1000 and ease up maintenance.
Thanks in advance !
Multiple tables and is bad for maintenance and code and MySQL. You should not be touching .ibd files in the file system. There is nothing you can do with the files that would not risk damaging the data.
Furthermore having one-table-per-week (or whatever) is a bad schema design. That discussion has been had repeatedly on this forum as well as StackOverflow.
The alternative is
PARTITION BY RANGE (TO_DAYS(...))and explicitly (say, weekly)
DROP(if you need to purge) and
REORGANIZEto create a new partition.
Perhaps the only advantage of using
PARTITIONingfor a time-series is the efficiency of deleting old data via
DROP PARTITION. I discuss this, and more, in http://mysql.rjweb.org/doc.php/partitionmaint
OK, for your desire to efficiently migrate a week's data to somewhere else, see "transportable tablespaces" for turning a
TABLE, and then moving it to another machine. There, you could re-hook it up to a partitioned table.