Fonte: http://www.catswhocode.com/blog/10-sql-tips-to-speed-up-your-database

Aproveitando a dica do @pinceladasdaweb, dei uma lida no post e vou fazer uma tradução livre aqui, acrescentando meu ponto de vista e mais algumas dicas sobre SQL em um banco de dados relacional:

1. Defina seu banco de dados com cuidado
Um bom banco de dados é um banco de dados bem desenhado, que não gera tanto trabalho para buscar as informaçoes e que não guarda dados duplicados. É um banco que tem uma estrutura clara e com nomes de tabelas e campos identificáveis.

2. Otimize o que for necessário
Nem todos os SQL que utilizaremos devem e podem ser utilizados. Mas várias instruções podem e DEVEM ser otimizadas. Para o banco cada query tem um custo, e utilizando a ferramenta e EXPLAIN, que tem em praticamente todos os bons bancos de dados, podemos ver o que está afetando mais nossa query. Cuide os joins, linhas desnecessárias, igualdades incorretas, falta de indices, etc…

3. Use algo para agilizar
Utilizar um sistema de cache é sempre muito bom, e não apenas para as querys. Pois tudo que tem que ser gerado e processado gera custo e tempo, sendo que se você já tem um resultado pronto, do que você quer, sendo q o mesmo já foi processado a algum tempo atrás é muito bom ! Isto acaba com o tráfego na rede e no processamento do banco.
No site ele cira algumas ferramentas interessantes. E devem ter outras também.

4. Não selecione o que você não precisa
Corretíssimo! Nada de “select * …” por ai heim !
Coloque na projeção do SQL ( aquela parte depois do “select” e antes do “from” ) apenas os campos que você vai precisar. Isto nos além de gerar menos custo para o banco, acaba organizando nosso retorno e padronizando a aplicação.

5. Limite o numero de linhas a serem retornadas
Geralmente o usuário final, aquele que está vendo aquela listagem de algum item na tela, não vai precisar de todos os itens que tem cadastrado na tabela. Então use paginação ! Sempre ! Limitando o número de linhas a serem retornadas do banco o custo cai ser bem menor e a resposta vai ser mais rápida. O uso de Ajax ( para web ) geralmente ajuda bastante nestes casos.

6. Não coloque chamadas ao banco dentro de loops
Organize seu código, que eu duvido que você não conseguirá unir todos os dados necessários em apenas 1 ou 2 chamadas no banco, ao invés de colocar a query em um loop. Lembre-se, que o quanto menos tiver que acessar o banco melhor !

7. Utilize JOINS ao invés de SUBQUERYs
Concordo ! A não ser em casos muito, muito complexos, não utilize subquerys, elas são muito mais pesadas e são executadas 1 vez para cada linha retornada da nossa query pai, gerando assim um custo enorme para o banco. E subquerys na projeção NEM PENSAR ! NUNCA !

8. Utilize os wildcards
Até certo ponto eu levo isto como uma feature mesmo, do SQL ansi, com o comando LIKE, do que um forma de otimização.

9. Utilize UNION ao invés de OR
Bem, eu não entendi direito o pq desta dica, até mesmo pq eu não utilizo o MySql, mas para mim isto não é válido, pois na grande maioria dos casos esta troca causará um aumento de custo ao banco, e não o contrário. Mas sempre teste sua query com mais de uma forma, talvez este seja seu caso.

10. Use Índices
Sim ! Use indices no seu banco. Mas além das PK e os indices para elas criados automaticamente, utilize somente indices em campos e tabelas que necessitarão deles. Se un indice não é utilizado em nenhum momento, em nenhuma query, exclua-o, pois ele estará gerando trabalho em vão para o banco atualizá-lo.
Como eu faço:
- Rodo a query e analiso
- Crio indices onde eu acho que deve ser criado
- Rodo a query e analiso
- Crio, altero e excluo mais algins indices onde eu acho que deve
- Rodo a query e analiso
- Excluo todos os indices que não estão sendo utilizados
- Rodo a query e analiso
- Pronto!

Geralmente estes poucos passos bastam controlar a criação de indices.

11. Triggers – Tome cuidado com elas
Elas podem nos ajudar muito, mas podem deixar o banco de dados lentão e até gerar dead-locks. Então pense bem se é realmente necessário criar tal procedimento como uma trigger.

12. Sequences/Autoincremento
Estes campos nos facilitam muito o controle das PKs e não oneram em trabalho do banco.

13. Procedimentos pesados, Batch
Estes sim eu aconselho a, sempre que possível, serem executados dentro banco.
A linguagem que roda dentro do banco de dados é muito mais ágil ao trabalhar com os dados do que ficar trazendo e enviando dados do banco até a aplicação. E isto em procedimentos que geram informações de cálculos violentíssimos, ou controles com muitos dados, e que podem demorar para rodar, podem ser colocados dentro do banco.
No PostgresSQL por exemplo, temos não somente o Pl/pgSQL, mas sim inúmeras linguagens que trabalham dentro do DB, no Oracle tem o PL/SQL, e assim vai…

No mais é o seguinte:

• Sempre teste suas querys com mais de uma forma, trocando campos de lugar, trocando a ordem dos joins e das filtragens.
• Quantos aos frameworks de banco, leia bastante na documentação como fazer para agilizar as pesquisas.
• Aprenda a alterar as principais configurações do seu banco de dados para que ele trabalhe melhor para sua aplicação, sendo que ela faz mais gravação de dados ou mais leitura de dados.
• A infra estrutura do ambiente de produção todo é muito relevante, tanto para a aplicação como para o banco. Sempre ter o banco em uma máquina separada da aplicação é ótimo!