Options :
Page caching
Action caching
Fragment Caching
Read these:
Real World Rails series
Content Caching in Rails
For fragment caching, use timed_fragment_cache which allows the cached content to be expired automatically based on a timeout.
I was thinking of allowing my users to request an explicit rebuild of the page by forcing the cache to expire before the timeout. This could be done by providing an action which will simply expire the fragment and redirect back to the original action.
However in the event that two users click on the rebuild request within say 1 minute of each other, I want the subsequent build request to be ignored automatically. I will take a stab at modifying the timed_fragment_cache plugin for doing this.
Thursday, July 19, 2007
Sunday, July 15, 2007
SQL Query optimization
To begin optimization, select the SQL query that
- returns max number of rows
- has most number of executions
- most I/O
Optimizations:
- The basic tenet is to avoid full table scans. Add an index to the fields mentioned in the where clause. Indexs are created as an additional file typically that the database can consult to filter the table based on the values of the corresponding field. Be careful, don't add to many indexes as every index requires an additional change to the database whenever data is inserted in the table. The indexs are typically stored as BTree indexes.
Use EXPLAIN Select query... to examine if the database is using the indexes correctly.
If you do any kind of operation on a field in the where clause, the full table is scanned since the field value for each record needs to be computed to be compared. As a result the index on such a field is a waste. Try using the field as is.
- Joins should be ordered so that the table that has the max rows to be processed is filtered the most. The order is also imp. If you are joining A, B and C where C is 10 rows and A and B are 100000000 rows each, then it makes sense to join B and C first instead of joinging A and C first.
Apply max filtering to table that has max rows.
Other Database trivia:
1NF - normalization in which repeated fields (score1, team1, score2, team2) are moved into their own table. One to many relationship.
2NF - normalization in which repeated values (publisher and publisher address for a list of books) are moved into their own table. Many to Many relationship.
3NF - TO be written.
- returns max number of rows
- has most number of executions
- most I/O
Optimizations:
- The basic tenet is to avoid full table scans. Add an index to the fields mentioned in the where clause. Indexs are created as an additional file typically that the database can consult to filter the table based on the values of the corresponding field. Be careful, don't add to many indexes as every index requires an additional change to the database whenever data is inserted in the table. The indexs are typically stored as BTree indexes.
Use EXPLAIN Select query... to examine if the database is using the indexes correctly.
If you do any kind of operation on a field in the where clause, the full table is scanned since the field value for each record needs to be computed to be compared. As a result the index on such a field is a waste. Try using the field as is.
- Joins should be ordered so that the table that has the max rows to be processed is filtered the most. The order is also imp. If you are joining A, B and C where C is 10 rows and A and B are 100000000 rows each, then it makes sense to join B and C first instead of joinging A and C first.
Apply max filtering to table that has max rows.
Other Database trivia:
1NF - normalization in which repeated fields (score1, team1, score2, team2) are moved into their own table. One to many relationship.
2NF - normalization in which repeated values (publisher and publisher address for a list of books) are moved into their own table. Many to Many relationship.
3NF - TO be written.
Subscribe to:
Posts (Atom)