Advice

[adam84]

A feature on my site is the ability to send another member a message (email type thing). Every email that is sent is obliviously stored in my database.

 

I have two columns that deal with deletion. One is if the sender deletes the message, I set that to 1(default is 0) and another other one is if the receiver deletes that message, I set that one to 1.

 

The reason why I am not deleting the row out of the database is because I give the user the option of viewing all the message contact they have had with the other user.

 

Over a while, that table (ISAM) is going to become rather large, especially if the site has a lot of members. My question is how many rows in a table are considered to be overwhelming for a simple search to execute in a reasonable manner. I understand that it can vary.

 

Say I have five millions rows in my table and I want to run the query

SELECT * FROM userMessages 
WHERE 
    (senderID = 34 and receiverID = 423) OR
    (senderID = 423 and receiverID = 34) 
ORDER BY sentDate DESC

 

Based on your sql knowledge, so you think that a query like this would run at a expectable time?

[akitchin]

i doubt it will really slow you down that much - it automatically restricts itself to trawling for just those IDs.  it might be easier to use IN though (however i don't know how this will affect your performance):

 

SELECT * FROM userMessages 
WHERE 
    senderID IN (34,423) AND
    receiverID IN (34,423)
ORDER BY sentDate DESC

[fenway]

Depends which version of mysql -- version 5 has index_merge, and might be smart -- impossible to tell without table structure and EXPLAIN output.