CODEWITHSUNDEEP
phpmysqlsqldatabase-schema
Alejandro L.
Alejandro L.

Reputation: 1076

MySQL Query 127 seconds of execution

I have this SQL Query:

SELECT * FROM magnitudes t1 INNER JOIN value_magnitudes t2 ON t1.id = t2.magnitude_id WHERE t1.floor_id = 42 AND t2.reading_date = (SELECT Max(reading_date) FROM value_magnitudes WHERE t2.magnitude_id = t1.id);

And my DB Schema is:

CREATE TABLE magnitudes (
  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  name VARCHAR(255)  NULL  ,
  sdi_id VARCHAR(255)  NULL  ,
  sdi_id_floor VARCHAR(255)  NULL  ,
  visible TINYINT(1) UNSIGNED  NULL  ,
  history TINYINT(1) UNSIGNED  NULL  ,
  created_at DATETIME  NULL  ,
  updated_at DATETIME  NULL  ,
  floor_id INT(11)  NULL  ,
  unit VARCHAR(255)  NULL  ,
  kind_id INT(11)  NULL  ,
  sample_time INT(11)  NULL  ,
  history_from DATETIME  NULL DEFAULT 2011-02-22 11:18:07   ,
PRIMARY KEY(id));

CREATE TABLE value_magnitudes (
  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  value FLOAT  NULL  ,
  magnitude_id INT(11)  NULL  ,
  sdi_belongs_id VARCHAR(255)  NULL  ,
  reading_date DATETIME  NULL  ,
  created_at DATETIME  NULL  ,
  updated_at DATETIME  NULL    ,
PRIMARY KEY(id));

What I actually want is, get the last value_magnitudes depending on the last updated_at by magnitudes.id on values_magnitudes.magnitude_id

Thanks.

Upvotes: 0

Views: 92

Answers (2)

Mahesh.D
Mahesh.D

Reputation: 1689

SELECT VM.* FROM value_magnitudes VM, magnitudes M WHERE M.id = VM.id ORDER BY updated_at DESC LIMIT 0,1;

Upvotes: 1

S.Visser
S.Visser

Reputation: 4725

SELECT TOP 1 VM.* FROM value_magnitudes VM, magnitudes M  WHERE M.id = VM.id ORDER BY updated_at DESC

Upvotes: 1

Related Questions