Re: Query execution plan
Thank you for yor reply Rick. I have answered questions. Also note my follow up post that shows a query that is not subject to such wild execution time differences. Unfortunately, for most channels it is a little slower; but for those misbehaving few, it is much faster.
>> PRIMARY KEY (`time`)
>Risky. You could get two records with the same time. (But I don't know what kind >of clock has the resolution you seem to have.)
No problem here; times will be unique within each data channel.
>> reason for not having one big table is speed of recalling data
>Have you demonstrated that it makes any difference?
Yes. Significant design benchmarking went into the system 6 years ago. Sign the typical fetch is thousands of values, being able to grab them in contiguous disk blocks has significant speed advantage.
>Do this:
>EXPLAIN PARTITIONS select * from table_288 where time <= 5706114878826086400 order by time desc limit 1 ;
>Do it for both a "range" query and an "index" query.
mysql> explain partitions select * from table_5593 where time <= 5706114878826086400 order by time desc limit 1;
+----+-------------+------------+------------------------------------------------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------------------------------------------------------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | table_5593 | p130,p131,p132,p133,p134,p135,p136,p137,p138,p139,CatchAll | index | PRIMARY | PRIMARY | 8 | NULL | 1 | Using where |
+----+-------------+------------+------------------------------------------------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain partitions select * from table_5691 where time <= 5706114878826086400 order by time desc limit 1;
+----+-------------+------------+------------------------------------------------------------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------------------------------------------------------------+-------+---------------+---------+---------+------+----------+-------------+
| 1 | SIMPLE | table_5691 | p130,p131,p132,p133,p134,p135,p136,p137,p138,p139,CatchAll | range | PRIMARY | PRIMARY | 8 | NULL | 46238076 | Using where |
+----+-------------+------------+------------------------------------------------------------+-------+---------------+---------+---------+------+----------+-------------+
1 row in set (0.00 sec)
>> This query runs very fast on the majority of tables, but very slow on a few.
>Provide more details, so we can discuss what is different.
The archiving system consists of several dedicated host managing data for over 70,000 data channels, one table per channel. Only a couple of hundred are slow to fetch with the single value fetch, although some of the 'fast' ones also use the 'index' strategy, but are not a problem because they have so little data. The interesting thing is consistency. I get nearly identical results each time I run my analysis. Slow channels stay slow and likewise for fast ones. Doing a OPTIMIZE table makes no difference.
>> 1 CEV0L05RT.MIN 93 range
>Are you saying that you have a 93-row table partitioned?
Yes. All 70,000+ channel tables are treated exactly the same. I actually discussed this design with you some years ago. The reason for table partitioning is speed of data purge. The system needs to clear disk space whenever it is running out. Tradtional ways would be to delete rows older than a date and OPTIMIZE table to recoup the disk blocks. This is way to slow. Purging old history now is dropping entire partitions, which is very fast.
>> 4,242 ILM1H03B 111,476,494 index
>When it decides to be "range" is the time (5706114878826086400, or whatever) in the table? I am wondering if this leads to the diff.
>Is the query just like the SELECT, above?
I don't believe the time in the query is ever exactly in the table. All queries are identical except for table name. Of course timestamp will also change in real conditions, but not the test.