Organize IDB tables in subfolders in MySQL data folder



  • 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 REORGANIZE to create a new partition.

    Perhaps the only advantage of using PARTITIONing for 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 PARTITION into a TABLE, and then moving it to another machine. There, you could re-hook it up to a partitioned table.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2