Being a relative n00b at SQL, optimization has never been my thing. Its complicated, its usually limited in applicable scenarios, and frankly its time consuming. Of course, eventually SQL optimization is a must, but there's one cheater's solution before you come to that road: SQL indexes. Using an index, or two or three indexes, allows much faster execution of common queries all across the board. In an example today:
Code:
SELECT COUNT(op.id) AS total_adopts, SUM(op.clicks) AS clicks, sum(op.views) AS views, user.username, user.userid FROM zf_ownedPokemon AS op, user WHERE op.owner = user.userid GROUP BY op.owner ORDER BY total_adopts DESC LIMIT 30
With an index only on "op.id", the query took nearly 20 seconds. With the index added, the query took just under a second: a 20 times optimization. A metaphore used at Database Journal describes the well: it would take you forever to find what you need in a large technical manual, but with an index, its much easier.
