Hello,

I need a MySQL Guru to take the code provided below and turn it into one query to the server.

The existing code is:


$result = mysql_query("SELECT id FROM table1, table2, table3 WHERE table2.accountid=table1.accountid AND table1.id=table3.id AND table2.planid > 1",$mysqldb);
while ($myrow = mysql_fetch_array($result)) {
$arrayofids[] = $myrow[id];
}

foreach ($arrayofids AS $ids) {
$notincludedids .= " AND table4.id!='$ids'";
}

$myq = "SELECT field1, table4.id FROM (table4, table5) LEFT JOIN table6 ON table4.id=table6.id AND table6.cron_date=NOW() WHERE table4.id=table5.id AND table5.cycledate='" . date("d") . "' AND table6.id IS NULL" . $notincludedids;


As you can see it is very complicated (at least to me).

The goal is to remove all of the PHP processing and make one query that does all of this. I know it is possible, but so far I have been unable to get it working.

I will pay $50 via PayPal (Only PP) to whoever is able to post a working version. I will also pay $25 via PayPal (Only PP) to whoever is able to take the query that is provided by the first person and optimize it even better. To get the $25 the optimization must return results faster than any of the others that people submit.

To make it easier I can give you the exact code that is being used via PM. The tables and fields have been changed for security reasons, however you should still be able to get it with that.

In short, the code above does one query and gets an array of ids, then that array is used in the second query to exclude each id from the results.

The only part that should remain PHP is the '" . date("d") . "' section.

If you have any questions, or a solution feel free to post it.

Whoever provides the best optimization may also get additional work. I have dozens of queries that probably could be optimized.

Hello Joel,

I have sent you a private message. I am ready to start now.

Thanks!

Einnar

A solution has been found. Thanks goes to fusionrays for the solution.

I will post the solution soon for anyone that wants to optimize it even better and be eligible for the $25.

Just to reiterate, the $50 is no longer available, but the $25 still is.

If anyone is still interested in the $25 PM me for the complete query and database schema to work with.

The $25 is still available.