StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPHP / SQL (MySQL) function optimization help required
text
Body
copied!<p>I'm currently trying to optimize a "bottleneck" function that is called really ofen in an application.</p> <p>In the application in question, options can be selected. But some options can be restricted by other. For example, when option "A" is selected, it restricts the selection of option "B".</p> <p>These restriction links are saved to a table which have this structure:</p> <pre><code>option_restrictions option_code_1 varchar(20) option_code_2 varchar(20) </code></pre> <p>Also some options are options packages. For exemple, option "Fruit" consist of the package of option "Apple", "Orange", "Kiwi".</p> <p>Those are defined this way in the table:</p> <pre><code>option_packages option_code varchar(20) option_included varchar(20) </code></pre> <p>Currently the "bottleneck" function do this:</p> <ol> <li>Check if 2 specified options restricts each other.</li> <li>If not, it checks if option 1 is an option package and if so, retreive all of it's options and check if any is restricted by option 2.</li> <li>If not, it checks if option 2 is an option package and if so, retreive all of it's options and check if any is restricted by option 1.</li> <li>If not it returns false (no restriction).</li> </ol> <p>Here is the actual code I'm trying to optimize:</p> <pre><code>//Returns true if options restricts each other, false otherwise function restriction($option_1, $option_2) { global $conn; //Pass 1 check is each option restrict themselves $sql_restriction = "select * from option_restrictions where (option_code_1 = '".$option_1."' or option_code_1 = '".$option_2."') and (option_code_2 = '".$option_1."' or option_code_2 = '".$option_2."')"; $res_restriction = mysql_query($sql_restriction, $conn); if (mysql_num_rows($res_restriction)>0) { log_action('End restriction(' . $option_1 . ', ' . $option_2 . ')'); return true; } else { //Pass 2 check if option 1 is a package and if so test it's options against option 2 $sql_option_1_composante = "select * from option_packages where option_code = '".$option_1."'"; $res_option_1_composante = mysql_query($sql_option_1_composante, $conn); if (mysql_num_rows($res_option_1_composante)>0) { while ($option_1_composante = mysql_fetch_array($res_option_1_composante)) { if (restriction($option_1_composante["option_included"], $option_2)) { return true; } } } //Pass 3 check if option 2 is a package and if so test it's options against option 1 $sql_option_2_composante = "select * from option_packages where option_code = '".$option_2."'"; $res_option_2_composante = mysql_query($sql_option_2_composante, $conn); if (mysql_num_rows($res_option_2_composante)>0) { while ($option_2_composante = mysql_fetch_array($res_option_2_composante)) { if (restriction($option_2_composante["option_included"], $option_1)) { return true; } } } } return false; } </code></pre> <p>The recursive calls/loops are killing the system ATM... For a single page load this function can be called over 15,000 times which lead to page load of 30secs/4mins!</p> <p><strong>I'm trying to optimize as much as I can. I've tryed to merge pass 2 and 3 together and to remove the recursive call... Any ideas welcome!</strong></p> <p>FYI, I know this is maybe not the best design but this function is part of a leviathan system I have no control over nor the ressources/time to change ATM.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload