optimization - MySQL: optimize query for scoring calculation -

i have data table use calculations. resulting data set after calculations looks like:

+------------+-----------+------+----------+ | id_process | id_region | type | result   | +------------+-----------+------+----------+ |          1 |         4 |    1 |  65.2174 | |          1 |         5 |    1 |  78.7419 | |          1 |         6 |    1 |  95.2308 | |          1 |         4 |    1 |  25.0000 | |          1 |         7 |    1 | 100.0000 | +------------+-----------+------+----------+ 

by other hand have other table contains set of ranges used classify calculations results. range tables looks like:

 +----------+--------------+---------+  | id_level | start |  end |  status |  +----------+--------------+---------+  |        1 |   0   |   75 |  danger |  |        2 |  76   |   90 |  alert  |  |        3 |  91   |  100 |    |  +----------+--------------+---------+ 

i need query add corresponding 'status' column each value when calculations. currently, can adding following field calculation query:

select    ...,    ...,    [math formula] result, (select status     ranges r    result between r.start , r.end) status ... ... 

it works ok. when have lot of rows (more 200k), calculation query become slow.

my question is: there way find 'status' value without subquery?

some 1 have worked on similar before?

thanks

yes, looking subquery , join:

select s.*, r.status (select s.*       <your query here>      ) s left outer join      ranges r      on s.result between r.start , r.end 

explicit joins optimize better nested select. in case, though, ranges table seems pretty small, may not performance issue.