MySQL database index query a sharing of instances that optimize large data volumes

The table we want to access is a very large table, 40 million records, the ID is the primary key, the index is built on the program_id.
Execute a SQL:
SELECT * from Program_access_log where program_id between 1 and 4000

This SQL is very slow.
We thought we were dealing with too many records, so we added an ID limit, read only 500,000 records at a time.
SELECT * from Program_access_log where ID between 1 and 500000 and program_id between 1 and 4000

But this SQL is still very slow, and the speed is almost no higher than the above one.
MySQL processes 500,000 records of the table, the condition field is also indexed, this statement should be completed in an instant.

Problem Analysis:

This table is approximately 30G in size, database server memory 16G, and cannot be loaded once. That's what's causing the problem.
This SQL has two conditions, id one to 500,000 and program_id one to 4,000, because the program_id range is much smaller, and MySQL chooses it as the primary index.
This process is very fast by locating all the IDs of all program_id in the range 1 to 4000, using the index file first.
The next step is to find out the table's records through these IDs, because the IDs are discrete, so MySQL's access to the tables is not sequential read.
And this table is very large, can not be loaded into memory at one time, so every access to a record MySQL to re-locate on the disk and the nearby records are loaded into memory, a large number of IO operations caused by the speed of the decline.

Problem Solutions:
1. Partitioning the table on a program_id condition
2. Sub-table processing, the size of each table does not exceed the size of the memory
However, the server uses mysql5.0, does not support partitioning, and the table is a common table and cannot modify the structure of the table without affecting other items.
So we took a third approach:
SELECT * from Program_access_log where ID between 1 and 500000 and program_id between 1 and 15000000

Now the scope of the program_id is much larger than the scope of the ID, the ID is looked up as the primary index, because the ID is the primary key, so the search is 500,000 consecutive records, speed and access to a 500,000 record table basically the same

Summarize:
This is a in the tens of millions of records in the table because of the use of the index caused by the data to find the problem of slow, there is a certain typical and everyone under the exchange!

MySQL database index query a sharing of instances that optimize large data volumes