id,summary,reporter,owner,description,type,status,priority,milestone,component,version,severity,resolution,keywords,cc,branch,changelog,apichanges,internalchanges 6986,MySQL schema changes for performance,mikeyp@…,,"We have been dealing with extremenly poor Trac performance when using MySQL as the backend database. The root cause is the use of 'text' fields in many columns that should preferably be varchar. We finally decided to make some schema data changes to improve performance. As a result of these changes, we have substantial performance improvements. For an example ,the following query is executed very often by Trac: {{{ SELECT DISTINCT s.sid, n.value, e.value FROM session AS s LEFT JOIN session_attribute AS n ON (n.sid=s.sid and n.authenticated=1 AND n.name = 'name') LEFT JOIN session_attribute AS e ON (e.sid=s.sid AND e.authenticated=1 AND e.name = 'email') WHERE s.authenticated=1 ORDER BY s.sid; }}} Our execution time dropped from 8 seconds for this query to approximately 0.1 seconds. When timing this, there were about 6000 rows in the session table, and 20k rows in session_attribute. The only data type changes me made were to convert several columns from text to varchar, primarily colums used often in joins and where clauses. We also added indexes on ticket.{priority,milestone,component} I am attaching three files: - trac_schema_before is a MySQL dump of our table definitions before the changes (Trac 0.10.4 bas schema) - trac_schema_after is a MySQL dump of our table definitions after the changes. - trac_schema_diffs shows the changes we made. I recommend that these changes or similar ones be merged into a future Trac version. Thanks, mike ",enhancement,new,normal,next-major-releases,general,0.11rc1,major,,performance mysql database patch,dx@… info@… peter@…,,,,