query takes forever to process...

[appel]

Hi all,

I've been struggling with this one for two days straight now and the pile of hair on my desk keeps getting bigger and bigger...

The script task is to display a number of records from the db. Through a form the user can apply some filters which the script uses to dynamicly construct a SQL-query.

The problem is that the query below takes forever to process and my script keeps timing out. Also Taskmanager tells me mysqld-nt.exe consumes 99% of my cpu so that can't be right, can it?

A friend of mine told me the query might multiply the 3 tables, so that would explain the time it's taking. However, i can't think of a better way to pull this off.

Below is the part of the script with the 'dynamic construct' and query. If you need to see more, please let me know.

<?php

// construct condition
$cnr = 0;
if($vpid) {
$condition	.= "WHERE e.pid = '$vpid'";
$cnr++;}
if($g) {
	if($cnr > 0) {
	$condition .= " AND ";
	} else { 
	$condition .= "WHERE ";
	}
$condition	.= "g.geslacht = '$g'";
$cnr++;}
if($l) {
	if($cnr > 0) {
	$condition .= " AND ";
	} else { 
	$condition .= "WHERE ";
	}
$condition	.= "g.leeftijd = '$l'";
$cnr++;
}

$filter = $condition."AND e.cid = g.id AND e.pid = p.id";

// construct ORDER clause
if($order == "p") {
$orderby = "ORDER BY p.naam ASC";
} elseif($order == "e") {
$orderby = "ORDER BY g.email ASC";
} elseif($order == "l") {
$orderby = "ORDER BY g.leeftijd ASC";
} elseif($order == "g") {
$orderby = "ORDER BY g.geslacht ASC";
} elseif($order == "c") {
$orderby = "ORDER BY e.v1 ASC";
} elseif($order == "w") {
$orderby = "ORDER BY e.v2 ASC";
} elseif($order == "b") {
$orderby = "ORDER BY e.v3 ASC";
}

$query = "
SELECT DISTINCT p.naam AS productnaam, g.email, g.geslacht, g.leeftijd, e.id, e.v1, e.v2, e.v3, e.v4
FROM $tb[enqu] e, $tb[gebr] g, $tb[prod] p
INNER JOIN $tb[gebr] ON (e.cid = g.id)
INNER JOIN $tb[prod] ON (e.pid = p.id)
".$condition."
".$orderby."
" or die (mysql_error());

?>

I must be doing something wrong, but what is it?! Any help would be greatly appreciated!

Thanks in advance!

[Anon]

You are joining excessively.

FROM table1, table2, table3, ... is shorthand for a join.

Change to this:

FROM $tb[enqu] e
INNER JOIN $tb[gebr] g ON (e.cid = g.id)
INNER JOIN $tb[prod] p ON (e.pid = p.id)

[appel]

Thanks for your reply c!
Learned something new again 😃

Anyway, I tried your suggestion, but it didn't fly... The script's still taking too much time to process and times out. It MUST be in the query, because when i simplify it, it does work (although it's no Hakkinen).

Do you have any other suggestion?

I appreciate your effort,

appel

[Anon]

Can you post an explain of your SQL. This will show what kind of indexing MySq is using. If you haven't used explain, it's very easy.

Syntax: e.g.

explain select a.field1, b.field1 from a, b where b.field2 = a.field2

should result in something like:

+-------+--------+---------------+-------------+---------+-----------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+---------------+-------------+---------+-----------+------+------------+
| a | ref | when_played | when_played | 1 | const | 409 | |
| b | eq_ref | PRIMARY | PRIMARY | 2 | a.oppo_id | 1 | where used |
+-------+--------+---------------+-------------+---------+-----------+------+------------+

[appel]

I think i found the right syntax for my query. Somehow i managed to speed it up, now it works like a charm.

I guess i ought to go to the nearest bookstore and by me a nice mysql book....

Thanks all!