Index is slowing down the selecting actions

Description the problem:

Table size 30G, Ram size 16G

mysql> select * from program_access_log where program_id between 1 and 4000;

very slow

Try to select the top 500,000 records:

mysql> select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000;

still very slow

Analysis:

MySQL cann't put this 30G table into Ram, it will read them from disk:

mysql> select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000;

MySQL will select the program_id between 1 and 4000 out coz it's smaller then try to find id between 1 and 500000, however the id column didn't store as sorted, so MySQL will read them from disk.

Solutions:

1. Partition: split program_id into different partitions

2. Split tables: split table into smaller tables

3. select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000;