Modify

Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#5644 closed defect (fixed)

no index on table, time

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

Description

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.name, bp1.version, bp1.publish_time, bp1.author, 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.id, f.name, f.author, 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.id, t.subject, t.body, t.author, t.time, t.forum, f.name FROM topic t LEFT JOIN (SELECT id, name FROM forum) f ON t.forum = f.id 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.id, m.author, m.time, m.forum, m.topic, m.body, f.name, t.subject FROM message m, (SELECT id, name FROM forum) f, (SELECT id, subject FROM topic) t WHERE t.id = m.topic AND f.id = m.forum 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 8 years ago by Odd Simon Simonsen

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 8 years ago by Odd Simon Simonsen

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 8 years ago by Ryan J Ollos

Hello,

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?

Thanks,

  • Ryan

comment:4 Changed 8 years ago by Odd Simon Simonsen

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
Action
as closed The owner will remain Odd Simon Simonsen.
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.