1,250,000 Rows

[jaymc]

I have a huge table that records which members have viewed someones profile and the time

 

At this moment it has accumulated over 1 million rows

 

Im worried that when that table is accessed it will basically blow mysql up (kidding)

 

Here are the queries executed in relation to the table

 

$queryj = "SELECT `viewer` FROM `viewedpro` WHERE `username` = '$_GET[user]' ORDER BY `time` DESC LIMIT 0,10000";

$queryj = "SELECT `viewer`, `views` FROM `viewedpro` WHERE `viewer` = '$User_Session' AND `username` = '$_GET[user]' LIMIT 0,1";

$queryl = "UPDATE `viewedpro` SET `views` = '$hits', `time` = '$time' WHERE `username` = '$_GET[user]' AND `viewer` = '$User_Session' LIMIT 1";

 

Note that the 10,000 limit is because I now at the moment no member would have had 10,000 unique visitors

 

The rest out Limited to 1

 

Will this be ok or is is destened to kill my server due to the size of the table

[ShogunWarrior]

If it was me I would not use the username as the user key, I would as an integer user ID. This is more indexable and generally more normalized.

Maybe think about periodically archiving older rows to an archive table(s) to improve efficiency.

AFAIK speed will be a problem because MySQL must lock/re-index the table each time a user views a profile, which is often.

[btherl]

There's also the saying "If it ain't broke, don't fix it"

 

Ideally you need a way to measure the performance.  That way you know if there really is a problem.  Measuring and logging query times is a good way to do this.

 

1 million rows is probably fine as long as you are using an index.  If there is a problem, then I think the biggest wins you will get are by archiving off old rows (this is what friendster does for example, it only tells you about recent views) or by partitioning your table by username (for example, all users starting with 'a' in one table, all starting with 'b' in another table, etc etc)

 

You can do broad partitioning, like A-K, L-M, N-Z or very fine partitioning, such as every starting letter.  This basically gives you lots of small tables instead of one big table.

 

But once again, don't fix it unless it's actually broken

 

If you commonly do time range queries, like 'all users in the last month', then partitioning by date may be appropriate.

[fenway]

Also, you probably want a covering index on (username, viewer).

[ShogunWarrior]

Yes, add an index. As long as space isn't a concern adding appropriate indexes can be very advantageous.

[jaymc]

I have indexes in the appropriate places

 

Can you explain what a covering index is, or is it essentually, a standard index

[fenway]

Post the show create table output... I doubt you have the appropriate indexes.

[btherl]

My impression is that jaymc doesn't have any performance problem currently.. he's just worried about future problems.  Is that correct?  If so, he already has appropriate indexes.  A sequential scan on a million rows will definitely be noticeable