Modify

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#5644 closed defect (fixed)

no index on table, time

Reported by: ThurnerRupert Owned by: osimons
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 5 years ago by osimons

  • Priority changed from normal to high

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 5 years ago by osimons

  • Resolution set to fixed
  • Status changed from new to closed

(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 5 years ago by rjollos

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 5 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.

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from osimons. Next status will be 'closed'.
The resolution will be deleted. Next status will be 'reopened'.
Author


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

 
Note: See TracTickets for help on using tickets.