CODEWITHSUNDEEP
mysqloptimization
nashuald
nashuald

Reputation: 825

MySQL: optimize query for scoring calculation

I have a data table that I use to do some calculations. The 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 I have other table that contains a set of ranges that are used to classify the calculations results. The range tables looks like:

 +----------+--------------+---------+
 | id_level | start |  end |  status |
 +----------+--------------+---------+
 |        1 |   0   |   75 |  Danger |
 |        2 |  76   |   90 |  Alert  |
 |        3 |  91   |  100 |  Good   |
 +----------+--------------+---------+

I need to do a query that add the corresponding 'status' column to each value when do calculations. Currently, I can do that adding the following field to calculation query:

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

It works ok. But when I have a lot of rows (more than 200K), calculation query become slow.

My question is: there is some way to find that 'status' value without do that subquery?

Some one have worked on something similar before?

Thanks

Upvotes: 2

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

Yes, you are looking for a subquery and join:

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

Explicit joins often optimize better than nested select. In this case, though, the ranges table seems pretty small, so this may not be the performance issue.

Upvotes: 2

Related Questions