CODEWITHSUNDEEP
mysqloptimizationquery-optimization
lucasrod
lucasrod

Reputation: 93

How to make faster queries on my mysql table?

I have the following table I have the following table

As you can see It has 1868155 rows. I am attempting to make a realtime graph, but It is impossible since almost any query lasts 1 or 2 seconds.

For example, this query

SELECT sensor.nombre, temperatura.temperatura
FROM sensor, temperatura
WHERE sensor.id = temperatura.idsensor
ORDER BY temperatura.fecha DESC, idsensor ASC
LIMIT 4

Is supposed to show this enter image description here

Ive tried everything, using indexes(perhaps not correctly), using only the fields i need instead of *, etc. but the results are the same!

These are the indexes of the table. Indexes of the table

Explain of the query Explain of the query

EDITED EXPLAIN QUERY 2 This is the explain of the query after implementing

ALTER TABLE temperatura
ADD INDEX `sensor_temp` (`idsensor`,`fecha`,`temperatura`)

And using inner join syntax for the query

SELECT s.nombre, t.temperatura
FROM sensor s
INNER JOIN temperatura t
ON s.id = t.idsensor
ORDER BY t.fecha DESC, t.idsensor ASC
LIMIT 4

This is my whole sensor table Sensor table

Upvotes: 0

Views: 3383

Answers (2)

Willem Renzema
Willem Renzema

Reputation: 5187

Try the following:

ALTER TABLE temperatura
ADD INDEX `sensor_temp` (`idsensor`,`fecha`,`temperatura`)

I also recommend using modern join syntax:

SELECT s.nombre, t.temperatura
FROM sensor s
INNER JOIN temperatura t
ON s.id = t.idsensor
ORDER BY t.fecha DESC, t.idsensor ASC
LIMIT 4

Report the EXPLAIN again after making the above changes, if performance is still not good enough.

Attempt #2

After looking closely at what it appears you are trying to do, I believe this next query may be more effective:

SELECT
s.nombre, t.temperatura
FROM temperatura t
LEFT OUTER JOIN temperatura later_t
ON later_t.idsensor = t.idsensor
AND later_t.fecha > t.fecha
INNER JOIN sensor s
ON s.id = t.idsensor
WHERE later_t.idsensor IS NULL
ORDER BY t.idsensor ASC

You can also try:

SELECT
s.nombre, t.temperatura
FROM temperatura t
INNER JOIN (
    SELECT
    t.idsensor,
    MAX(t.fecha) AS fecha
    FROM temperatura t
    GROUP BY t.idsensor
) max_fecha
ON max_fecha.idsensor = t.idsensor
AND max_fecha.fecha > t.fecha
INNER JOIN sensor s
ON s.id = t.idsensor
ORDER BY t.idsensor ASC

In my experience, if you are trying to find the most recent record, one of the two queries above will work. Which works best depends on various factors, so try them both.

Let me know how those perform, and if they still get you the data you want. Also, any query you run, run at least 3 times, and report all 3 times. That will help get an accurate measure of how fast a given query is, since various external factors can affect the speed of a query.

Upvotes: 2

Rick James
Rick James

Reputation: 142268

It is not possible to optimize a mixture of ASC and DESC, as in

ORDER BY t.fecha DESC, t.idsensor ASC

You tried a covering index:

INDEX `sensor_temp` (`idsensor`,`fecha`,`temperatura`)

However, this covering index may be better:

INDEX `sensor_temp` (`fecha`,`idsensor`,`temperatura`)

Then, if you are willing to get the sensors in a different order, use

ORDER BY t.fecha DESC, t.idsensor DESC

This will give you up to 4 sensors for the last fecha:

sensor: PRIMARY KEY(id)
tempuratura: INDEX(fecha, idsensor, tempuratura)

SELECT  
      ( SELECT  nombre FROM  sensor WHERE  id = t.idsensor ) AS nombre,
      t.temperatura
    FROM  
      ( SELECT  MAX(fecha) AS max_fecha FROM  tempuratura  ) AS z
    JOIN  temperatura AS t  ON t.fecha = z.max_fecha
    ORDER BY  t.idsensor ASC
    LIMIT  4;

Upvotes: 1

Related Questions