1、对于分页查询，事先进行count查询总的记录数，如果count结果小于分页数，将limit设置为$num，避免全表扫描
$num = mysql_query("select count(*) from table where ...", $conn); $limit = 10; if ($num < $limit) { mysql_query("select * from table limit 0,$num"); } else { mysql_query("select * from table limit 0,$limit"); }2、避免在for循环里操作数据库
3、一开始页面不需要展示的元素（或折叠起来的元素），可以在页面完成后再通过ajax刷新到页面上
4、优化count查询同一列不同值的数量
select count(color = 'blue' or null) as blue,count(color = 'red' or null) as red from table; select sum(color = 'blue') as blue,sum(color = 'red') as red from table;5、limit查询
//优化limit，通过“延迟关联”，扫描尽量少的字段 select id,title,desc,field_N from table limit 1000,5; //改为 select id,title,desc,field_N from table inner join ( select id from table limit 1000,5 ) as temp using(id); select * from table limit 20; 假设得到id为500; 则下一页： select * from table where id > 500 limit 20;使用explain结果中的rows列的值作为结果集总数的近似值
6、UNION查询
除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。
7、使用用户自定义变量
//简单示例： set @now = 0; update t1 set lastUpdated = NOW() where id = 1 and @now = NOW(); select $now; //最常见问题：没有注意到赋值和读取变量的时候可能是在查询的不同阶段 set @rownum = 0; select actor_id,@rownum = @rownum + 1 as cnt from sakila.actor where @rownum <= 1; actor_id cnt 1 1 2 2 //解决：让变量的赋值和取值发生在执行查询的同一阶段 set @rownum = 0; select actor_id,@rownum as rownum from sakila.actor where (@rownum = @rownum + 1) <= 1; actor_id rownum 1 1 //“偷懒”的UNION：用户表(users)、不活跃用户表(users_archived) select id from users where id = 123 union all select id from users_archived where id = 123; 改为 select greatest(@found = -1, id) as id, 'users' as which_tbl from users where id = 1 union all select id, 'users_archived' from users_archived where id = 1 and @found is null union all select 1, 'reset' from dual where (@found = null) is not null;
