Is there a way to create Indexes that will actually speed up record searches in LibreOffice Base?

[LibreLyrae]

Is there a way to create an index in LibreOffice base that will actually speed up the searches. I used:

CREATE INDEX IDX_FName ON “Contacts”(“FirstName”)

which was executed successfully (though required a re-start of Base due to an error message saying I was out of memory). However upon doing the exact same search as without the index the search took the exact same amount of time (A full 12 seconds, which will only increase as the database gets used more and acquires more records).

I have written queries which are faster but I had problems with that approach as well due to client-specific considerations (my client doesn’t know SQL, nor can he spell well enough for the exact match necessary for a parameter input query) See my other question here: Is it possible to put a wildcard search into a parameter input query in Base?

As always any input is greatly appreciated!

[oweng]

Can you provide a few further details e.g., platform, Java version, LO version, type of database (embedded HSQLDB, or external), number of records in the Contacts table, and the query you are running against the table? I am not sure what the indexing effect is, but there may be other things you can do to improve performance. Thanks.

[the_watchmann]

Is this not maybe related to Bug 51239. Look it up @ https://bugs.freedesktop.org/show_bug.cgi?id=51239.
I had a problem with LO 3.5 initially which got worse with 3.6 etc etc… Anyway, it seems to have been sorted out with LO 4.1.3, being slightly slower than LO 3.4 but not all that noticeable. Believe me, I have been working on a 40000 plus entry database, so I will notice if the speed of Base is affected.

[oweng]

@the_watchmann, it is possible although unlikely IMO. There are many Base performance-related bugs but I think this question is asking a more general one i.e., that use of an index appears to have no effect on performance. Bug fdo#51239 dealt with a regression in performance (many factors slower) in v3.5. Bug fdo#51976 dealt with a regression in performance (2 factors slower) in v3.6. Bug fdo#53281 is a further v3.5/3.6 spin-off. All are technical and there is a JDBC factor involved as C->Java is 100 times slower (refer 51976#c26 and fdo#57872). The original question does not provide enough details to determine whether any of these are related. We will never know until those details are forthcoming.

[LibreLyrae]

A little late on the follow-up my apologies. My question was just whether Index will actually increase search speed, my attempts were unsuccessful. I use form based filters instead, with queries for complex searches I use often. I even taught those who cannot spell and are not computer savvy to use the filters as well, particularly with LIKE, and they’ve caught on and ultimately find it more useful and quicker than trying to use the search.

[LibreLyrae]

And I was/am using Ubuntu 12.04, Ubuntu 13.04, LO 4.0.0.3 and now LO 4.1.3.2 which I do find to be a bit faster. There’s less than 2000 records, which is why I was surprised by how slowly it goes through the whole set, using Record Search. I’m a bit of a newbie but I understand databases are supposed to speed up when you make an INDEX for tables you search often so I tried that without success. Form based filters are ultimately quicker and more useful for our purposes.

[iplaw67]

The problem is that the whole record set is loaded into memory before the search is carried out and then each tuple iterated (I assume you are using the Search function dialog which is accessed via the binoculars icon), so having an index will not make much of a difference. There may or may not be an Easy Hack performance enhancement bug request for this, there was certainly talk at one stage about putting it out there for some kind volunteer to improve the code here. In the meantime, you seem to have found that the most optimal way at present is to use form-based filters.