Bug #109

query speed slows as database grows

Added by Chad Berkley over 20 years ago. Updated almost 19 years ago.

Target version:
Start date:
Due date:
% Done:


Estimated time:


As the database grows in size, the speed at which a query result is returned
becomes greater. The database size to query result time ratio seems to grow at
a large rate. These results were obtained by the experiment described below:

First of all, I wrote a script that adds an xml file to the database
repetitively using the command line INSERT feature of I used this script for the following tests. When I
started, the database (under the jones username) had about 15 documents
in it and queries were returned pretty quickly (within 5 seconds or
so). I started by adding several different small xml document (<20
lines, <500 bytes) to the database about 10-15 times each. Query speed
was not noticably effected by adding these documents. It took about 5
seconds or so to add each document. Then I added a larger document (973
lines, 34,000 bytes). I added it about 10 times and it averaged about
30 seconds for the INSERT. After inserting this document query time was
noticable slower. It took about 10 seconds to return any simple query.
Next, I added a bohemith document (~5000 lines, 186,000 bytes). I added
this document only once. It took it 5 min. 20 sec. to insert. After
inserting this document, queries are now slowed to about 15-20
seconds/query. The database now contains 105 documents and xml_nodes
contains 37,449 records. I think we expected these results, and after
loading the 5000 line file, I actually expected it to take much longer
to retrieve a query.


#1 Updated by Matt Jones over 20 years ago

Reassigned to berkley.

#2 Updated by Chad Berkley over 20 years ago

Changed the queries to reduce the number of subqueries which increased the speed
of the query signifigantly.

#3 Updated by Redmine Admin almost 8 years ago

Original Bugzilla ID was 109

Also available in: Atom PDF