Very slow opening database with FTS (Full Text Search)

[Former user]

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

[Christopher Kramer]

Thanks for reporting this issue. Sorry for the late reply.

I guess we'll simply not do a count on ft_* tables. I'll have a look on it and fix
this soon.

Reported by crazy4chrissi on 2012-11-06 15:08:51

[Christopher Kramer]

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

[Christopher Kramer]

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