ARCHIVE: Five simple tips for High Performance DB sites
Most of my newer /tech 'blog entries are a result of my posts on various online tech sites. Tonight's tips come from the Digg thread about High Performance Site Coding.
In addition to the language/server tips mentioned above, here's a few more database-oriented tips I think designers of large/busy sites should consider:
In addition to the language/server tips mentioned above, here's a few more database-oriented tips I think designers of large/busy sites should consider:
- Don't use persistent links (mysql_pconnect) on busy sites. EVER.
- Minimize joins: "SELECT users.userid, users.username, comments.comment FROM users INNER JOIN comments ON users.userid=comment.userid" - if you just start storing username (that rarely changes) in the comments tables, you can just "SELECT userid, username, comment FROM comments" and avoid the join altogether. You can't do this everywhere and it's not the optimal-normalized elegant db structure that you learnt about, but guess what? It's fast!
- Try using commands like the SQL EXPLAIN to see where the bottlenecks are.
- Don't put everything in the database: Hardcode things that you know will rarely change - this is a big one because I've seen systems with a truth table containing 'T = true' and 'F = false' stored. Or more commonly 'yesno' table with 'Y = Yes' and 'N = No' - hardcode these already! Also, even if types of status (P=pending, C=completed, X=cancelled etc.) can change, just put them in an array in your globals file. Think how many SQL calls you're making because of these. At least one per page!
- Most importantly, pick a database that fits your needs perfectly. Don't use MySQL 5.x just because everyone's using it now. Don't use Postgres because all the cool kids are doing it. Find out which version of which db best satisfies your need the best. I've seen sites falter under Oracle that worked great under MySQL 4.1. Why? Because the site just needed very fast SELECTs with few INSERTs and almost no UPDATE queries. Older version of MySQL's great for that kinda stuff. Then when you absolutely need triggers and views, upgrade to the bigger dbs. Stick to simpler dbs if you can - they use less resources in general.