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 1st query below, I get pruning just fine, only one partition gets queried.
When I run the 2nd query, I'd expect just a few partitions to be queried, but instead ALL are.

  1. select count(*) from emp where gen_year=1984 and month=8;
  2. select count(*) from emp where gen_year=1984 and month>8;
  3. 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.

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).

How does the database know that gen_month=13 is not a valid value and that it shouldn't search in the partition for that value?

How does the database know that gen_month=77+8 is not a valid value?

Logically, gen_months > 8 implies "search all partitions".

Try gen_months between 9 and 12 to see if that uses the correct number of partitions.

  • Those work with PARTITION BY RANGE. BY HASH does not optimize any flavor of 'range' on the partition-key. – Rick James Jun 4 at 20:29

Yes, I forgot to mention that I tried the "between 8 and 12" trick and got, again, all 77 partitions. So, the "=" operator works fine, but the ">" and the "between" fail to prune.

  • Edit your own question and put in any extra information there! Comments are for pointing people to clarifications (using @ + handle - for me @V)érace and requests for extra information from potential responders. Please delete this and put the information in your question. – Vérace May 31 at 18:01
  • @Vérace - (I added it in case George does not have the reputation to edit.) – Rick James Jun 4 at 20:27
  • He always has enough reputation to edit his own question - and (just for completeness), he (or any user of whatever rep) can always propose edits - it's just that they are subject to approval! – Vérace Jun 5 at 8:27

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.