vishesh92 commented on PR #8737: URL: https://github.com/apache/cloudstack/pull/8737#issuecomment-1991855141
> @vishesh92 _vm_stats_ table data, timestamp column cardinality in both cases are the same? please check/confirm results with the filter matching rows, and same delete count. Here are the stats. ```sql MySQL root@(none):test> DELETE FROM vm_stats_without_index WHERE timestamp < '1972-05-01'; -- without index Query OK, 9213 rows affected Time: 0.414s MySQL root@(none):test> DELETE FROM vm_stats_with_index WHERE timestamp < '1972-05-01'; -- with index Query OK, 9213 rows affected Time: 0.100s ``` ```sql MySQL root@(none):test> DELETE FROM vm_stats_without_index WHERE timestamp < '1980-01-01'; You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 84840 rows affected Time: 0.438s MySQL root@(none):test> DELETE FROM vm_stats_with_index WHERE timestamp < '1980-01-01'; You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 84840 rows affected Time: 0.375s ``` In case of a delete operation with some filter, before deleting the records database needs to fetch the records from the database. If the index is not present, this fetching of records from the database becomes slow and leads to scanning of all rows in the table resulting in high I/O on disk. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@cloudstack.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
