Re: Partitions and performance

[Rick James]

Step 1. Shrink the data. Smaller -> less I/O -> faster.

Some examples:

> `Patient_Id` BIGINT(10) NOT NULL DEFAULT '0',

BIGINT lets you have more than 4 billion ids. Do you need that much? BIGINT takes 8 bytes. MEDIUMINT UNSIGNED has a range of 0..16M, and takes only 3 bytes.

`YEAR` BIGINT(4) NOT NULL DEFAULT '0',

There is a YEAR datatype; it takes 2 bytes (vs 8).

> INT(255)

The number (255) means nothing (unless you use ZEROFILL). INT takes 4 bytes and (when SIGNED) has a range of -2 billion to +2 billion.

> DOUBLE(18,14)

Largest value is only 9999.99999999999999. Is that what you expected? It takes 9 bytes (I think). Consider DOUBLE or FLOAT.

> KEY `idx_year` (`YEAR`),

It is rarely useful to index fields with low cardinality.

> KEY `idx_year` (`YEAR`),
> KEY `idx_big` (`YEAR`,`Patient_Id`,`crisk`,`drisk`,`zrisk`,`arisk`,`acrisk`)

The second of those indexes handles cases where you might think to use the first.

> KEY `idx_big` (`YEAR`,`Patient_Id`,`crisk`,`drisk`,`zrisk`,`arisk`,`acrisk`)

What is the intent of this index?

I see no PRIMARY KEY or other UNIQUE key. This seems unlikely.

> key_buffer_size 57671680

How much RAM do you have _available_ to MySQL? That number should be about 20%.

> lets say if we select dimension A then we have to group the values to the respective diemension, same applicable for the rest of the dimension.

Please provide some concrete examples.