Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

phpBB MySQL Indexing

8:29 pm on Nov 20, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 28, 2005
posts: 76
votes: 0

I have about 600K users and my wordmatch table is around 380 MB for my phpbb 2x site using MySQL.

Long story short - it's running slow. I've been researching phpBB forums the past couple days as well as many others and indexing these tables appears to be the key to performance (based on how phpBB does it's searching, not efficient).

There appears to be a couple MODS available, but they all appear to be sketchy in terms of the results and some of them are no longer supported. So after many hours of researching, I feel I'm back to where I started...a slow forum and no idea how to fix it (and no confidence in what solutions I can find).

DB work isn't my strong suit, and that is making this difficult to fix. I feel like I can't be the only one who's been in this situation and I'm wondering if anyone has any suggestions?

Is there an indexing script available I haven't found?
Anyone have any experience outsourcing this type of DB MySQL work?

I appreciate any advice. Just not used to being in this kind of situation...one I can't fix myself.

3:59 am on Nov 21, 2008 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 11, 2007
votes: 3

If you currently have no indexes on your user table, then when you do a SELECT * FROM tUser WHERE Username='someuser' the DB has to do a full table scan to find that row. In other words, it has to look row by row through the table checking each row to see if Username='someuser'. This is inefficient. If you have 600K users in the table, then on average it will have to look at 300K user rows before finding your desired row every time you query it. So yes... Your problem is likely the lack of indexing if you don't have any, or are not indexing the correct fields.

How you index the table should be determined by how you look up users in the table (i.e. which fields you use to find rows in the table). Without knowing which fields are in the table (username? password? firstname? lastname? email address? etc.) and how you query the table, it's hard to say what your index(es) should be like.

Does a user enter a username/password on a login screen and then you look them up to see if there is a row in the table for that username/password combination? If so maybe you need an index on Username/Password combination.

Do you look users up sometimes by Firstname/Lastname? If so then maybe you need an index on Firstname/Lastname combination.

Do you look users up by email/password? Then maybe you need an index on email/password combination.

Do you every look users up by just username or just email? Then maybe you need an index on just username or just email, respectively.

Do you do all of the above? Then maybe you need multiple indexes.

The thing you need to watch out for with indexes is that while they make SELECTs, UPDATEs, and DELETEs run much faster (because it can quickly find a particular row in the table), it does so at the cost of making INSERTs run slower because it can no longer simply shove a new row into a table. If you have 4 indexes on the table then every time you do an INSERT it has to also go update all 4 indexes so that in the future it will know how to quickly find the row you just inserted.

Like everything in computing, there is always a trade off.

1:50 pm on Nov 21, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 28, 2005
posts: 76
votes: 0


I understand the concepts of indexing, after all the reading I feel like I could write a paper on it...but the execution is where I'm afraid to jump in. I know it will take a long time to index, and a lot of things can go wrong with it (given the experiences I've been reading about).

I'm having a hard time knowing how to execute and tackle this speed issue.