Joins without indexes

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
funtent
Registered User
Posts: 276
Joined: Fri Aug 27, 2004 4:30 pm
Location: Denver
Contact:

Joins without indexes

Post by funtent »

I'm seeing many slow queries because of joins without indexes.

phpBB version 3.3.1

My board (not the one in my sig) has been running since Nov 2002, and I've moved it many many times between hosts, and even between servers within hosts, since then. Extensions installed, extensions removed, etc.

Somewhere, I believe some database tables lost their indexes.

So my question: what's the best way to go through the tables ensuring the indexes are in the right places?

What I've tried: creating a backup from within ACP. Then I created a new database, installing a fresh/blank new phpBB 3.3.1, then importing the backup (data only) into it. Too many MySQL errors to work through with that approach,

Code: Select all

Unknown column 'forum_recent_topics' in 'field list' [1054]
for instance.

My thought now is to put two phpMyAdmin pages side-by-side, one with existing phpBB database and one with the new clean install, and going table by table to check indexes.
User avatar
JoshyPHP
Code Contributor
Posts: 1177
Joined: Mon Jul 11, 2011 12:28 am

Re: Joins without indexes

Post by JoshyPHP »

Install a new forum, dump its schema, then dump your current forum's schema and diff them.
I wrote the thing that does BBCodes in 3.2+.
funtent
Registered User
Posts: 276
Joined: Fri Aug 27, 2004 4:30 pm
Location: Denver
Contact:

Re: Joins without indexes

Post by funtent »

Excellent idea, thanks JoshyPHP. I've got the diff up now.

For those who happen on this topic, here's how to export the xml files from phpMyAdmin:

On the left menu, click your database name.
On the right pane, choose Export from the top menu.
From the Format dropdown, choose XML.
From the Export Method options, choose Custom - display all options.
Uncheck Export Contents from the Data dump options section.
Click Go.
Post Reply

Return to “[3.3.x] Support Forum”