Very slow opening database with FTS (Full Text Search)
Issue #105
resolved
Originally reported on Google Code with ID 105
What steps will reproduce the problem?
1. Create a FTS index in a database as described at http://sqlite.org/fts3.html
2. Try to open that database with pgpliteadmin
What is the expected output? What do you see instead?
With 16000 (small) document records in the database index, opening is normal (within
a second). With the FTS index defined, opening takes over a minute.
What version of the product are you using? On what operating system?
v1.9.1, linux
Please provide any additional information below.
FTS creates 'hidden' tables ft, ft_content, ft_segdir and ft_segments. These tables
do not have a regular index so doing a count(*) on them is very slow.
phpliteadmin shows them in the stucture page and the count(*) on each table takes a
very long time.
Reported by jos.grootlipman
on 2012-05-12 09:03:05
Comments (3)
-
-
Okay, I guess we should not simple use the table name "ft_*" or something like this, but rather see whether the table has a primary key. If it doesn't, we should not count the records as there could be quite a lot and counting would need some time. We could also add some link to count the records nevertheless. I just wanted to implement this, but I think we should do it properly and not hardcode some common case. So let's see whether I find the time in the next days to do it. It still shouldn't be so much work.
Reported by
crazy4chrissi
on 2013-01-21 23:20:14 - Status changed:Accepted
-
Okay, I just submitted a version to SVN as revision #335 that should fix this issue. In the DB-structure tab, we now only count the number of rows of a table if the table has a primaryKey or the complete database is small (currently <=2MB). Otherwise, we just say '?'. You can get the latest development version from svn here: https://phpliteadmin.googlecode.com/svn/source/1.9.4/phpliteadmin.php I have not yet tried if this fixes issues with FTS, as I did not create an FTS index yet. Could please somebody who is affected by this issue check whether this version solves your problem? It would also be good if somebody could comment on my choice of 2MB as a DB-size in which a count() can be safely done in a short amount of time, even without indexes.
Reported by
crazy4chrissi
on 2013-02-10 22:38:09 - Status changed:Fixed
- Log in to comment
Reported by
crazy4chrissi
on 2012-11-06 15:08:51