0

I have a simple mysql query that get customer's equipment detail (general info like id, name, job site):

SELECT *
FROM equipments
WHERE CUSTOMER_ID = 87

But each equipment has is own table of variable datas like fuel level, engine hours, latitude, longitude (the equipment ID is the name of the table). So now, I'm making a query that takes all the general info and put it into an array. After that, I create a loop that include a query for each equipment who looks into the right table (table name = equipement ID) and get the last entry.

I tested it with 4 equipments and it takes on average 4 seconds to load the page which is a bit too long. Is there a way to make it shorter and put this inside one query?

Can I join the first table with the last entry of the second table? The second table is changing and named like the equipment id of the first table.

I found this post, but I can't make it work for this application because I can't set the ON statement. https://stackoverflow.com/a/3619209/1895428

Community
  • 1
  • 1
pmrotule
  • 9,065
  • 4
  • 50
  • 58
  • Can you provide sample data? As a general rule, storing table names in columns would be evidence of bad database design. – Gordon Linoff Feb 07 '14 at 18:18
  • This sounds like a problematic database schema to begin with. Why have different table for each equipment? – Mike Brant Feb 07 '14 at 18:30
  • Because the first version was all inside one table and it ended up with really slow performance because the query was looking into 3 billions of rows... It's another query to get last 50 entries of a specific equipment (or last 100, 200, 500) – pmrotule Feb 07 '14 at 18:37

0 Answers0