Mysql – Question on MySQL Partitioning

MySQLmysql-5.5partitioning

Using version 5.5 (on which I'm stuck), I have a table I want to partition, here is the DDL:

CREATE TABLE `emp` (
  `id` int(11) DEFAULT NULL,
  `date1` bigint(20) DEFAULT NULL,
  `gen_year` int(11) GENERATED ALWAYS AS (year(from_unixtime(`date1`))) VIRTUAL,
  `gen_month` int(11) GENERATED ALWAYS AS (month(from_unixtime(`date1`))) VIRTUAL,
  UNIQUE KEY `id` (`id`,`gen_year`,`gen_month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (12*gen_year+gen_month) PARTITIONS 77 ;

As you can see I want to partition by unique month (taking the year into account). I set 77 partitions because the test data I have put in there uniformly cover 77 months.

When I run the 1^st query below, I get pruning just fine, only one partition gets queried.
When I run the 2^nd query, I'd expect just a few partitions to be queried, but instead ALL are.

select count(*) from emp where gen_year=1984 and month=8;
select count(*) from emp where gen_year=1984 and month>8;
select count(*) from emp where gen_year=1984 and month BETWEEN 8 AND 12;

EXPLAIN PARTITIONS gives one partition for the first query, but all 77 partitions for the others.

Please advise on what I'm missing.

Best Answer

Any range (eg month>8 or month BETWEEN 8 AND 12) leads to punting on partition pruning, hence 77 partitions are selected. That is, PARTITION BY HASH is useless for ranges. Meanwhile, non-partitioning is excellent for the other type of queries -- "point queries". So don't bother using BY HASH!

Where does id come from? Typically it is an AUTO_INCREMENT, but apparently not for your table.

select count(*) from emp where gen_year=1984 and month=8;

can be significantly sped up by

Storing date1 as either a TIMESTAMP or DATETIME. With fractional seconds if needed.
Adding INDEX(date1)
Writing the query thus:

SELECT COUNT(*) FROM EMP WHERE date1 >= '1984-08-01' AND date1 < '1984-08-01' + INTERVAL 1 MONTH

If you build and maintain a Summary table, then the query becomes 'instantaneous', and even the other query will be faster than

     WHERE date1 >= '1984-09-01'
       AND date1  < '1985-01-01'

InnoDB really needs a PRIMARY KEY. Promote the UNIQUE key to primary. (Though I have serious doubts about that index.)

These are all "range" tests: BETWEEN, >=, <. All may work well with non-partitioned tables, but fail miserably on PARTITION BY HASH tables (when the range involves the partition-key).

Related Solutions

MySQL optimization – year column grouping – using temporary table, filesort

I don't see a lot of opportunity for improvement.

The index you added was probably a big help, because it's being used for the range matching on the WHERE clause (type => range, key => tran_date), and it's being used as a covering index (extra => using index), avoiding the need to seek into the table to fetch the row data.

But since you're using functions to construct the financial_year value for the group by, both the "using filesort" and "using temporary" can't be avoided. But, those aren't the real problem. The real problem is that you're evaluating MONTH(tran_date) 346,485 times and YEAR(tran_date) at least that many times... ~700,000 function calls in one second doesn't seem too bad.

Plan B: I am definitely not a fan of storing redundant data, and I'm dead-set against making the application responsible for maintaining it... but one option I might be tempted to try would be to create a dashboard_stats_by_financial_year table, and use after-insert/update/delete triggers on the transactions1 table to manage keeping those stats current.

That option has a cost, of course -- adding to the amount of time it takes to update/insert/delete a transaction... but, waiting > 1200 milliseconds for stats for your dashboard is a cost, too. So it may come down to whether you want to pay for it now or pay for it later.

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Best Answer

Related Solutions

MySQL optimization – year column grouping – using temporary table, filesort

Mysql – Finding rows for a specified date range

Related Question