Re: A first table partitioning exercise.

[Rick James]

SHOW VARIABLES LIKE '%buffer%'; -- To see the MyISAM index cache
SHOW TABLE STATUS LIKE '%part_tab'; -- To see the table size
How much RAM do you have? -- To see if it might hold all the data.

It could have been that all the data was in RAM.

Recommend running each SELECT twice in a row, and posting the second time.

I would expect Data_length to be about 200MB (for MyISAM), and essentially 0 for Index_length. The author was running in only 1GB of RAM, so the MYD may have spilled to disk, especially after creating one table from the other.

Well, I could not get it either:

mysql> select count(*) from no_part_tab where
    ->  c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (7.42 sec)
1 row in set (6.37 sec)  <-- run again

mysql> select count(*) from    part_tab where
    ->  c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (0.43 sec)
1 row in set (0.45 sec)  <-- run again

| key_buffer_size | 57671680 | -- on a 4GB machine.

(OK, the key_buffer is irrelevant since there are no indexes.)

I did not get 800000 rows since the first and last days of the year are excluded.