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?

Re: Partionting - Allowing MySQL to handle it 80 80

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.

Re: Partionting - Allowing MySQL to handle it 80 80

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

Re: Partionting - Allowing MySQL to handle it 80 80

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

Re: Partionting - Allowing MySQL to handle it 80 80

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.

Re: Partionting - Allowing MySQL to handle it 80 80

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.