PHP Script Optimization - MySql Search and Sort

I have developed a fairly simple script to search a database and then sort the results based on the search terms used, so trying to get most relevant first.

Now this ran fine on my local machine and before I put in the sorting ran okay on the web server I have hired but once the sorting went in search times have greatly increased on the webserver.

What I'm posting below I have optimized as much as I know how, so I'm looking for some help in a better sort algorithm and maybe even a better way of querying the database anything to help speed up sort times!

Now some information about what I'm working with I needed to allow searches of 3 letters or more for example cat or car and I couldn't change the natural search word length limit for the mysql server so i can't use natural language searching of mysql hence why I am doing the queries I currently have.

Also an average search can easily return anywhere between 100-15000 results with the databases holding around 20000 entries

Any help will be greatly appreciated

<?php

require_once 'config.php';

$bRingtone = true;

$aSearchStrings = $_POST["searchStrings"];

$cConnection = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if (mysqli_connect_errno())
{
    exit();
}

$sTables = array("natural", "artificial", "musical", "created");

$aQueries = array();

foreach ($sTables as $sTable)
{
    $sQuery = "SELECT filename, downloadPath, description, imageFilePath, keywords FROM `$sTable` WHERE";

    $sParamTypes = "";
    $aParams = array();

    $iCount = 0;

    foreach ($aSearchStrings as $sString)
    {
            $sParamTypes .= "ss";

            $aParams[] = "%,$sString%";
            $aParams[] = "$sString%";

            $sQuery .= $iCount++ == 0 ? " (keywords LIKE ? OR keywords LIKE ?)" : " AND (keywords LIKE ? OR keywords LIKE ?)";
    }

    array_unshift($aParams, $sParamTypes);

    $aQueries[$sQuery] = $aParams;
}

$aResults = array();

foreach ($aQueries as $sQuery => $aParams)
{
    if ($cStmt = $cConnection->prepare($sQuery))
    {
            $aQueryResults = array();

            call_user_func_array(array($cStmt, 'bind_param'), $aParams);

            $cStmt->execute();

            $cStmt->bind_result($sFileName, $sDownloadPath, $sDescription, $sImageFilePath, $sKeywords);

            while($cStmt->fetch())
            {
                    if ($bRingtone)
                    {
                            $sFileName = $_SERVER['DOCUMENT_ROOT'] . "/m4r/" . str_replac开发者_JS百科e(".WAV", ".M4R", $sFileName);
                            if (file_exists($sFileName))
                            {
                                    $sDownloadPath = str_replace("Sounds", "m4r", str_replace(".WAV", ".M4R", $sDownloadPath));
                                    $aResults[$sDownloadPath] = array($sDownloadPath, $sDescription, $sImageFilePath, $sKeywords, $aSearchStrings);
                            }
                    }
            }

            $aResults = array_merge($aResults, $aQueryResults);

        $cStmt->close();
    }
}

$cConnection->close();

$aResults = array_values($aResults);

function in_arrayi($needle, $haystack) {
    return in_array(strtolower($needle), array_map('strtolower', $haystack));
}

function keywordSort($a, $b)
{
    if ($a[0] === $b[0]) return 0;

    $aKeywords = explode(",", $a[3]);
    $bKeywords = explode(",", $b[3]);

    foreach ($a[4] as $sSearchString)
    {
        $aFound = in_arrayi($sSearchString, $aKeywords);
        $bFound = in_arrayi($sSearchString, $bKeywords);

        if ($aFound && !$bFound)
        {
            return -1;
        }
        else if ($bFound && !$aFound)
        {
            return 1;
        }
    }

    return 0;
}

usort($aResults, "keywordSort");

foreach ($aResults as &$aResult)
{
    unset($aResult[3]);
    unset($aResult[4]);
}

echo json_encode($aResults);

?>

---

Sorting large quantities of data while having to split the field code-side will be slow. Rather than optimizing, I'd seriously recommend another way of doing it, such as full-text indexing. It's really quite neat once it's working.

If full-text really isn't an option, I'd recommend splitting the keywords off into a separate table. That way, you can sort based on a count after grouping. For example ...

SELECT d.*, COUNT(k.id) AS keywordcount
FROM data d
    INNER JOIN keywords k ON (d.id = k.dataid)
WHERE k.value IN ('keyword1', 'keyword2', 'keyword3')
GROUP BY d.id
ORDER BY keywordcount

On another PSish type note, you can probably speed up the thing by UNIONing the selects, followed by ordering, rather than running them all independently.