I'm trying to reduce this script execution time. It queries a database with about 2 million records about 1000 times on a loop:

foreach ($ids as $id){
    $stmt=$dbh->query("SELECT SQL_CACHE * FROM `ids` 
                WHERE $id BETWEEN `id_start` AND `id_end`");
    $rows[] = $stmt->fetch();
}

It takes forever on a 4 core 8 GB machine (about 800 seconds!). Id groups do not overlap, ids tend to be from just a few different groups in each execution and I have indexed both (id_start,id_end) and (id_end).

Caching improves very much the situation (running the same 1000 values more than once is just a few seconds), but I would like to know what can I do to speed up non cached queries.

Example output of EXPLAIN:

"id"    "select_type"   "table"     "type"  "possible_keys"     "key"               "key_len"   "ref"   "rows"  "Extra"
"1"     "SIMPLE"        "ids"       "range" "id_start,id_end"   "id_start,id_end"   "5"          ""     "52508" "Using index condition"

EDIT: Instead of "Using index condition" I get "Using where" sometimes (Not sure but I think from id values higher than 840771583) Why?

EDIT 2: Full create code:

CREATE TABLE `ids` (
    `id_start` INT(10) UNSIGNED NULL DEFAULT NULL,
    `id_end` INT(10) UNSIGNED NULL DEFAULT NULL,
    `iso-639-1` VARCHAR(2) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `country_name` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `region_name` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `city_name` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `area_code` VARCHAR(16) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `timezone` VARCHAR(6) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    UNIQUE INDEX `id_startid_end` (`id_start`, `id_end`),
    INDEX `id_end` (`id_end`),
    INDEX `country_name` (`country_name`),
    INDEX `region_name` (`region_name`),
    INDEX `city_name` (`city_name`),
    INDEX `area_code` (`area_code`),
    INDEX `iso-639-1` (`iso-639-1`),
    INDEX `timezone` (`timezone`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

Since the intervals do not overlap, try this rewriting of the query:

SELECT * 
FROM ids
WHERE id_start =
      ( SELECT MAX(id_start) 
        FROM ids
        WHERE id_start <= $id
      )
  AND $id <= id_end ;

It queries a database with about 2 million records about 1000 times on a loop:
                                                         ^^^^^^^^^^^^^^^^^^^^   

HERE

is your problem.

It should be a single query for sure.

Also consider tweaking mysql daemon, to make sure if key buffer size is enough.

this question has absolutely nothing to do with PDO, btw. When dealing with a problem you have to narrow it down as much as possible, taking out ALL the unnecessary parts. Speaking of the query, you have to take it into console and play there.