RE: [PHP-DB] Optimize following Mysql-Query

[Vaughn, Paul]

Title: RE: [PHP-DB] Optimize following Mysql-Query





Another large performance hit is the use of "like" with the wildcards.  If you can avoid wildcard searches do.
WHERE Personen.NAME IN ('$name')"; or "= $name"  Obviously this is only valid if the variable is going to match what is in the data exactly.

- Paul


-Original Message-
From: Andr Sannerholt [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 30, 2003 6:49 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Optimize following Mysql-Query



Hi Everyone,


I have the following Mysql-Query that returns correct information, but it
just needs to much time (3-4 Minutes!!) Can you help me to optimize it?


$query=
"SELECT * FROM Firmennamen INNER JOIN Master ON Firmennamen.ID=Master.FN
WHERE Master.FN IN (SELECT Master.FN FROM Master INNER JOIN Personen ON
Master.NN=Personen.ID WHERE Personen.NAME LIKE '%$name%')";
$result  =  mysql_query($abfrage2) or die("Anfrage results fehlgeschlagen");


Regards


Andre Sannerholt




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Optimize following Mysql-Query

[Paul Burney]

on 05/30/2003 7:49 AM, André Sannerholt at [EMAIL PROTECTED] appended
the following bits to my mbox:

> I have the following Mysql-Query that returns correct information, but it
> just needs to much time (3-4 Minutes!!) Can you help me to optimize it?

Since subselects are fairly new to MySQL and one of the big reasons the
developers didn't want to implement them was the performance hit, I'd bet
that is your problem.

Looking at the query, you might be able to join the Personen table directly
so you can use its fields in the WHERE clause.  Something like:

$query= "SELECT * FROM Firmennamen INNER JOIN Master ON
Firmennamen.ID=Master.FN INNER JOIN Personen ON Master.NN=Personen.ID WHERE
Personen.NAME LIKE '%$name%'";

Also make sure that you are indexing the fields that you are performing the
joins on.

HTH.

Sincerely,

Paul Burney






--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php