[Yum-devel] Sqlite performance + tuning

[seth vidal]

On Mon, 2007-03-19 at 17:29 +0100, Florian Festi wrote:
> Hi!
> 
> As yum now also resolves only on the xml/sqlite data some of our results 
> might interesting for the yum development, too.
> 
> It turned out that there where no indices in the sqlite database for some of 
> the common use cases:
> 
> returnPrco() in sqlitesack.py:
> CREATE INDEX pkgprovides ON provides (pkgKey)
> CREATE INDEX pkgrequires ON requires (pkgKey)
> CREATE INDEX pkgconflicts ON conflicts (pkgKey)
> CREATE INDEX pkgobsoletes ON obsoletes (pkgKey)
> 
> Those could be handy if the YumSqlitePackageSack.searchFiles() would try to 
> make direct use of those columns:
> CREATE INDEX filenames ON files (name)
> CREATE INDEX dirnames ON filelist (dirname)
> 
> I doubt that this will give any visible improvements with the current 
> depsolver as it is currently hidden by other problems but as the other 
> problems will get fixed the difference will increase. With our own resolver 
> the time needed for resolving an "install *" on FC6 + extras + updates (6580 
> packages) dropped from 10 to 3 Minutes.
> 
> As there are plans to directly ship the sqlite dbs insted of the XML 
> metadata, it might be worth checking if it is better to create the indices 
> on the client side (sorry, didn't any checks on myself yet).
> 
> It also turned out that if any quadratic behavior and looping over files is 
> avoided resolving is reasonably fast.

How much bigger do these indexes make the sqlite db files?

-sv