1

I have this central table in my schema that is getting slow to query since it hits ~6M rows. I'm planning to alter some indexes, to this on the example, to try to better fit the queries needs.

I'm also considering partitioning the table to let newst records separate from the old ones. The system oftem needs to query the last 3 months of records. The older records may be required evetually or in reports. But keep all of then is important to not allow the same records (UNIQUE KEY) to enter the system again by costumer mistake (and they try to do this every day).

  1. How do I compose the partition expression since I have a PK and an UNIQUE KEY?
  2. How much partitions seens a good start point?
  3. Partitioning this table seees like a good idea at all?

Example table:

  • The real table have 3 times more columns and some other indexes.
  • Like 7 other tables with FK pointing to this table's id.
  • A simple mysqldump of this table ganerates a 2.3GB file.
  • Now it is getting ~1M rows bigger each month.
  • mysql Ver 14.14 Distrib 5.1.73, for debian-linux-gnu (x86_64) using readline 6.1
CREATE TABLE `docs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `taxId` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `creator` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `model` int(2) NOT NULL,
  `serie` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `num` varchar(9) COLLATE utf8_unicode_ci NOT NULL,
  `creationDt` datetime NOT NULL,
  `modificationDt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `main` (`taxId`,`model`,`serie`,`num`,`creator`),
  KEY `cdt` (`creationDt`, `model`, `taxId`), 
  KEY `mdt` (`modificationDt`, `model`, `taxId`)
) ENGINE=InnoDB AUTO_INCREMENT=7412843 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
EthraZa
  • 388
  • 5
  • 9
  • 1
    `Like 7 other tables with FK pointing to this table's id.` That was a fast no go. Partitioned (innodb) tables cannot be used with foreign keys. Partioning is a tool that is useful only in very specific situations, and with your unique index I see absolutely no good use case where partitioning would help you. You should focus on (good) indexes. – Solarflare Oct 27 '16 at 21:43
  • Consider load balancing / separating read vs write servers which will give better performance. – Krish Oct 27 '16 at 21:55
  • `tax_id` -- If you have my taxId in your machine and it is hacked into, I will be coming after _your_. Hire a security consultant. – Rick James Oct 27 '16 at 22:02
  • Partitioning does not intrinsically give you any performance boost. To further study your question, please provide _all_ the important `SELECTs` in order to devise the best indexes and (possibly) partitioning. – Rick James Oct 27 '16 at 22:06
  • @RickJames Here in Brazil, the business TaxID is a public info. There is even a law that states it must be visible somewhere in the company website (almost no one complies) – EthraZa Oct 28 '16 at 13:17
  • @Solarflare thank you, for you comment. With this statement I searched for this specific and found the confirmation in the manuals (http://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html). Now I'm having a hard time to believe that is that much people out there that would use Innodb without using FK and would know how to partitioning. – EthraZa Oct 28 '16 at 13:17
  • 1
    @EthraZa As I said, I guess you overestimate what partitioning is good for. That's why it is not at all such a big deal as you may think. Partitioning is not used to speed up general performance (it will actually generally slow down most queries except very specific ones), but to solve specific problems. You have none of those. Indexes are really the tool you are looking for! Now, are you ready to hear another thing that you (maybe) cannot believe? MyISAM doesn't support foreign keys at all (not only together with partitioning). And people still use it (more than you might think). – Solarflare Oct 28 '16 at 16:23
  • FKs have limitations, even outside partitioning. I often find that FKs are more hassle than benefit. – Rick James Oct 28 '16 at 18:11
  • 1
    I have found _only_ 4 use cases for Partitioning. I list them [_here_](https://mariadb.com/kb/en/mariadb/partition-maintenance/) . One of them is for purging old data, which you have not yet mentioned. (I agree with Solareflare.) – Rick James Oct 28 '16 at 18:15

0 Answers0