Extremely slow filter performance in 7.1.13

[nate]

I'm using the filtering feature in the data grid. When filtering on an indexed column, it is extremely slow.

For example, I have a table with millions of rows. I tried to filter on the `id` column. In the MySQL logs, I see this query issued by dbForge:

Code: Select all

SELECT * FROM (SELECT * FROM my_dbname.users) subquery WHERE id = 12321677 limit 0,1000

That's a bad query -- it does a full table scan.

I remember the filter being faster in previous versions. Did something change?

[alexa]

We will fix this issue in one of the next product builds and will notify you once it's available for downloading.

[nate]

Is this fix available yet?

[nate]

Unfortunately this bug is still there in 7.1.20 and it's a big issue.

I opened one of our large tables, clicked the "filter" symbol at the top of the "id" column and looked for an id. dbForge Studio issued the following query, which I had to kill at the server:

Code: Select all

SELECT * FROM (SELECT * FROM my_db.a_large_table
) subquery WHERE id = 555555555 limit 0,1000

The table has more than 1 billion rows, and EXPLAIN shows that it does a full table scan. Of course it should have issued an efficient query like `SELECT * FROM my_db.a_large_table WHERE id=555555555`.

What's worse is that dbForge Studio issued a similar query when I simply clicked the id column header, to sort by that column:

Code: Select all

SELECT * FROM (SELECT * FROM my_db.a_large_table
) subquery ORDER BY id limit 0,1000

Not only is this slow, but it loads down our server with these giant queries. For now we've stopped using dbForge Studio and are using (a competing product). I personally prefer dbForge Studio and hope this gets resolved.

[alexa]

Unfortunately, it's still to be fixed.

[nate]

I just tried version 7.2.34 and this seems to be fixed!! Is that correct?

[alexa]

That's right, it's fixed.