"Huge database mode" - Don't sort / filter unindexed columns

Battlestar posted 2 years ago in Feature discussion

I am using HeidiSQL on a database server which currently holds about 1 TB of data with many really big tables (hundrets of gigabytes). It happens every now and then that I accidently click on a column header in the data tab which executes a query which is ordered by an unindexed column. As those queries are running for hours I need to open a second instance of HeidiSQL and terminate that query. This is very annoying.

My idea is that HeidiSQL supports a feature that will prevent executing queries which are run by convenience features in that "Data"-Tab (Sort, Filter, "Show all"). At least there should be a warning message.

ansgar posted 2 years ago

Ok, the "Show all" button is already limited to a default setting of 100000 rows (customizable in Preferences > Text formatting. This is a global setting, not specific to a session (like all settings in the preferences dialog).

For the sort feature, I can only guess I could introduce a setting which disallows setting it on non-indexed columns. But would that really help? And wouldn't you want to explicitly override that setting again for certain columns in smaller tables?

Battlestar posted 2 years ago

Sorry for the late answer. As you might have noticed, I've posted a similar issue about the "huge database" problem: https://www.heidisql.com/forum.php?t=24470

I think a possible solution might be to just show a warning message (with "Don't ask me again" option) whenever I click a column header. Trying to detect wether a column can be efficient to be sorted is very difficult (think of combined indexes, computed columns, etc.). Maybe you can run an "EXPLAIN" Query before and parse the result. DBeaver, JetBrains' IntelliJ/DataGrip and many other SQL clients I know make it hard to execute queries that are potentially slow.

Please login to leave a reply, or register at first.