Re: 11 millions records : more than 30 seconds to retrieve simple request

[Funkiki]

Thank you again for your answer (for the innodb_file_per_table especially)

So, hereafter my last performances tests both on INNODB and MYISAM to compare them.

First, here is the final table schema :
CREATE TABLE my_table` (
`variable` varchar(60) NOT NULL,
`log_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`milli` smallint(3) unsigned NOT NULL DEFAULT '0',
`value` double NOT NULL DEFAULT '0',
PRIMARY KEY (`variable`,`log_time`,`milli`,`value`) USING BTREE,
KEY `logtime` (`log_time`,`milli`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

And the results of the tests now :

* generating a table of 110 millions records with 250 records per SQL INSERT statement :
- MYSAM : 3 hours
- INNODB : 3 hours 25 minutes

(for information, it requires more than 10 hours with myISAM to generate those 110 millions records when inserting data, record per record)

* after generation, execute SELECT log_time,milli,value FROM my_table WHERE variable='myvariable' ORDER BY log_time, milli :
- INNODB : less than 1 second
- MYISAM : 20 secondes ????

* OPTIMIZE the myISAM Table :
- it takes more than 5 hours to execute OPTIMIZE (more than for generate the table) !!!

* execute again the same SELECT command :
- MYISAM : less than 1 second
Now, it's good ! But I don't why !

So, because the MYISAM needs to be OPTIMIZE to be useable, because generation time difference between MYISAM and INNODB is not significant, because INNODB is more reliable, I should decide to work with INNODB.

Now, my problem is to manage the files. I've got one 25 Gb table per month. I would like to backup them, or to restore them, to delete them, to reintegrate them in the database. With MYISAM, all of those operations are so easy ! Compress the MYD and MYI files and copy them to an external hard disk or to the network. Delete the files. Copy them back in the database directory. And MySQL is able to handle these transactions with no special difficulty.

But, unfortunately, this is not possible with INNODB !
I've tried to copy a .IBD file to another directory, then drop the table with The MySQL query brower tools. No problem until now ! But, then, I copy back the files in the database directory at the same location. MySQL Query Brower displays it in its tables list. But when I run any SQL statement on the table, the mysqld daemon completely crashes or says that the table doesn't exist.
It is even not possible to simply rename a database with INNODB, knowing that, with MyISAM, it is so obvious and immediate.

This is my dilemna now :
- choose MyISAM for its usability of its file system, but I need to optimize each table after generation in order to have good SELECT performance
- or choose INNODB for the reasons mentioned above.

---------------------------------------------------------------------------

Other point already mentioned : including the 'value' field in the PRIMARY key is mandatory even if there is absolutely no other reason to include it in the key!!!
but else :
SELECT variable,log_time,milli,value FROM my_table WHERE log_time>='begindate' AND log_time <'enddate' ORDER BY log_time, milli is catastrophic.
With no 'value' field in the primary key: EXPLAIN said 'using where'
With 'value' field in the primary key: EXPLAIN said 'using where,using index' ???
And this is true for both MYISAM and INNODB