Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Do
-
None
-
None
Description
This article contains ready-to-use copy/paste code for working with the querystats table, but these examples contain errors which result in either non-working queries or plain wrong results.
https://mariadb.com/kb/en/library/analyzing-queries-in-columnstore/
1. Whenever "SELECT ... rows ... FROM querystats" is used, the word "rows" should be put in single quotes as it is a reserved word, but here it is used as column name (which is, IMHO a bad practice, but still). Without these quotes mcsmysql will tell you have a syntax error in query next to word "rows".
2. To calculate the running time of a query, it is suggested to use "endtime-starttime". This is plain wrong, as both columns are DATETIME-based (at least on my 1.2.2, which is latest GA) and the resulting integer may or may not be the correct number of seconds between the start and end of the query (depending on the actual values). A proper way to calculate duration in seconds is to convert both first to UNIX_TIMESTAMP. Here is an example of a query which ran for 28 seconds, but the method from the article will report its runtime as 68 seconds:
{{-------------------------------------------------+
starttime | endtime | ok | wrong |
-------------------------------------------------+
2019-02-22 14:46:56 | 2019-02-22 14:47:24 | 28 | 68 |
-------------------------------------------------+
1 row in set (0.001 sec)}}