Help - improper use of index majorly bottlnecking mysql

cadkins23

Hi everyone. I started an adult social networking site in August 2006, now we have more than 40,000 members. Unfortunately I'm the only person doing everything and am not really a skilled coder or anything. I'm in no way an expert, so please let me know what I should do, etc.

Anyway here is the problem. When a user deletes his or her account, the site gets laggy because of the severe bottlenecking. For about 10 minutes I will see bottlenecking taking place, and out of all of the delete queries that are being run, there are few queries that are actually causing the bottleneck.

Let me explain:

If I'm correct, an index on a table is basically which row you want to have your data stored by to make it easier for mysql to find results. One extreme example of an improper index usage would be when I recently discovered that my programmer had the code like this: update messages_sent set read_status=1 where subject = "Re: Re: Re: Re: Re: About what we were saying"

Obviously you wouldn't want to search the entire table of hundreds of thousands of messages for this subject and not have any idea where mysql should begin searching, etc. I fixed this problem by updating where the message_id = the message id, which happens to be the index, now the problem is gone thank goodness.

Here is where I'm still having trouble.

There are 4 queries still giving me a bottleneck, between just 2 tables.

messages
and
messages_sent

both of these tables have message_id as the index since every single time a message is read or sent message is read, or replied to, the read_status is updated. As I said, this alone was causing a bottleneck until I decided to do this differently by using the message_id which is the index.

Now I notice the bottlenecking is caused because when they delete their account, please refer to these 4 queries, you'll notice none are using the index, which is the message_id field. So this is causing a bottleneck since the messages and messages_sent tables are being locked, etc. Looking at the mysqladmin command process list, I see that it isn't uncommon for a single one of these queries below to LOCK the table for more than 5 minutes at a time. 🙁

delete from messages where mess_by = $SESSION[member_id]
delete from messages where mess_to = $SESSION[member_id]
delete from messages_sent where mess_to = $SESSION[member_id]
delete from messages_sent where mess_by = $SESSION[member_id]

When someone writes a message on the site, it is stored with a message id, and of course the id of the sender and receiver, along with the read status.

The most common query on these tables is when someone simply reads a message and the table is locked while the row is updated to show read_status=1.

We of course update where the message matches the message_id, the index.

The problem is when a user deletes his or her account and rather than delete the message by message_id, we delete where the messages are to or from the user removing their account. Since neither mess_by or mess_to are indexes on either tables, the bottleneck begins.

I've learned A LOT since August, I've learned A LOT in the past week too. 🙂 I appreciate any and all suggestions.

Thanks,

Chris

Piranha

You have actually stated the solution yourself. Make those columns indexed as well, it would probably solve the problem.

A solution if it is not solving the problem is to have a column in the member table showing status. Every time you load a page you then check if the status is set to active. If you do it this way you don't have to remove everything at once, you can wait until the server load goes down and then delete the messages. The columns should be indexed with this solution as well.

By the way, I would not trust your "programmer" to handle the database, he clearly don't know anything about it. It is not only that it might take time, it might as well delete messages that was not intended to be deleted.

cadkins23

Thanks Piranha, I was thinking the same thing as you regarding accidentally deleting messages. My programmer took it upon himself to send out a bulletin in my name (since I have 38,000+ friends) advertising his business, then he put an ad on the site for his business, etc. The other day I noticed images weren't working on some pages, I told him and went back and forth between my hosting company because I thought it was a server problem. 🙂 It turns out he put the word EXIT in the php file way early which was stopping further execution. 🙂

I fixed that, then the query I mentioned above, etc. I think you're right about not trusting him. Thanks for your help btw.

So I can have more than 1 field as index?

I'm not sure I'm following wha tyou said, I should make the read status an index and then if it's not already read then update it, don't necessarily update it every time because it may already be read is that what you're saying?

Thanks a lot!!

Chris

Piranha

Yes, you can have more than one column as index. And normally you should have every column that you use to search as index. So mess_by and mess_to should be indexed.