MySQL innoDB: Long time of query execution

Question

I'm having troubles to run this SQL:

enter image description here

I think it's a index problem but I don't know because I dind't make this database and I'm just a simple programmer.

The problem is, that table has 64260 records, so that query gets crazy when executing, I have to stop mysql and run again because the computer get frozen.

Thanks.

EDIT: table Schema

CREATE TABLE IF NOT EXISTS `value_magnitudes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` float DEFAULT NULL,
  `magnitude_id` int(11) DEFAULT NULL,
  `sdi_belongs_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `reading_date` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1118402 ;

Query

select * from value_magnitudes
where id in 
(
    SELECT min(id)
    FROM value_magnitudes
    WHERE magnitude_id = 234
    and date(reading_date) >= '2013-04-01'
    group by date(reading_date)
)

EDIT2

enter image description here

I need to improve that table, in order to make that query quickly, I don't know how or what to index, because the picture says lot of null and they're not good. — Alejandro L.
– Alejandro L., Commented May 10, 2013 at 11:21

ypercubeᵀᴹ · Accepted Answer · 2013-05-10 11:51:54Z

2

First, add an index on (magnitude_id, reading_date):

ALTER TABLE
  ADD INDEX magnitude_id__reading_date__IX       -- just a name for the index
    (magnitude_id, reading_date) ;

Then try this variation:

SELECT vm.* 
FROM value_magnitudes AS vm
  JOIN 
  ( SELECT MIN(id) AS id
    FROM value_magnitudes
    WHERE magnitude_id = 234
      AND reading_date >= '2013-04-01'         -- changed so index is used
    GROUP BY DATE(reading_date)
  ) AS vi
    ON vi.id = vm.id ;

The GROUP BY DATE(reading_date) will still need to apply the function to all the selected (thorugh the index) rows and that cannot be improved, unless you follow @jurgen's advice and split the column into date and time columns.

edited May 10, 2013 at 11:51

answered May 10, 2013 at 11:42

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ypercubeᵀᴹ Over a year ago

So, was it helpful? How is the execution time now?

Alejandro L. Over a year ago

You edited it but, the first edition was executed in 0,005 secs so it is amazing @ypercube don't know why it is so fast, because I didn't indexed to try if it was the problem, and it run so gooood =)

ypercubeᵀᴹ Over a year ago

My edit did not change the code. I only added comments and the last paragraph. Good to hear that it's running fast now.

juergen d · Accepted Answer · 2013-05-10 11:51:47Z

2

Since you want to get results for every day you need to extract the date from a datetime column with the function date(). That makes indexes useless.

You can split up the reading_date column into reading_date and reading_time. Then you can run the query without a function and indexes will work.

Additionally you can change the query into a join

select * 
from value_magnitudes v
inner join 
(
    SELECT min(id) as id
    FROM value_magnitudes
    WHERE magnitude_id = 234
    and reading_date >= '2013-04-01'
    group by reading_date
) x on x.id = v.id

edited May 10, 2013 at 11:51

answered May 10, 2013 at 11:30

juergen d

205k40 gold badges304 silver badges377 bronze badges

Comments

cmbaxter · Accepted Answer · 2013-05-10 11:34:07Z

1

For starters, I would change your query to:

select * from value_magnitudes where id = (
  select min(id) from value_magnitudes
  where magnitude_id = 234
  and DATE(reading_date) >= '2013-04-01' 
)

You don't need to use the IN clause when the subquery is only going to return one record.

Then, I would make sure you have an index on magnitude_id and reading_date (probably a two field index) as that's what you are querying against in the subquery. Without that index, you are scanning the table each time.

Also if possible change magnitude_id and reading_date to non null. Null values and indexes are not great fits.

edited May 10, 2013 at 11:34

answered May 10, 2013 at 11:24

cmbaxter

35.5k4 gold badges88 silver badges95 bronze badges

3 Comments

juergen d Over a year ago

There is a group by in the query. So no single record from the subquery

Alejandro L. Over a year ago

I want your help adding the index =)

cmbaxter Over a year ago

Check out @ypercube's answer as he supplied the table alter for adding the index

Collectives™ on Stack Overflow

MySQL innoDB: Long time of query execution

Query

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Query

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related