Shane Shane - 5 months ago 13
MySQL Question

Optimizing many "BETWEEN ? AND ?" where clauses

I'm trying to grab vehicles that have mileage within a certain

$range
and
$interval
. Currently, there are a huge number of
between ? and ?
clauses which are slowing the query down immensely. Is there a better way of accomplishing this? I'm thinking a modulo operation might work, although the
$range
makes it more difficult.

$interval = 15000;
$range = 5000;
$max = 3000000;

for ($i = $interval; $i < $max; $i += $interval)
{
$between = [
$i - $range,
$i + $range,
];

// generates "WHERE mileage BETWEEN ? AND ? OR mileage BETWEEN ? AND ? ..."
$query->orWhereBetween('mileage', $between);
}

Answer
select .. where (mileage>=15000) and ((mileage-10000) mod 15000)<=10000