# mysql TABLE optimisation

**URL:** <https://forums.percona.com/t/mysql-table-optimisation/537>\
**Category:** Other MySQL® Questions\
**Created:** [November 22, 2007, 9:50am UTC](https://forums.percona.com/t/mysql-table-optimisation/537 "2007-11-22T09:50:45Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![rohanpinto](https://avatars.discourse-cdn.com/v4/letter/r/7ba0ec/32.png) [@rohanpinto](https://forums.percona.com/u/rohanpinto)\
**Post date:** [November 22, 2007, 9:50am UTC](https://forums.percona.com/t/mysql-table-optimisation/537/1 "2007-11-22T09:50:45Z")

</div>

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(cool: NOT NULL default ‘0’,  
`fav_num` int(cool: 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(cool: 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)

---

<div class="post-metadata">

**Author:** ![rohanpinto](https://avatars.discourse-cdn.com/v4/letter/r/7ba0ec/32.png) [@rohanpinto](https://forums.percona.com/u/rohanpinto)\
**Post date:** [November 22, 2007, 10:01am UTC](https://forums.percona.com/t/mysql-table-optimisation/537/2 "2007-11-22T10:01:15Z")

</div>

logging slow queries on this DB.. here’s what I got:  
(a few examples)

SELECT keyword from content where adult\<\>“yes” and type=‘public’ order by rand() ASC LIMIT 0, 25;

# Time: 071122 4:18:34

# [User&#64;Host](mailto:User&#64;Host): username[username] @ localhost

# Query\_time: 2 Lock\_time: 0 Rows\_sent: 0 Rows\_examined: 79270

SELECT \* from content where type=‘public’ and (keyword like ‘%harbhajan%’ or keyword like '%mann%'or keyword like '%man%'or keyword like '%jatt%'or keyword like '%sam%'or keyword like '%latest%'or keyword like '%punjabi%'or keyword like '%song%'or keyword like '%mitti%'or keyword like '%wajan%'or keyword like '%mardi%'or keyword like '%wajde%'or keyword like '%dhol%'or keyword like '%naal%'or keyword like '%algose%'or keyword like '%tumbi%'or keyword like '%upload%'or keyword like '%miti%'or keyword like '%vaja%'or keyword like ‘%vajan%’) and adult\<\>“yes” order by VID asc limit 14619, 5;

# Time: 071122 4:20:03

# [User&#64;Host](mailto:User&#64;Host): username[username] @ localhost

# Query\_time: 2 Lock\_time: 0 Rows\_sent: 10 Rows\_examined: 79685

---

<div class="post-metadata">

**Author:** ![rohanpinto](https://avatars.discourse-cdn.com/v4/letter/r/7ba0ec/32.png) [@rohanpinto](https://forums.percona.com/u/rohanpinto)\
**Post date:** [November 22, 2007, 10:32am UTC](https://forums.percona.com/t/mysql-table-optimisation/537/3 "2007-11-22T10:32:02Z")

</div>

i modified my table and added:

KEY `INDEX` (`adult`,`featured`,`type`,`addtime`)

still no use…

here’s my PHP select statements:  
$sql1=“SELECT \* 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); —\> i use smarty for the presentation…

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [November 22, 2007, 10:40am UTC](https://forums.percona.com/t/mysql-table-optimisation/537/4 "2007-11-22T10:40:12Z")

</div>

And your last query didn’t gain speed?

As for the two others.  
1.  
Try not to use negations like adult \<\> ‘yes’.  
Change them to adult = ‘no’ instead.  
Index usage is much improved.

1. 

An “… ORDER BY RAND()” can not use an index. Hence it will always require CPU to order the records. But note that the amount of records that needs sorting is determined from how many that match the WHERE and/or that are left after a GROUP BY statement since sorting is one of the last steps in a query execution.  
The only thing you can do to speed this query up is to make sure that you have a sort\_buffer\_size setting that is large enough to store the temporary table in RAM instead of having to write it to disk.

---

<div class="post-metadata">

**Author:** ![rohanpinto](https://avatars.discourse-cdn.com/v4/letter/r/7ba0ec/32.png) [@rohanpinto](https://forums.percona.com/u/rohanpinto)\
**Post date:** [November 22, 2007, 10:55am UTC](https://forums.percona.com/t/mysql-table-optimisation/537/5 "2007-11-22T10:55:14Z")

</div>

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="[URL]http://img.youtube.com/vi/[/URL]".$flvdoname."/".$img.".jpg"; elseif($provider=="myspace") $thumburl="[URL]http://myspaceurl.com/vi/[/URL]".$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;$iexecute($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}

- {$channels[i].name} 
{/section}

{section name=i loop=12 start=0 max=12}  
{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}” /\>  
{$featuredcontent[i].title|truncate:17: “…”:true}

{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}[{$channel[k].name}](https://forums.percona.com/channel/%7B%24channel%5Bk%5D.CHID%7D){/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…

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [November 22, 2007, 3:49pm UTC](https://forums.percona.com/t/mysql-table-optimisation/537/6 "2007-11-22T15:49:13Z")

</div>

Please don’t post a lot of PHP code because it really doesn’t say anything.

Do as you did posting the CREATE TABLE and then post the specific query that takes time and the explain plan for it.

As for your CPU usage it is 7.5% not 29%. If you look at the top of the “top” output it says

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

which means that 7.5% is used and 92.5% is idle.

The reason why mysqld is reporting 29% in the list is that the mysql process is in fact a _lot_ of threads and a rounding error for each thread makes the cpu usage to rise. But that is purely a display problem.  
The summarized cpu usage for the system is what you should look at and 7.5% isn’t that high.

---

<div class="post-metadata">

**Author:** ![mikec](https://avatars.discourse-cdn.com/v4/letter/m/977dab/32.png) [@mikec](https://forums.percona.com/u/mikec)\
**Post date:** [November 22, 2007, 8:00pm UTC](https://forums.percona.com/t/mysql-table-optimisation/537/7 "2007-11-22T20:00:52Z")

</div>

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.