How to search multiple data in mysql without losing performance

Asked

Viewed 103 times

2

I have a table called "readings" where my search returns 10 thousand results for the equipmentid = 1

SELECT id, result, date FROM readings WHERE equipamentid  = 1;

for each returned result I need to bring another data with equal smaller date nearest now referring to the equipmentid = 2

SELECT result FROM readings WHERE equipamentid = 2 AND date <= date(data da primeira busca) ORDER BY date DESC LIMIT 1

The code works perfectly, but as the first search returns 10 thousand results, another 10 thousand searches of the next equipment, which makes everything very slow. I have tried also to perform using LEFT JOIN but the runtime is the same, it is unviable.

Is there another way I haven’t been able to visualize yet to solve this problem, and gain performance?

php mysql sql database mysqli

  • already created index to optimize the query? link

2 answers

4

From what I understand of your question, is that with each result of the first SELECT you make a new query by always picking a single record with a date less than or equal to each record returned by the first SELECT. Right?

That is, there will be 10 thousand different consultations.

And that’s where the mistake is!

Because if you need to get all the records with the date equal to or less than the date of the equipment with equipamentid = 1 just take the LONGEST date of this record and filter into the second SELECT, so it will bring all the records at once.

This SELECT keeps:

SELECT id, result, date FROM readings WHERE equipamentid  = 1;

'Cause you need those results.

The second, you change to that:

SELECT result
FROM readings
WHERE equipamentid = 2 
AND data <= 
(
  SELECT data FROM readings WHERE equipamentid = 1 ORDER BY data DESC LIMIT 1
)

So you’ll get the results with result where equipamentid = 2

  • Opa André, almost that, come on... Imagine that the first 3 lines of the equipamentid = 1 are: #1 date(03/03/2020 10:10:00); result(10) #2 date(02/03/2020 10:10:00); result(9) #3 date(01/03/2020 10:10:00); result(8) i now need to bring a result of the equipmentid = 2, less than or equal to the date of the nearest 1st line, ie the first to be found in descending order.

  • I need, in addition to the result of the equipmentid = 2, the result of the equipmentid = 1 and the date of the equipmentid = 1

  • @Gustavopaes but then, you make the first normal consultation with equipamentid = 1 THEN you make the second query I published and there will be all the values you need. Got it?

  • @Gustavopaes made an edition to make it clearer

  • Andrei, in his query he brings only the result of equipment 2, I need a result of 2 for each row of the result of 1, and still the column date of 1, in his query he brings only the data of the equipment 2

  • The way it is, the second query you put behind all the records of the team 2, I need the result 2 with smaller equal date closer to the date of the equipment 1, if I don’t bring the data ready in the query then I will have to compare directly in php. For while euqipament 1 may have 10,000 records equipment 2 may have 20,000 records in the same time frame...

  • To make it easier, I created a table for you to understand the result I need, I believe I am not able to explain it to you, take a look at the columns with comments https://drive.google.com/open?id=1m8xat-R9I02BA_Cn_HcsvaEJPGKoFEN3

  • @Gustavopaes ok, later I will give a analyzed

Show 3 more comments

1

To improve performance I suggest the following:

  1. Indexes the fields used in the WHERE enclosure
  2. It is preferable to take everything using junction or subconsulta and treat programmatically, because the way you are doing it greatly harms the application:

The code works perfectly, but as the first search returns 10 thousand results, another 10 thousand searches is carried out

Open 10.001 connections even if only for query consumes much more resource than 1 single query.

In short try like this:

SELECT 
  p.id,
  p.result,
  p.date,
  (SELECT s.result FROM readings s WHERE s.equipamentid = 2 AND s.date <= p.date ORDER BY s.date DESC LIMIT 1) as result2
FROM readings p
WHERE equipamentid = 1

Browser other questions tagged php mysql sql database mysqli

You are not signed in. Login or sign up in order to post.