Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#5644 closed defect (fixed)

no index on table, time

Reported by: rupert thurner Owned by: osimons
Priority: high Component: FullBlogPlugin
Severity: normal Keywords:
Cc: Trac Release: 0.11


clicking on the timeline queries tables by time, but there is no index. if the table gets larger, this might slow down the query.

2009-08-09 06:44:50,720 Trac[main] DEBUG: Dispatching <Request "GET u'/timeline'">
2009-08-09 06:44:50,799 Trac[session] DEBUG: Retrieving session for ID 'rupert.thurner'
2009-08-09 06:44:50,819 Trac[chrome] DEBUG: Prepare chrome data for request
2009-08-09 06:44:50,897 Trac[model] DEBUG: get_blog_posts() SQL: 'SELECT, bp1.version, bp1.publish_time,, bp1.title, bp1.body, bp1.categories FROM fullblog_posts bp1 WHERE bp1.version_time>%s AND bp1.version_time<%s ORDER BY bp1.publish_time DESC' ((1241992799, 1249855199))
2009-08-09 06:44:50,920 Trac[model] DEBUG: get_blog_comments() SQL: 'SELECT name, number, comment, author, time FROM fullblog_comments WHERE time>%s AND time<%s' ((1241992799, 1249855199))
2009-08-09 06:44:58,441 Trac[timeline] DEBUG: start: 2009-05-10 23:59:59.812740+02:00, stop: 2009-08-09 23:59:59.812740+02:00, filters: ['blog', 'milestone', 'ticket', 'changeset', 'wiki', 'discussion']
2009-08-09 06:44:58,444 Trac[timeline] DEBUG: SELECT,,, f.subject, f.description, f.time FROM forum f WHERE f.time BETWEEN 2009-05-10 23:59:59.812740+02:00 AND 2009-08-09 23:59:59.812740+02:00
2009-08-09 06:44:58,453 Trac[timeline] DEBUG: SELECT, t.subject, t.body,, t.time,, FROM topic t LEFT JOIN (SELECT id, name FROM forum) f ON = WHERE t.time BETWEEN 2009-05-10 23:59:59.812740+02:00 AND 2009-08-09 23:59:59.812740+02:00
2009-08-09 06:44:58,478 Trac[timeline] DEBUG: SELECT,, m.time,, m.topic, m.body,, t.subject FROM message m, (SELECT id, name FROM forum) f, (SELECT id, subject FROM topic) t WHERE = m.topic AND = AND time BETWEEN 2009-05-10 23:59:59.812740+02:00 AND 2009-08-09 23:59:59.812740+02:00

Attachments (0)

Change History (4)

comment:1 Changed 9 years ago by osimons

Priority: normalhigh

Yeah, I know... I started without indexes with the intention of adding as needed. I've just not got around to it yet. This is a good candidate - thanks for reminding me.

comment:2 Changed 9 years ago by osimons

Resolution: fixed
Status: newclosed

(In [6484]) FullBlogPlugin: Adding some basic time-based indexes to improve performance of Timeline (+ minor refactoring of some db code). New db and plugin versions, require upgrade.

Closes #5644.

comment:3 Changed 9 years ago by Ryan J Ollos


I don't quite understand the change, and just trying to determine if I should go through the trouble to upgrade.

Is it strictly a performance issue with the timeline, and therefore if I'm not experience any performance problems can ignore for the time being? Or might there be some other reason I should go through the effort to upgrade?


  • Ryan

comment:4 Changed 9 years ago by osimons

It is just adding indexes for better performance for time-based queries of posts and comments - like typically in Timeline. Depending on the number of posts/comments it may be noticeable, but feature-wise it makes no difference.

Modify Ticket

Change Properties
Set your email in Preferences
as closed The owner will remain osimons.
The resolution will be deleted.

Add Comment

E-mail address and name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.