PHP Fastest query in huge DB mysql

Question

I'm creating a table to store millions of records, that's it 86400seconds a day x 365days x 10years = 315,360,000 rows of records with only 3 columns, with datetime, decimal, and smallint (only 3 fields) datetime as index.

I'm thinking of converting the datetime into INT unsigned (PHP time()) to reduce the storage. With the datetime, decimal and smallint, I'm having 2.5GB for 1 table. I've not tried to replace the DATETIME with INT.

The insertion to this table is 1 time job, and I'll have a lots of SELECT statement for analytical purpose, thus I'm changing the InnoDB to MyISAM.

Any thoughts or suggestion?

Very simple select, and WHERE clause were only pointing to the first field. — Fire, Aug 20 '16 at 11:17
how about myisam or innodb in this case? shall i go for myisam? — Fire, Aug 20 '16 at 12:14
http://stackoverflow.com/questions/20148/myisam-versus-innodb and http://stackoverflow.com/questions/15678406/when-to-use-myisam-and-innodb — juergen d, Aug 20 '16 at 14:06

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Indexes are one of the things used to get a faster search on a table: http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

On the basic level, indexes are responsible for making the engine not iterate over the entire table when searching for something you asked for.

Some of the things stated in the link regarding the use of indexes:

To find the rows matching a WHERE clause quickly.

To eliminate rows from consideration. If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows (the most selective index).

See if that suits your needs

I understand the indexing part, my case here is only single index which is the Datetime or INT. — Fire, Aug 20 '16 at 11:17

PHP Fastest query in huge DB mysql

1 Answers1