Partionting - Allowing MySQL to handle it

[Venom Rush]

Hi all

I've been trawling the net trying to find out how MySQL would handle partitioning if I don't specify any values for RANGE. My code is as follows:

PARTITION BY RANGE(vehicle_id) PARTITIONS 10()

The table will most likely end up having roughly 50 000+ records. Can anyone perhaps give me an idea of how MySQL will handle this?

[debasisdas]

the following is a sample of range partition.

CREATE TABLE test_range (
  test_id INT AUTO_INCREMENT PRIMARY KEY,
  test_date DATE
) ENGINE = MYISAM
  PARTITION BY RANGE(test_id) (
    PARTITION p0 VALUES LESS THAN(10000),
    PARTITION p1 VALUES LESS THAN(20000),
    PARTITION p2 VALUES LESS THAN(30000),
    PARTITION p3 VALUES LESS THAN(40000),
    PARTITION p4 VALUES LESS THAN(50000)
);

Please read more about table partition here, here and here.

[smantscheff]

@debasidas: The question was not how to code a partition but about the behaviour in absence of such explicit coding - which I would also like to know.

[Venom Rush]

@debasidas: The question was not how to code a partition but about the behaviour in absence of such explicit coding - which I would also like to know.

That's exactly it. I just want to know what MySQL will do if I don't specify values for partitions.

[Venom Rush]

Ok, seems I found my answer in debasidas' first link. Seems you have to specify values when using RANGE. If one uses HASH then you don't need to specify values. The quote below explains.

CREATE TABLE orders_hash (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_surname VARCHAR(30),
  store_id INT,
  salesperson_id INT,
  order_date DATE,
  note VARCHAR(500)
) ENGINE = MYISAM
  PARTITION BY HASH(id)
  PARTITIONS 4;

MySQL determines which partition stores rows that are inserted into the orders_hash table using the modulus of the id value and the number of partitions (4). Internally, the partitions are numbered 0 to 3; this means that order #10203 is stored in partition #3, because 10203 mod 4 equals 3 (that is, dividing 10203 by 4 leaves a remainder of 3).

Furthermore, if one uses KEY and doesn't specify any columns (ie 'PARTITION BY KEY()') then the PRIMARY KEY of the table is used.

[debasisdas]

Seems you have to specify values when using RANGE. If one uses HASH then you don't need to specify values.

Exactly, that is how partition works.

Things becomes more challenging once you decide to you composite partition.
i.e.-- different types of partition logic on a single table.