> The SQL query is made in function `_get_ticket_revisions` in > https://trac-hacks.org/browser/tracticketchangelogplugin/1.2/ticketlog/web_ui.py#L187 > > SELECT p.value, r.rev, r.author, r.time, r.message > FROM ticket_revision AS tr > LEFT JOIN revision AS r > ON r.repos=tr.repos AND r.rev=tr.rev > LEFT JOIN repository AS p > ON p.id=tr.repos AND p.name='name' > WHERE tr.ticket=%s > LIMIT 10 > > Note the "LIMIT 10" in the last SQL line which I inserted in my experiment. > The total wait time is not influenced by this "LIMIT 10". I am neither a > database nor TRAC expert. But this SQL query looks rather simple. I cannot > imagine that this takes 30 seconds. The `ticket_revision` table is short and > has only 1000 rows on my repo. > > Some specs about my setup: > - TRAC 1.4.2 > - TracTicketChangelogPlugin 1.2 (r17251 by rjollos on 2018-07-30) > - Linux with TRAC running via apache Mod-WSQI > - SQLite database > - 500 tickets > - SVN with 5000 commits > - I have a powerful server with plenty of RAM, SSD and otherwise fast TRAC
I recommend measuring the time of query using ".timer" option in SQLite shell before improving query performance. Also "EXPLAIN QUERY PLAN SELECT ...." is useful to analyze the query. sqlite> .timer on sqlite> SELECT p.value, r.rev, r.author, r.time, r.message ...> FROM ticket_revision AS tr ...> LEFT JOIN revision AS r ...> ON r.repos=tr.repos AND r.rev=tr.rev ...> LEFT JOIN repository AS p ...> ON p.id=tr.repos AND p.name='name' ...> WHERE tr.ticket=17 ...> ; ... Run Time: real 0.001 user 0.000000 sys 0.004000 sqlite> sqlite> EXPLAIN QUERY PLAN ...> SELECT p.value, r.rev, r.author, r.time, r.message ...> FROM ticket_revision AS tr ...> LEFT JOIN revision AS r ...> ON r.repos=tr.repos AND r.rev=tr.rev ...> LEFT JOIN repository AS p ...> ON p.id=tr.repos AND p.name='name' ...> WHERE tr.ticket=17 ...> ; 0|0|0|SEARCH TABLE ticket_revision AS tr USING COVERING INDEX sqlite_autoindex_ticket_revision_1 (ticket=?) 0|1|1|SEARCH TABLE revision AS r USING INDEX sqlite_autoindex_revision_1 (repos=? AND rev=?) 0|2|2|SEARCH TABLE repository AS p USING INDEX sqlite_autoindex_repository_1 (id=? AND name=?) sqlite> -- Jun Omae <jun6...@gmail.com> (大前 潤) -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To unsubscribe from this group and stop receiving emails from it, send an email to trac-users+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/trac-users/CAEVLMaipsXa%2BYcjttoNghydswVH5X5aBLFxK7QRSTw4WTEO9QA%40mail.gmail.com.
