homepage Welcome to WebmasterWorld Guest from 54.242.18.190
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
phpBB MySQL Indexing
AlwaysWorkin




msg:3790777
 8:29 pm on Nov 20, 2008 (gmt 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.

 

ZydoSEO




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

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.

AlwaysWorkin




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

Thanks,

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved