사이트 내 전체검색
그누보드
검색목록
[팁] mysql 문자열에 대해 like,INSTR, 범위, between 중에 제일 빠른것은?
로빈
https://cmd.kr/gnu/716 URL이 복사되었습니다.

본문

<?php
include_once('./_common.php');

echo "<xmp>";
$limit=100;
echo "총 테이블수 79 / 총 레코드 :59,488  반복수 $limit \n";

$arr_time=array();
for ($i=0;$i<=4;$i++) {
$start = get_time();
//echo microtime(),"\n";
for ($ii=0;$ii<$limit;$ii++) {
$sql="select  * from {$g5['board_table']} where ";
$sql.="gr_id!='adm' ";
//$sql.="and bo_table not like '_%' ";

if ($g5['show']) echo "$sql\n";
$rst=sql_query($sql,true);
$count=0;
$count_total=0;
while ($board=sql_fetch_array($rst)) {
$bo_table=$board['bo_table'];
if (substr($bo_table,0,1)=='_') continue;
//if ($bo_table!='autocamping') continue;
//if ($bo_table!='hotucc') continue;
$write_table = $g5['write_prefix'] . $bo_table; // 게시판 테이블 전체이름

$tmp1 =sql_fetch("select count(*) as cnt from $write_table");

$sql2 ="select count(*) as cnt from $write_table where ";
if ($i==0) $sql2.="wr_content like '2017%'";
if ($i==1) $sql2.="INSTR(wr_content, '2017')";
if ($i==2) $sql2.="substring(wr_content,1,4)='2017'";
if ($i==3) $sql2.="wr_content > '2017-00-00 00:00:00' and wr_content < '2018-00-00 00:00:00' ";
if ($i==4) $sql2.="wr_content between '2017-00-00 00:00:00' and '2018-00-00 00:00:00' ";
$tmp2=sql_fetch($sql2,true);
$count++;
$count_total+=$tmp1['cnt'];
//if ($i==1) echo "$bo_table -> {$tmp2['cnt']}\n";

}
}
$end = get_time();
//echo microtime(),"\n";
$time=$end - $start;


echo '소요시간: '.number_format($time,2)."초  방식: ";

if ($i==0) echo "where wr_content like '2017%'";
if ($i==1) echo "where INSTR(wr_content, '2017') ";
if ($i==2) echo "where substring(wr_content,1,4)='2017'";
if ($i==3) echo "where wr_content > '2017-00-00 00:00:00' and wr_content < '2018-00-00 00:00:00' ";
if ($i==4) echo "where wr_content between '2017-00-00 00:00:00' and '2018-00-00 00:00:00' ";
echo "\n";

}


function get_time() {
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

첨부파일

댓글목록

등록된 댓글이 없습니다.

그누보드
65 (1/2P)

Search

Copyright © Cmd 명령어 18.219.207.11