0

good day everyone,

i'm having dificulties optimizing my tables

i have the following info:

id|Equipment|Date|Info1|info2|info3|info4|(9 columns total)

every day i get a report for that day that i insert into the DB for almost every equipment with the actual date but they might not be inserted everyday or in the correct date order so the highest ID will not always be the most recent.

in 3 months i have almost 1 million rows so my querys to check 2000 equipments latest info are taking about 1 minute now (using an i5 laptop to host the DB for development), so in a few more months i might well go for lunch while i wait...

to reduce the load on the queries i was thinking about using a table for the latest infos for each equipment and another to keep the history if i need to check.

i was thinking about using a trigger when i add info to the main table that would empty the latest records table and add the latest record for each equipment again(from my research i don't know if its possible)

right now the query i'm using is:

 VIEW `metersbycontract` AS
  SELECT 
   `equipments`.`Serial` AS `serial`,
   `equipments`.`modelname` AS `model`,
   `contracts`.`number` AS `contract`,
   `contracts`.`customername` AS `customer`,
   `meters`.`Date` AS `date`,
   `meters`.`BlackPrints` AS `Black`,
   `meters`.`ColourPrints` AS `Colour`,
   `meters`.`TotalPrints` AS `Total`,
   `meters`.`PageCount` AS `Pages`
  FROM
   ((`equipments`
  LEFT JOIN `contracts` ON ((`equipments`.`Serial` = `contracts`.`serial`)))
   LEFT JOIN `meters` ON ((`contracts`.`serial` = `meters`.`Serial`)))
  ORDER BY `meters`.`Date` DESC

this gives me all records by date and i have to group them by serial to only keep the latest(i wanted to avoid getting all the info if possible and accepting any ideas)

i attached an image of my existing tables(still in construction) i use an "equipments" table on the view but for now it can be ignored since its not adding any new info to the final data

Community
  • 1
  • 1
Tiago Sá
  • 1
  • What's your question? You've said what you want to do, but haven't said what problem you're having when you try to do it. – Barmar Apr 29 '17 at 18:05
  • right now the query is getting heavy, i don't know if its possible to use a trigger to update a table with only the latest values, that is my problem. – Tiago Sá Apr 30 '17 at 11:14
  • If you can write a query to get the latest records, you can put it into an insert query that you put in the trigger. – Barmar Apr 30 '17 at 21:15
  • well... i tought i got away with sorting by date in one view and grouping afterwords with another view while testing but when i tried to do both using subqueries i realised that i was getting random rows, the group by does not work like intended... i guess that i need to forget the trigger and get another way to select the latest values outside mysql and re-insert it on the latest records table... – Tiago Sá May 01 '17 at 12:12
  • See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the latest record for each equipment. – Barmar May 01 '17 at 15:17
  • i can't believe that i've been trying for about 4 days to get a fast and correct list of the latest info for each equipment and i didn't stumble in that post with such an easy and fast solution... thank you so much Barmar, – Tiago Sá May 01 '17 at 15:54

0 Answers0