Home » Performance » MySQL » mysql TABLE optimisation
| mysql TABLE optimisation [message #2241] |
Thu, 22 November 2007 10:20  |
 rohanpinto
Messages: 5
Registered: November 2007 |
Junior Member |
|
|
Hi All (please help)
my mysql DB uas a table of the following structure:
CREATE TABLE IF NOT EXISTS `content` (
`VID` bigint(20) NOT NULL auto_increment,
`UID` bigint(20) NOT NULL default '0',
`provider` char(2) default NULL,
`title` varchar(120) NOT NULL default '',
`description` text NOT NULL,
`featuredesc` text NOT NULL,
`keyword` text NOT NULL,
`channel` varchar(255) NOT NULL default '0|',
`vdoname` varchar(40) NOT NULL default '',
`flvdoname` varchar(40) default NULL,
`adult` char(3) default 'no',
`duration` float NOT NULL default '0',
`space` bigint(20) NOT NULL default '0',
`type` varchar(7) NOT NULL default '',
`addtime` varchar(20) default NULL,
`adddate` date NOT NULL default '0000-00-00',
`record_date` date NOT NULL default '0000-00-00',
`location` text NOT NULL,
`country` varchar(120) NOT NULL default '',
`vkey` varchar(20) NOT NULL default '',
`viewnumber` bigint(10) NOT NULL default '0',
`viewtime` datetime NOT NULL default '0000-00-00 00:00:00',
`com_num` int( NOT NULL default '0',
`fav_num` int( NOT NULL default '0',
`featured` char(3) NOT NULL default 'no',
`ratedby` bigint(10) NOT NULL default '0',
`rate` float NOT NULL default '0',
`filehome` varchar(120) NOT NULL default '',
`be_comment` char(3) NOT NULL default 'yes',
`be_rated` char(3) NOT NULL default 'yes',
`embed` varchar( NOT NULL default 'enabled',
`voter_id` varchar(200) NOT NULL default '',
`contentowner` varchar(120) default NULL,
`adurl` varchar(255) default NULL,
`admsg` text,
`AnonName` varchar(255) default 'anonymous',
PRIMARY KEY (`VID`),
UNIQUE KEY `vkey` (`vkey`),
KEY `VID` (`VID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=79890 ;
when i search this table using PHP using simple queries, my CPU utilisation grows exponentially. just 1 search query on this table causes my CPU to spike to 30%... and when i have multiple queries the CPU utilisation grows to 300%..
could somone help me optimise this table please...
(the number of rows in this table is > 100K)
|
|
| | | |
| Re: mysql TABLE optimisation [message #2245 is a reply to message #2244 ] |
Thu, 22 November 2007 11:25  |
rohanpinto
Messages: 5
Registered: November 2007 |
Junior Member |
|
|
didnt help... here's what I have now:
1 PHP page:
content as follows:
<?php
include("include/config.php");
include("include/function.php");
$mydate= date('Y-m-d');
$sql="SELECT CHID, name from channel";
$rs = $conn->Execute($sql);
$channels = $rs->getrows();
STemplate::assign('channels',$channels);
if($_SESSION['filterstatus']=="on"){
$sql1="SELECT * from content where type='public' and (adult<>'yes' or adult is NULL) and featured=\"yes\" order by addtime desc limit 12";
} else {
$sql1="SELECT VID, title, from content where type='public' and adult='yes' and featured='yes' order by addtime desc limit 12";
}
$rs1=$conn->Execute($sql1);
if($rs1->recordcount()>0)
$featuredcontent = $rs1->getrows();
STemplate::assign('featuredcontent',$featuredcontent);
STemplate::assign('page', 'index');
STemplate::display('head.tpl');
STemplate::display('err_msg.tpl');
STemplate::display('index.tpl');
STemplate::display('footer.tpl');
?>
here's my function.php
<?
function insert_id_to_thumb($a)
{
global $config,$conn;
$VID=$a[vid];
$provider=$a[provider];
$flvdoname=$a[flvdoname];
$img=$a[img];
if($img=="") $img=rand(1, 3);
if($provider=="yt") $thumburl="http://img.youtube.com/vi/".$flvdoname."/".$img.".jpg";
elseif($provider=="myspace") $thumburl="http://myspaceurl.com/vi/".$flvdoname."/".$img.".jpg";
else $thumburl="/thumb/".$img."_".$VID.".jpg";
return $thumburl;
}
function insert_video_channel($a)
{
global $conn;
if($a[tbl]=="")$sqlx="channel from content where VID='$a[vid]'";
else $sqlx="channel from $a[tbl] where GID='$a[gid]'";
$sql="select $sqlx";
$rs=$conn->execute($sql);
$a=$rs->fields[channel];
if($a!="")
{
$temp=explode("|",$a);
if(count($temp)>=1) for($i=1;$i<count($temp);$i++) $list.=" or CHID=".$temp[$i];
$sql="select CHID,name from channel where CHID=$temp[0] $list";
$rsx=$conn->execute($sql);
$res=$rsx->getrows();
return $res;
}
}
function insert_comment_count($a)
{
global $conn;
$sql="select count(*) as ttl from comments where VID='$a[vid]'";
$rs=$conn->execute($sql);
return $rs->fields[ttl];
}
?>
and here's my index.tpl (smarty template)
{section name=i loop=$channels}
<li><a href="{$baseurl}/channel/{$channels[i].CHID}/page/1">{$channels[i].name} </a></li>
{/section}
{section name=i loop=12 start=0 max=12}
<a href="{$baseurl}/view/{$featuredcontent[i].vkey}">{insert name=id_to_thumb assign=thumburl vid=$featuredcontent[i].VID provider=$featuredcontent[i].provider flvdoname=$featuredcontent[i].flvdoname}<img src="{$thumburl}" width="120" height="90" border="0" alt="{$featuredcontent[i].title|escape:"quotes"|escape:"htmlall "|strip}" title="{$featuredcontent[i].title|escape:"quotes"|escape: "htmlall"|strip}" /></a>
<a href="{$baseurl}/view/{$featuredcontent[i].vkey}" style="color:#F1670B;">{$featuredcontent[i].title|truncate:17: "...":true}</a>
{insert name=comment_count assign=commentcount vid=$featuredcontent[i].VID}Comments: {$commentcount}
{insert name=video_channel assign=channel vid=$featuredcontent[i].VID}{section name=k loop=$channel start=0 max=1}<a href="/channel/{$channel[k].CHID}">{$channel[k].name}</a>{/section}
{/section}
with this simple query: hre's what TOP shows me when i hit refresh on my page... just once:
top - 11:27:48 up 7 days, 18:53, 1 user, load average: 0.00, 0.00, 0.00
Tasks: 119 total, 2 running, 117 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.1%us, 4.4%sy, 0.0%ni, 92.5%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 32553000k total, 32021216k used, 531784k free, 1162548k buffers
Swap: 2031608k total, 144k used, 2031464k free, 1640916k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
28452 mysql 18 0 184m 28m 3600 S 29 0.1 0:04.56 mysqld
28504 apache 15 0 195m 12m 2992 S 2 0.0 0:00.06 httpd
28498 apache 16 0 195m 11m 2580 S 1 0.0 0:00.03 httpd
1 root 15 0 10312 684 572 S 0 0.0 0:01.65 init
2 root RT 0 0 0 0 S 0 0.0 0:00.39 migration/0
could someone help me structure my table right and enable me to display this 1 page without much CPU consumtion.. I can run with that.. but this 1 query is killing me...
|
|
| | |
| Re: mysql TABLE optimisation [message #2257 is a reply to message #2241 ] |
Thu, 22 November 2007 20:30 |
mikec
Messages: 22
Registered: September 2007 |
Junior Member |
|
|
One comment on the table design...
Recommend splitting the table up so that there is a main
table with a few commonly accessed fields and a child (dependent)
table that has all the less commonly accessed fields. The goal
is to slim down the number of columns in the main table.
The row size is sooo big for this table that it will be
slow to search as not many rows can be packed into a 16k
block with a large row like this. This means that it will
take a lot of disk accesses to search this table. Making
it skinnier will improve search speed.
You would want to put any fields that are searched for in
the main table, or any fields that are always accessed in
the main table.
|
|
|
Goto Forum:
Current Time: Fri Jul 10 03:47:07 EDT 2009
Total time taken to generate the page: 0.00547 seconds |
Members
Search
Help
Register
Login
Home
