MYSQLÖÐͳ¼Æ²éѯ½á¹û×ÜÐÐÊýµÄ±ã½Ý·½·¨¡¾×ªÔØ¡¿
ת×Ô£ºhttp://ginew.blog.163.com/
½ñÌìÔĶÁ wordpress´úÂëʱ£¬·¢ÏÖÒ»¸öMYSQLµÄ¹Ø¼ü´Ê SQL_CALC_FOUND_ROWS ²é¿´ÊÖ²áºó·¢Ïִ˹ؼü´ÊµÄ×÷ÓÃÊÇÔÚ²éѯʱͳ¼ÆÂú×ã¹ýÂËÌõ¼þºóµÄ½á¹ûµÄ×ÜÊý£¨²»ÊÜ Limit µÄÏÞÖÆ£©
ÀýÈ磺 SELECT SQL_CALC_FOUND_ROWS tid from cdb_threads WHERE fid=14 LIMIT 1,10;
¼ÙÉèÂú×ãÌõ¼þµÄÓÐ1000Ìõ£¬ÕâÀï·µ»Ø10Ìõ¡£Á¢¼´Ê¹Óà SELECT found_rows() AS rowcount; Ôò·µ»ØµÄ rowcount Ϊ1000£»
ÕâÑù½ÚÊ¡ÁËSELECT count(*) AS rowcountµÄÖØ¸´²éѯ£¬¿ÉÒÔ½ÚÊ¡±È½Ï¿É¹ÛµÄʱ¼ä
===============================
ÒÔÏÂÊǸ÷ÅÔÚÔÚÓÎζÖеÄÓ¦Óãº
function mail_list_sent($uid, $start)
{
// ×¢ÒâSQL_CALC_FOUND_ROWS uidÖ®¼äûÓжººÅ
$query = "SELECT SQL_CALC_FOUND_ROWS uid, real_name, current_city, msg_uid, sender_flag, msg_title, msg_content from ".TT_DBTABLEPRE."mailbox as mb1, ".TT_DBTABLEPRE."user as usr1 WHERE mb1.sender_id=usr1.uid AND mb1.sender_id=$uid AND sender_flag > 0 LIMIT $start, ".TT_PAGESIZE;
$mails = $this->db->fetch_all($query);
//²éѯSELECTÖÐÂú×ãÌõ¼þµÄÐÐÊý£¬ÓëLIMIT×Ó¾äÎÞ¹Ø
$max_count = $this->db->fetch_first("SELECT found_rows() AS rowcount");
$tmp['state_code'] = 200;
$tmp['info'] = "OK";
$tmp['list'] = $mails;
$data = json_encode($tmp);
return $data;
}
¸Ðл×÷Õß·ÖÏí£¡
Ïà¹ØÎĵµ£º
×ܽáÁËһЩMySQLÓÅ»¯·½ÃæµÄ¼¼ÇÉ
Ò». Æô¶¯²ÎÊýÓÅ»¯
ÐÞ¸Ä my.cnf (»òÕßmy.ini)£¬¼ÓÈë/ÐÞ¸ÄÒÔϼ¸ÐÐ
#É趨»º´æµÄÁ¬½ÓÊý,½ÚÊ¡Á¬½ÓʱµÄ¿ªÏú
back_log = 64
#½ûÓÃÎļþϵͳÍâ²¿Ëø
external-locking = 0
#½ûÓÃBDB,Èç¹ûÄãȷʵ²»ÐèÒªµÄ»°,innodbÒ²ÊÇÈç´Ë
skip-bdb
#Ë÷Òý»º³å,Èç¹ûÊÇרÓõÄÊý¾Ý¿â·þÎñÆ÷,¿ÉÒÔÉèÖøߴï·þÎñÆ÷ÄÚ´ ......
¿´µ½²»ÉÙÓû§·´Ó³×ª»»ÍêÒÔºóÊÇÂÒÂëµÄÇé¿ö,³öÏÖÕâÖÖÏÖÏóµÄÖ÷ÒªÔÒòÊÇÕâÀàÓû§Ê¹ÓõͼÊÇmysql4.1ÒÔÉϵİ汾.ÏÂÃæ×÷Ò»¸ö˵Ã÷,Ï£Íû³öÏÖÕâ¸öÎÊÌâµÄÅóÓѶ¼ÄÜÄÍÐĵİÑÕâ¸öÎĵµ¿´Íê!!!
MySQL 4.1¿ªÊ¼£¬¶Ô¶àÓïÑÔµÄÖ§³ÖÓÐÁ˺ܴó±ä»¯ (Õâµ¼ÖÂÁËÎÊÌâµÄ³öÏÖ)¡£¾¡¹Ü´ó²¿·ÖµÄµØ·½ (°üÀ¨¸öÈËʹÓúÍÖ÷»úÌṩÉÌ)£¬MySQL 3¡¢4.0 ÈÔȻռÖ÷µ¼µØ ......
ÎÒÔÚmysql ÃüÁîÐÐÕâ¸öϵÁÐÀï½éÉܹýÓÐÌõ¼þ²éѯ£¬ËüÓõ½WHERE×Ӿ䡣µ«ÊÇÓÐʱºòÓÃWHERE×Ó¾äÎÞ·¨¸øÎÒÃǵIJéѯ¼ÓÈëÌõ¼þ£¬±ÈÈçÔÚÉÏÒ»½ÚÎÒÃÇѧϰµÄGROUP BY(°´×é¹éÀà)×Ӿ䣬Èç¹ûÎÒÃÇÒªÔÚ°´×é¹éÀàºó¼ÓÈëÌõ¼þ£¬ÓÃWHERE¾ÍÐв»Í¨£¬ÒòΪWHEREÖ»¿ÉÒÔÔÚ°´×é¹éÀàǰ¼ÓÈëÌõ¼þ¡£ºÃÔÚmysqlÌṩHAVING×Ӿ䣬Ëü¿ÉÒÔ°ïÎÒÃǽâ¾öÕâ¸öÎÊÌâ¡£
ÈçºÎÊ¹Ó ......
http://www.mysqlperformanceblog.com/2006/11/12/trying-archive-storage-engine/
£¨1£©ÔÚÏàͬµÄÌõ¼þÏ£¬archiveµÄÊý¾Ý´óСԶСÓÚmyisam
£¨2£©ÔÚѹËõarchiveÊý¾Ýʱ£¬bzip2ºÃÓÚgzip
£¨3£©ÐèÒªÌí¼ÓarchiveµÄ·½·¨ºÍѹËõµÄlevel£¬ÒÔÂú×㲻ͬÓû§µÄÐèÇó
£¨4£©Õâ¸ö´æ´¢ÒýÇæÊÊÓÃÓÚÈÕÖ¾Êý¾Ý
£¨5£©´ËÒýÇæ²»ÄÜupdate,delete£¬²¢ ......