How to interpret explain query results?

[tonywang531]

This is a general question. When I use explain query, the results is almost always 1000 (I think that is the limit). Is this a bad thing? Most of the results are the code of "Column". Is there a way to see total number of executions for the query?

[pawelsalawa]

1000 is just one page. You can move between pages using toolbar icons (with left & right arrow icons).
Also in toolbar you can see current page number and (if you hold mouse cursor over it) total number of pages:

You can change the limit of 1000 per page in configuration of application:

[pawelsalawa]

To count number of rows to be returned from query, modify the query - remove any result columns from it and put count(*) instead.

[tonywang531]

1000 is just one page. You can move between pages using toolbar icons (with left & right arrow icons).
Also in toolbar you can see current page number and (if you hold mouse cursor over it) total number of pages:

You can change the limit of 1000 per page in configuration of application:

I am sorry if I did not explain clearly, when I mean "explain query" I mean this button in sqlite studio.

The initial setting is limited to 1000 and I cannot change page.

After changing number of data rows to 10000, I can view all the results like this: (this is the same sql query)

Is this a bug by any chance? Also thanks for the count(*) tip it is a nice method which is faster than individual selection.

[pawelsalawa]

Yes, that looks like a bug.

[tonywang531]

Another similar bug:

The database usually fails to load the last page when the table is large (usually larger than one million). As you can see in the picture it stops somewhere strange. Refresh the table again can fix the problem. This is the "true" last page.

Perhaps it is better to disable view last page until it can be displayed?

[pawelsalawa]

Weird. Is there a chance you can share this db file for sake of reproducing this bug?

[tonywang531]

Weird. Is there a chance you can share this db file for sake of reproducing this bug?

I am sorry the data is confidential and cannot be shared. I will try to create a similar database and see if I can reproduce the same bug.

Update: I tried to replicated the same structure at home but to no anvil. I have SSD at home so even making 3 million rows the results is still displayed instantly.

Update2: After much experimenting with the database (original), I did not find the cause of the problem. However I have found that the database not loading last page bug can be fixed by clicking "Refresh all database schemas". Perhaps this database is just too big (17G and over) and needs to refresh from time to time. I believe this solves the second bug. The first bug, remains unsolved.