- If I use "ANALYZE TABLE t1,t2" with two tables, the PERSISTENT FOR clause in the second table t2 also affects the output for the first table t1
- If I use "ANALYZE TABLE t1,t2" with two tables, the PERSISTENT FOR clause in the first table t1 also affects the output for the first table t2
CREATE OR REPLACE TABLE t1 (a INT, KEY(a));
|
CREATE OR REPLACE TABLE t2 (b INT, KEY(b));
|
ANALYZE TABLE t1, t2 PERSISTENT FOR COLUMNS (b) INDEXES (b);
|
+---------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+-----------------------------------------+
|
| test.t1 | analyze | status | Engine-independent statistics collected |
|
| test.t1 | analyze | error | Invalid argument |
|
| test.t2 | analyze | status | Engine-independent statistics collected |
|
| test.t2 | analyze | status | OK |
|
+---------+---------+----------+-----------------------------------------+
|
ANALYZE TABLE t1, t2 PERSISTENT FOR COLUMNS (a) INDEXES (a);
|
+---------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+-----------------------------------------+
|
| test.t1 | analyze | status | Engine-independent statistics collected |
|
| test.t1 | analyze | status | OK |
|
| test.t2 | analyze | status | Engine-independent statistics collected |
|
| test.t2 | analyze | error | Invalid argument |
|
+---------+---------+----------+-----------------------------------------+
|
ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a) INDEXES (a), t2;
|
+---------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+-----------------------------------------+
|
| test.t1 | analyze | status | Engine-independent statistics collected |
|
| test.t1 | analyze | status | OK |
|
| test.t2 | analyze | status | Engine-independent statistics collected |
|
| test.t2 | analyze | error | Invalid argument |
|
+---------+---------+----------+-----------------------------------------+
|
ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (b) INDEXES (b), t2;
|
+---------+---------+----------+-----------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+---------+----------+-----------------------------------------+
|
| test.t1 | analyze | status | Engine-independent statistics collected |
|
| test.t1 | analyze | error | Invalid argument |
|
| test.t2 | analyze | status | Engine-independent statistics collected |
|
| test.t2 | analyze | status | OK |
|
+---------+---------+----------+-----------------------------------------+
|
Another problem is that the documentation at https://mariadb.com/kb/en/analyze-table/ is obviously incorrect:
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [,tbl_name ...]
|
[PERSISTENT FOR
|
{ ALL
|
| COLUMNS ([col_name [,col_name ...]]) INDEXES ([index_name [,index_name ...]])
|
}
|
]
|
It says the PERSISTENT clause is only possible after the table list. However in fact it's accepted after any tbl_name in the list.
|