Optimization of query and database structure

Question

There is a table:

CREATE TABLE IF NOT EXISTS `stats` ( `stats_id` bigint(21) unsigned NOT NULL AUTO_INCREMENT, `stats_server_id` int(10) DEFAULT NULL, `stats_time` int(10) DEFAULT NULL, `stats_players` int(6) DEFAULT NULL, `stats_map` varchar(255) DEFAULT NULL, `stats_server_status` int(1) NOT NULL DEFAULT '0', `stats_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`stats_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=29302216 ; /* пару строк для понимания */ INSERT INTO `stats` (`stats_id`, `stats_server_id`, `stats_time`, `stats_players`, `stats_map`, `stats_server_status`, `stats_timestamp`) VALUES (28117217, 9, 1463702411, 9, '$2000$', 1, '2016-05-20 03:00:13'), (28122217, 9, 1463702530, 8, '$2000$', 1, '2016-05-20 03:02:11'), (28127217, 9, 1463702649, 9, '$2000$', 1, '2016-05-20 03:04:10');

Everything worked fine until the table started to fill up, in the end my query takes a lot of time:

 SELECT * FROM `stats` WHERE `stats_server_id` = '9' AND `stats_time` > 1463702400

(233 total, request took 5.4820 seconds.)

For some reason, I am convinced that the timestamp will work faster, well, I added it and here is the result:

 SELECT * FROM `stats` WHERE `stats_server_id` = '9' AND `stats_timestamp` > '2016-05-19 00:00:00'

(233 total, request took 5.5570 seconds.)

What surprises me is the speed of this query:

 SELECT * FROM `stats`

(29,277,215 total, the request took 0.0005 seconds.)

And what's interesting is that if you choose by stats_server_id it turns out to be pretty not bad, but I need the same sampling by time:

 SELECT * FROM `stats` WHERE `stats_server_id` = '9'

(5,857 total, request took 0.0261 sec.)

For the test, I made more such requests, and I realized that there was a problem even with stats_timestamp even though stats_time

 SELECT * FROM `stats` WHERE `stats_timestamp` > '2016-05-19 00:00:00'

(1,395,000 total, request took 7.9079 seconds.)

 SELECT * FROM `stats` WHERE `stats_time` >1463702400

(1,155,000 total, request took 8.6857 sec.)

I certainly sinned on the condition > but the tests with stats_timestamp = , stats_time = and stats_server_id = showed something else:

 SELECT * FROM `stats` WHERE `stats_timestamp` = '2016-05-20 11:18:11'

(119 total, request took 7.6739 seconds.)

 SELECT * FROM `stats` WHERE `stats_time` =1463702411

(5,000 total, request took 6.2635 seconds.)

 SELECT * FROM `stats` WHERE `stats_server_id` =9

(5,875 total, request took 0.0262 sec.) Why does this happen if the stats_time and stats_server_id field is int(10) DEFAULT NULL ? How can I optimize a query or database structure for quick work?

Naturally, it will slowly work if you need to read and analyze each record.
@Etki but look at the last two queries, there are no indexes.
Most likely there are some optimizations due to low cardinality.
I can’t say why it happens quickly somewhere, but without indices you can hardly make quick samples for a large amount of data.
@wwwplaton If you have a server_id search that fits very quickly, then there is an index on it.
it could be created automatically if you did foregene for example.
To search for your condition, there must be a composite index for both fields (stats_server_id, stats_timestamp).
It may of course help just by stats_timestamp, but depends on the time interval and the number of different server_id.
Must perform and carefully study the explain for these requests
@wwwplaton Well, when the key field is empty, then the index is not used at all, i.e.
In the rows column you can see how many records he intends to view for finding the result.
You have 4628 - quite an acceptable amount, obviously not 29kk.

Optimization of query and database structure

0

More articles: