MySQL Forums
Forum List  »  Performance

Re: MySQL 5.1.49 - Optimal settings for memory usage
Posted by: Rick James
Date: January 19, 2012 12:17AM

To use an index, or not to use an index, that is the question.

If less than 10% of the table needs to be looked at, a relevant index will be used.
If more than 30% of the table needs to be looked at, the seemingly relevant index will not be used.
Why? To use an index means flipping back and forth between the index and the data. (Note: the index and the data are usually kept in separate places, and sorted in different ways.)

As long as Geraete_idGeraete is always 2 (> 30%), indexing that field is mostly useless.
Scanning a whole year is scanning most of the data, so it does not bother with the index.

This may involve a different issue:
> The query 2011-01-01 00:00:00 to 2012-01-01 00:00:00 takes 30sec
> The query 2011-01-06 09:30:00 to 2012-01-06 09:30:00 takes 8sec
Are there a similar number of rows in each query?
Did you run the first query first?
Was the system 'cold' when you ran the first query?
Is there so much data that it cannot all be cached in RAM?

Typically if you run any 'big' query twice in a row, the second run will be ten times as fast, simply because all the stuff it needs has been pulled from disk and "cached".

Please provide SHOW TABLE STATUS LIKE 'Wind'; (Without it, I am guessing at some of what I am saying.)

As for the differences in the EXPLAINs, first realize that the query optimizer has heuristics, and approximations. InnoDB, as you may have noticed, never quite knows how many rows are in a table, and what to put in EXPLAIN's "Rows colunn. Hence, two EXPLAINs may go in different directions because of the phase of the moon. Probably (and hopefully) the two choices are about equally 'good'.

key_len = 4 refers to the storage size of
> `Geraete_idGeraete` int

key_len = 12 refers to the storage size of the index being used, which includes
> `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', -- 8 bytes
> `Geraete_idGeraete` int(10) unsigned NOT NULL DEFAULT '0', -- 4 bytes

"ref=const" refers to
> Geraete_idGeraete=2
But then it estimated that it would need 9M rows.

What is the range of `time` in the table?
SELECT min(`time`), max(`time`) FROM Wind;

Have you tried a PRIMARY KEY or INDEX ordered this way?
(Geraete_idGeraete, time)
If that were the PK, then the queries you have shown would hit the minimum number of things -- namely a "range" scan over all the rows with Geraete_idGeraete=2 in the given time range, and no other rows. Also it would not need to flip back and forth between the index and the data.

Still, that is a lot of rows, so it will take a "long" time.

Options: ReplyQuote

Written By
Re: MySQL 5.1.49 - Optimal settings for memory usage
January 19, 2012 12:17AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.