mine is too big, what about yours? (database, of course) HELP

huge database queries

[liquidSOUL]

hello everyone:

i'm new in webmaster world and just registered.

What makes me come here is a huge database with 370000 rows and more than 20 columns of information most of it char.

I'm no experience with php and am strugling to get this site to work.

Main problem is the query time.

The query is quite straight forward but it takes too long.

I leave here my main search script and i hope someone could suggest any changes to speed up my search or any other

here it goes:

<form method="get" action="<?php echo $PHP_SELF?>">
<input name="busca" type="text" value="<?php echo $_GET["busca"]?>" size="75">
<input type="Submit" name="Submit" value="pesquisa ltr">
</form>

function pageBrowser($rows,$numLimit,$amm,$queryStr,$numBegin,$begin,$num) {

< removed massive code - $sqlprod is the LIMIT portion of the query -- jatar_k >

return array($sqlprod,$wheToWhe,$wholePiece);
}

$search=$_GET["busca"];

if (!$_GET["busca"]) {
echo "Nenhum nome foi introduzido<br>Por Favor introduza um nome";
} else {
$buscax = ereg_replace(" ", "%", $busca);

if (isset($_GET[total])) {
$rows = $_GET[total];
//if not, query the DB for the rows amount
} else {
$sql = "SELECT COUNT(main.nome) AS total
FROM main WHERE nome
LIKE '% $buscax %'
OR nome LIKE '$buscax %'
OR nome LIKE '% $buscax'
OR nome LIKE '% $buscax\,%'";

$arr = mysql_fetch_array(mysql_query($sql));
$rows = $arr[total];
}

$buscaxx = ereg_replace(" ", "+", $busca);

pageBrowser();
$append = "&total=".$rows."&busca=".$buscaxx;
$nav = pageBrowser($rows,10,10,$append,$_GET[numBegin],$_GET[begin],$_GET[num]);

if ($rows<11) {
$sql = "SELECT id, nome, activ, local, telf
FROM main
WHERE nome
LIKE '% $buscax %'
OR nome LIKE '$buscax %'
OR nome LIKE '% $buscax'
OR nome LIKE '% $buscax\,%'".$nav[0];
} else {
$sql = "SELECT id, nome, activ, local, telf
FROM main
WHERE nome
LIKE '% $buscax %'
OR nome LIKE '$buscax %'
OR nome LIKE '% $buscax'
OR nome LIKE '% $buscax\,%'
ORDER BY nome".$nav[0];
}

$rs = mysql_query($sql);

browsed. Eg. listing 1-10 of 100 results
echo "<font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"1\">\n";
echo "<p>Resultados ".$nav[1]." de ".$rows.".</p>";
function
echo "<p>".$nav[2]."</p>";

<removed display code - jatar_k>

PLEASE HELP ME IMPROVE.

[1][[b]edited by[/b]: jatar_k at 7:42 pm (utc) on May 16, 2005][/1]
[1][edit reason] edited code dump [/edit][/1]

[jatar_k]

Welcome to WebmasterWorld liquidSOUL,

something I don't understand, in this chunk, why does it call pageBrowser twice?

pageBrowser();
$append = "&total=".$rows."&busca=".$buscaxx;
$nav = pageBrowser($rows,10,10,$append,$_GET[numBegin],$_GET[begin],$_GET[num]);

another thing is that when you have this
WHERE nome
LIKE '% $buscax %'
OR nome LIKE '$buscax %'
OR nome LIKE '% $buscax'
OR nome LIKE '% $buscax\,%'".$nav[0];

4 seperate LIKEs in your where clause, that's going to be slow, not much you can do about that.

I also wondered about the flow, from what I can tell

1. select count() with 4 LIKEs
2. go off to pageBrowser function twice
3. come back and do another select with 4 LIKEs
4. loop through results (they seem to be properly LIMITed)

have you looked at the different sections, maybe timed how long they take to execute to see where the real slow down is?

[mcibor]

Also try to do that query in plain mysql just to see how much one query is done.

Wouldn't it be better to use mysql limiting?

SELECT * FROM table LIMIT 10, 20; (10 is the offset, from which row to start showing and 20 is how many rows to show).

Also you can do just one normal query and use mysql_num_rows function to get the number of rows (you won't have to do the query with count, as you still have to do the latter one).

Hope this helps you
However next time please write more relevant data and try to write it in English.

Best regards
Michal Cibor