CODEWITHSUNDEEP
mysql
absentx
absentx

Reputation: 1417

How do I speed up or Improve this query?

I have a table with the following structure:


id    widget_id    value    date_recorded
-----------------------------------------------
1         1          10     2019-10-12 12:00:15
2         2          15     2019-10-12 12:00:15
3         3          20     2019-10-12 12:00:15
4         4          50     2019-10-12 12:00:15 
5         1          12     2019-10-15 00:05:15
6         2          19     2019-10-15 00:05:15
7         3          25     2019-10-15 00:05:15
8         4          75     2019-10-15 00:05:15

This table has roughly 500,000 records as we need to keep historic data of the widget values. There are about three hundred different unique widget id's, and an equal amount of records for each id, so figure just shy of 1700 records per id.

Most of the time we simply need to grab the most up to date value for a given Id or for all ids, but I am finding that the query takes longer than expected, especially when looping through all needed ids.

I have tried the following two queries:

This first one takes between three hundred and five hundred MS. Over three hundred records that adds up.

SELECT 
  widget_id,
  value,
  date_recorded
  FROM widget_values
  WHERE  widget_id = 1
  ORDER BY date_recorded DESC
  LIMIT 1

This one takes 500 ms or longer

SELECT 
  widget_id,
  value,
  date_recorded
  FROM widget_values
  WHERE widget_id = 1
  AND date_recorded = (
      SELECT 
      MAX(date_recorded)
      FROM widget_values
      WHERE widget_id = 1
      )

I am wondering the following:

  1. Is there any way to perform these operations faster or perhaps to get the most recent values for all unique id's in one query?
  2. Being that this data set will double in size every few months, is a better strategy to store the most recent values for pertinent id's in a separate table so it is a less intense operation to grab them? So I would have one table storing historic values and another that always holds just the 300 most recent values. This feels like some kind of design violation but also seems like a fair solution to a growing problem.

| widget_values | CREATE TABLE `widget_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `widget_id` int(11) DEFAULT NULL,
  `value` int(11) NOT NULL,
  `date_recorded` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `widget_id` (`widget_id`),
  KEY `value` (`value`),
  KEY `date_recorded` (`date_recorded`),
  CONSTRAINT `widget_amounts_one` FOREIGN KEY (`widget_id`) REFERENCES `widget_codes` (`widget_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB

Upvotes: 0

Views: 50

Answers (3)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Apart from having index on your table, You may try below query to improve performance -

SELECT 
  widget_id,
  value,
  date_recorded
FROM widget_values
WHERE widget_id = 1
ORDER BY date_recorded DESC
LIMIT 1;

Upvotes: 0

ta_shuo
ta_shuo

Reputation: 101

What's the meaning of column date_recorded? If it's just the data's create time, you can use the column id to sort:


SELECT 
  widget_id,
  value,
  date_recorded
  FROM widget_values
  WHERE  widget_id = 1
  ORDER BY id DESC
  LIMIT 1

This will hit the primary key index.

Upvotes: 1

EchoMike444
EchoMike444

Reputation: 1692

A multi-column index on ( widget_id , date_recorded ) will help you .

The main drawback of having to many indexes is you have a penalty for each insert,update,delete .

When you want to optimize access to a table , you must verify each different query against the table and see if you have the right index .

A possible optimization is to remove the column id , and turn widget_id , date_recorded to a primary key . So you will have only one index . This optimization can not be pertinent , if others tables need reference a single row , because the you will use more bytes to store a reference widget_id , date_recorded.

Upvotes: 0

Related Questions