Disk I/O error with GROUP BY clause

[biospank]

hey @siara-cc thank you for working on this library.
I've just started to using it and I'm stuck on a query that use GROUP BY clause.

13:39:44.463 -> Opened database successfully 13:39:44.463 -> CREATE TABLE IF NOT EXISTS bins (id INTEGER PRIMARY KEY, type text NOT NULL, peak INTEGER NOT NULL, bin INTEGER NOT NULL) 13:39:44.463 -> Operation done successfully 13:39:44.463 -> Time taken:11554 13:39:44.463 -> SELECT COUNT(*) records FROM bins 13:39:44.463 -> Callback function called: records = 646 13:39:44.497 -> 13:39:44.497 -> Operation done successfully 13:39:44.497 -> Time taken:14563 13:39:44.497 -> SELECT type, peak, bin, COUNT(bin) AS bin_count FROM bins GROUP BY bin ORDER BY bin_count ASC 13:39:44.497 -> SQL error: disk I/O error 13:39:44.497 -> Time taken:9273 13:39:44.497 -> ERROR on sql select: disk I/O error

I get this error every time I use GROUP BY clause even on a simle query. The other queries work fine so far.
The bins table has around 600 records as you can see from query log.
I'm using the last version of the library and SPIFFS as FS on a ESP-WROOM-32 module.

Any hints would be really appreciated.

Cheers.

[biospank]

Switching to littlefs FS resolved my issue

[siara-cc]

Hi @biospank
I think it may work on SPIFFS if you create an index on bin column, or make it the primary key if it is appropriate.
Also, if you would like to achieve better performance, please see here: #66 (comment)

[biospank]

Hey @siara-cc thank you for your quick reply.
I'll have a go back on SPIFFS side just to let you know if it works although LITTLEFS seems to be the standard for ESP FS management.
Anyway I'll add an index to see if I can get better performance.

Cheers

[siara-cc]

Never mind, just a suggestion and above may be of use in case you face further issues.

[winkelict]

i think the solution in this issue might fix this problem #81