Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
I think it's could be usefull to add partitions for the command analyze, I just realize now that it's didn't worked.
MariaDB [pmacontrol]> explain partitions SELECT a.`id_mysql_server`, a.`id_ts_variable`, '' as connection_name,a.`date`, a.`value` as value FROM `ts_value_general_int` PARTITION (p739672,p739679) a INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server AND a.date = b.date INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id AND b.`id_ts_file` = c.`id_ts_file` WHERE id_ts_variable = 1620 AND a.id_mysql_server IN (1,2,3,4,14,15,16,17,18,28,5,6,7,8,9,10,11,12,13,19,20,21,22,24,29); |
+------+-------------+-------+-----------------+-------+---------------------------------------------------------------------+-----------------+---------+------------------------------------------------------+------+--------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-----------------+-------+---------------------------------------------------------------------+-----------------+---------+------------------------------------------------------+------+--------------------------+
|
| 1 | SIMPLE | c | NULL | const | PRIMARY,ts_variable_ibfk_1 | PRIMARY | 4 | const | 1 | | |
| 1 | SIMPLE | b | NULL | range | id_mysql_server_2,id_mysql_server,date,id_ts_file,id_mysql_server_4 | id_mysql_server | 8 | NULL | 25 | Using where; Using index | |
| 1 | SIMPLE | a | p739672,p739679 | ref | id_mysql_server | id_mysql_server | 13 | pmacontrol.b.id_mysql_server,const,pmacontrol.b.date | 2 | | |
+------+-------------+-------+-----------------+-------+---------------------------------------------------------------------+-----------------+---------+------------------------------------------------------+------+--------------------------+
|
3 rows in set (0,001 sec) |
MariaDB [pmacontrol]> analyze SELECT a.`id_mysql_server`, a.`id_ts_variable`, '' as connection_name,a.`date`, a.`value` as value FROM `ts_value_general_int` PARTITION (p739672,p739679) a INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server AND a.date = b.date INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id AND b.`id_ts_file` = c.`id_ts_file` WHERE id_ts_variable = 1620 AND a.id_mysql_server IN (1,2,3,4,14,15,16,17,18,28,5,6,7,8,9,10,11,12,13,19,20,21,22,24,29); |
+------+-------------+-------+-------+---------------------------------------------------------------------+-----------------+---------+------------------------------------------------------+------+--------+----------+------------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------------------------------------------------------------+-----------------+---------+------------------------------------------------------+------+--------+----------+------------+--------------------------+
|
| 1 | SIMPLE | c | const | PRIMARY,ts_variable_ibfk_1 | PRIMARY | 4 | const | 1 | NULL | 100.00 | NULL | | |
| 1 | SIMPLE | b | range | id_mysql_server_2,id_mysql_server,date,id_ts_file,id_mysql_server_4 | id_mysql_server | 8 | NULL | 25 | 25.00 | 100.00 | 100.00 | Using where; Using index | |
| 1 | SIMPLE | a | ref | id_mysql_server | id_mysql_server | 13 | pmacontrol.b.id_mysql_server,const,pmacontrol.b.date | 2 | 0.96 | 0.01 | 100.00 | | |
+------+-------------+-------+-------+---------------------------------------------------------------------+-----------------+---------+------------------------------------------------------+------+--------+----------+------------+--------------------------+
|
3 rows in set (0,017 sec) |
and I think could be nice if it's work too :
MariaDB [pmacontrol]> analyze partitions SELECT a.`id_mysql_server`, a.`id_ts_variable`, '' as connection_name,a.`date`, a.`value` as value FROM `ts_value_general_int` PARTITION (p739672 |
,p739679) a INNER JOIN ts_max_date b ON a.id_mysql_server = b.id_mysql_server AND a.date = b.date INNER JOIN `ts_variable` c ON a.`id_ts_variable` = c.id AND b.`id_ts_file` = c.`id_ts_fi
|
le` WHERE id_ts_variable = 1620 AND a.id_mysql_server IN (1,2,3,4,14,15,16,17,18,28,5,6,7,8,9,10,11,12,13,19,20,21,22,24,29); |
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'partitions SELECT a.`id_mysql_server`, a.`id_ts_variable`, '' as connection_...' at line 1 |