Hey, ----- On 13 Nov, 2014, at 13:27, Ryan Schmidt [email protected] wrote:
> Sure... > >> DROP TABLE sqlite_stat1; >> EXPLAIN QUERY PLAN SELECT id FROM files WHERE actual_path=? AND active; > > 0|0|0|SEARCH TABLE files USING INDEX file_actual (actual_path=?) > >> EXPLAIN QUERY PLAN UPDATE files SET actual_path=?, active=1 WHERE path=? AND >> id=?; > > 0|0|0|SEARCH TABLE files USING INDEX file_path (path=?) > >> ANALYZE; >> SELECT * FROM sqlite_stat1; > >> EXPLAIN QUERY PLAN SELECT id FROM files WHERE actual_path=? AND active; > > 0|0|0|SEARCH TABLE files USING INDEX file_actual (actual_path=?) > >> EXPLAIN QUERY PLAN UPDATE files SET actual_path=?, active=1 WHERE path=? AND >> id=?; > > 0|0|0|SEARCH TABLE files USING INDEX file_path (path=?) OK, that was a red herring, mostly because I didn't also instruct you to drop the sqlite_stat4 table as well and re-open the sqlite3 tool. See this: With table metadata: sqlite> EXPLAIN QUERY PLAN SELECT id FROM files WHERE actual_path=? AND active; 0|0|0|SEARCH TABLE files USING INDEX file_actual (actual_path=?) sqlite> EXPLAIN QUERY PLAN UPDATE files SET actual_path=?, active=1 WHERE path=? AND id=?; 0|0|0|SEARCH TABLE files USING INDEX file_path (path=?) Now: sqlite> DROP TABLE sqlite_stat1; sqlite> DROP TABLE sqlite_stat4; sqlite> ^D :( clemens@cSchlepptop:~$ sudo sqlite3 /opt/local/var/macports/registry/registry.db SQLite version 3.8.7 2014-10-17 11:24:17 Enter ".help" for usage hints. sqlite> .load /Users/clemens/Development/MacPortsBase/src/cregistry/macports.sqlext sqlite> EXPLAIN QUERY PLAN SELECT id FROM files WHERE actual_path=? AND active; 0|0|0|SEARCH TABLE files USING INDEX file_actual (actual_path=?) sqlite> EXPLAIN QUERY PLAN UPDATE files SET actual_path=?, active=1 WHERE path=? AND id=?; 0|0|0|SEARCH TABLE files USING INDEX file_port (id=?) And there's the problem – the query that updates the active state in the database doesn't use the file_path index anymore, but instead the file_port index that maps the registered files to ports. Of course, the file_port index has the same value for a lot rows (all belonging to the same port) and is thus a bad index to be used for this query. In essence each update of a file while activating boost selects all files installed by boost and then goes through them one by one to find the one with the right path, possibly doing a strcmp() on each row. Unsurprisingly, this is slow. I assume the same will be the case for deactivation. We should find a way to force the usage of the file_path index with these queries. -- Clemens Lang _______________________________________________ macports-dev mailing list [email protected] https://lists.macosforge.org/mailman/listinfo/macports-dev
