CODEWITHSUNDEEP
mysqlsqldatabaserelational-databasepartition
Malkhazi Dartsmelidze
Malkhazi Dartsmelidze

Reputation: 4992

Partitioning large table by dates

I have implemented custom url shortener in my app and I have one table for that. table structure looks like this:

CREATE TABLE `urls` (
  `id` int(11) NOT NULL,
  `url_id` varchar(10) DEFAULT NULL,
  `long_url` varchar(255) DEFAULT NULL,
  `clicked` mediumint(5) NOT NULL DEFAULT 0,
  `user_id` varchar(7) DEFAULT NULL,
  `type` varchar(15) DEFAULT NULL,
  `ad_id` int(11) DEFAULT NULL,
  `campaign` int(11) DEFAULT,
  `increment` tinyint(1) NOT NULL DEFAULT 0,
  `date` date DEFAULT NULL,
  `del` enum('1','0') NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

ALTER TABLE `urls`
  ADD PRIMARY KEY (`id`),
  ADD KEY `url_id` (`url_id`),
  ADD KEY `type` (`type`),
  ADD KEY `campaign` (`campaign`),
  ADD KEY `ad_id` (`ad_id`),
  ADD KEY `date` (`date`),
  ADD KEY `user_id` (`user_id`);

The table now has 20.000.000 records and currently growing by 300k-400k records per day.

url_id column is unique varchar(10) and url looks like that: http://example.com/asdfghjklu

Now i have partitioned this table into 10 partitions by HASH(id):

PARTITION BY HASH (`id`)
PARTITIONS 10;

When I try to generate reports and join this table on others query is getting really slow, so slow even can't get 1 week report.

When I try to make big query in this table I filter almost every query with dates and I think it will be much better if I partition this table by date column. Is it good idea?

As I read if I want to partition this table by date I need to add date in composite primary key: PRIMARY KEY(id, date)

What do you think about this? How do I improve my query performance?

Upvotes: 1

Views: 374

Answers (1)

vishal
vishal

Reputation: 352

I wold recommend use hash partition using date or month or YEAR 

CREATE TABLE `urls` (
  `id` int(11) NOT NULL,
  `url_id` varchar(10) DEFAULT NULL,
  `long_url` varchar(255) DEFAULT NULL,
  `clicked` mediumint(5) NOT NULL DEFAULT 0,
  `user_id` varchar(7) DEFAULT NULL,
  `type` varchar(15) DEFAULT NULL,
  `ad_id` int(11) DEFAULT NULL,
  `campaign` int(11) DEFAULT,
  `increment` tinyint(1) NOT NULL DEFAULT 0,
  `date` date DEFAULT NULL,
  `del` enum('1','0') NOT NULL DEFAULT '0',
    PartitionsID int(4) unsigned NOT NULL,
   KEY PartitionsID (PartitionsID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY HASH (PartitionsID)
PARTITIONS 366;

IN PARTITION ID you just need to insert TO_DAYS(date) so you have only one value for entire day . SOURCE

and it will make easy for partition for each day or you can do with month wise also depending on your data size .

for select you can use below query as example 

SELECT *    
            FROM TT ACT
            WHERE ACT.CustomerID = vCustomerID 
              AND ACT.TransactionTime BETWEEN vInvoiceEndDate AND vPaymentDueDate 
              AND ACT.TrxnInfoTypeID IN (19, 23) 
              AND ACT.PaymentType = '1'
    AND ACT.PartitionsID BETWEEN TO_DAYS(vInvoiceEndDate) AND TO_DAYS(vPaymentDueDate);

Upvotes: 1

Related Questions