CODEWITHSUNDEEP
sqlmysqloptimizationquery-optimization
Activist
Activist

Reputation: 196

PHP / SQL (MySQL) function optimization help required

I'm currently trying to optimize a "bottleneck" function that is called really ofen in an application.

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".

These restriction links are saved to a table which have this structure:

option_restrictions
    option_code_1    varchar(20)
    option_code_2    varchar(20)

Also some options are options packages. For exemple, option "Fruit" consist of the package of option "Apple", "Orange", "Kiwi".

Those are defined this way in the table:

option_packages
    option_code        varchar(20)
    option_included    varchar(20)

Currently the "bottleneck" function do this:

  1. Check if 2 specified options restricts each other.
  2. 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.
  3. 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.
  4. If not it returns false (no restriction).

Here is the actual code I'm trying to optimize:

//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;
}

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!

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!

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.

Upvotes: 1

Views: 241

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

I can't tell for sure without more detail, but to me this really sounds like a database design problem.

If it really is a database design problem,

  • you're probably not going to get very far optimizing the application code
  • but changing the structure could drop the load time dramatically, maybe even a couple of orders of magnitude

This is the kind of problem (legacy system, not allowed to change it) that I've successfully attacked with a proof-of-concept test. I set up a server on my own computer. (Oracle XE, SQL Server Express, and PostgreSQL are all free nowadays.) Then I'd build tables and views that did all the grunt work.

In your case, if you're not a database designer, find one that's willing to get involved in a skunk works project.

Good luck. I mean that.

Upvotes: 1

Related Questions