PHP / MySQL Performance Suggestion

Question

I have table(1) that holds a total records value for table(2). I do this so that I can quickly show users the total value without having to run select count every time a page is brought up.

My Question:

I am debating on whether or not to update that total records value in table(1) as new records come in or to have a script run every 5 minutes to update the total records value in table(1).

Problem is we plan on having many records created during a day which will result in an additional update for each one.

However if we do a script it will need to run for every record in table(1) and that update query will have a sub query counting records from table(2). This script will need to run like every 5 to 10 minutes to keep things in sync.

table(1) will not grow fast maybe at peak it could get to around 5000 records. table(2) has the potential to get massive over 1 million records in a short period of time.

Would love to hear some suggestions.

Mark Baker · Accepted Answer · 2013-06-15 22:37:10Z

2

This is where a trigger on table 2 might be useful, automatically updating table 1 as part of the same transaction, rather than using a second query initiated by PHP. It's still a slight overhead, but handled by the database itself rather than a larger overhead in your PHP code, and maintains the accuracy of the table 1 counts ACIDly (assuming you use transactions)

answered Jun 15, 2013 at 22:37

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gene Kelly Over a year ago

Interesting. Never thought of using triggers. I am going to read up on them now. Thank You.

Salvador Dali · Accepted Answer · 2013-06-15 22:48:57Z

2

There is a difference between myisam and innodb engines. If you need to count the total number of rows in the table COUNT(*) FROM table, than if you are using myisam, you will get this number blazingly fast no matter what is the size of the table (myisam tables already store the row count, so it just reads it).

Innodb does not store such info. But if an approximate row count is sufficient, SHOW TABLE STATUS can be used.

If you need to count based on something, COUNT(*) FROM table WHERE ... then there are two different options:

either put an index on that something, and count will be fast
use triggers/application logic to automatically update field in the other table

answered Jun 15, 2013 at 22:48

Salvador Dali

224k151 gold badges726 silver badges766 bronze badges

Collectives™ on Stack Overflow

PHP / MySQL Performance Suggestion

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related