Mysql result times over 15 seconds!

[Icoste]

Hi, I am having problems with result times. I run a search from my website and it takes around 15 seconds to generate the mysql query and then 40 seconds to load the page!

Does anyone have any ideas on this because it is racking my brains!

If you would like to checkl the result times please go to

http://www.icoste.com

and use the top search bar.

Thanks in advance.

 

[guitardave78]

What is your query and how many records are in the table?

}...the bane of my life!

http://www.yetanotherreviewsite.co.uk

http://www.fuzzyd.co.uk

 

[Icoste]

hi, there are around 800,000 products in the table.

I have copied this from the php file for my header, i think this is what you reuqire, let me know if it isnt, sorry i am alittle new to mysql.

Search:&nbsp;</div>
</td>
<td width="20%"><input name="searchText" value="<?=$_GET["searchText"]?>" type="text" class="effect" size="35"></td>
<td width="5%" class="bold" align="center">&nbsp;In&nbsp;</td>
<td width="10%">
<select name="catId" onkeypress="" class="effect">
<option value="">All Products&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</option>
<?
$resCat = executeQuery("SELECT ID, TITLE FROM ".PREFIX."CATEGORY WHERE PARENT_ID = 0 AND ISVALID='Y' ORDER BY SORT_ORDER, TITLE");
while ( $rowCat = getRow($resCat) )
{
?> <option value="<?=$rowCat["ID"]?>"><?=$rowCat["TITLE"]?></option>
<? }
free($resCat);
?>
</select>
</td>

http://www.icoste.com

- UK Shopping

 

[feherke]

Hi

No, that PHP code is useless and confusing for us.

Do you have index on the [tt]order by[/tt] fields ? If not, create it.

Are you filling that HTML [tt]select[/tt] control with around of 800000 [tt]option[/tt]s ? I would rethink that interface as that is not user friendly.

Feherke.

http://rootshell.be/~feherke/

 

[Kozusnik]

If you are trying to pull all records, you may want to break it up alphabetically, or by 100 records with next an prev. Or at least throw a search in for the user.

Mark