CODEWITHSUNDEEP
phpmysqlquery-optimization
P. Nick
P. Nick

Reputation: 991

Making my queries shorter, faster and better

I'm running a large PHP script to get database info which is adapted for search queries and so on. The problem is that the loading time for the page is several seconds (locally) and up to a minute on remote databases.

I'm not the best one when it comes to optimizing, but I know but the asterisk in queries, but in this case I need it because I'm using all fields in the table.

Keep in mind that the table it gets data from has over 800k rows in total (no im not showing 800k rows, as seen in the code I'm showing it WHERE = something)

<?php

    $getPage = $mysqli->real_escape_string(@$_GET["page"]); 
    $getSearch = $mysqli->real_escape_string(@$_GET["search"]);
    $getUser = $mysqli->real_escape_string(@$_GET["user"]);

    if(isset($getPage)) { $page  = $getPage; } else { $page = 1; }; 
    $start_from = ($page-1) * 12;

    $order = $mysqli->real_escape_string(@$_GET["ord"]); 

    $order_query = "item_name ASC";

    if($start_from >= 0) {
        if($getSearch)
        {
            if($order)
            {
                if($order == 1)
                {
                    $order_query = "item_name ASC";
                }

                if($order == 2)
                {
                    $order_query = "item_name DESC";
                }

                if($order == 3)
                {
                    $order_query = "item_id ASC";
                }

                if($order == 4)
                {
                    $order_query = "item_id DESC";
                }

                $shop = $mysqli->query("SELECT * FROM store_inventory WHERE (item_name LIKE '%".$getSearch."%' OR item_type LIKE '%".$getSearch."%' OR item_quality LIKE '%".$getSearch."%')  AND steamid = '".$getUser."' GROUP BY item_id ORDER BY ".$order_query." LIMIT $start_from, 28");
            }
            else
            {
                $shop = $mysqli->query("SELECT * FROM store_inventory WHERE (item_name LIKE '%".$getSearch."%' OR item_type LIKE '%".$getSearch."%' OR item_quality LIKE '%".$getSearch."%')  AND steamid = '".$getUser."' GROUP BY item_id ORDER BY item_name ASC LIMIT $start_from, 28");
            }
        }
        else 
        {
            if($order)
            {
                if($order == 1)
                {
                    $order_query = "item_name ASC";
                }

                if($order == 2)
                {
                    $order_query = "item_name DESC";
                }

                if($order == 3)
                {
                    $order_query = "item_id ASC";
                }

                if($order == 4)
                {
                    $order_query = "item_id DESC";
                }

                $shop = $mysqli->query("SELECT * FROM store_inventory WHERE steamid = '".$getUser."' GROUP BY item_id ORDER BY ".$order_query." LIMIT $start_from, 28");
            }
            else
            {
                $shop = $mysqli->query("SELECT * FROM store_inventory WHERE steamid = '".$getUser."' GROUP BY item_id ORDER BY item_name ASC LIMIT $start_from, 28");
            }
        }
    } else {
        $start_from = 0;
        if($getSearch)
        {
            if($order)
            {
                if($order == 1)
                {
                    $order_query = "item_name ASC";
                }

                if($order == 2)
                {
                    $order_query = "item_name DESC";
                }

                if($order == 3)
                {
                    $order_query = "item_id ASC";
                }

                if($order == 4)
                {
                    $order_query = "item_id DESC";
                }

                $shop = $mysqli->query("SELECT * FROM store_inventory WHERE (item_name LIKE '%".$getSearch."%' OR item_type LIKE '%".$getSearch."%' OR item_quality LIKE '%".$getSearch."%')  AND steamid = '".$getUser."' GROUP BY item_id ORDER BY ".$order_query." LIMIT $start_from, 28");
            }
            else
            {
                $shop = $mysqli->query("SELECT * FROM store_inventory WHERE (item_name LIKE '%".$getSearch."%' OR item_type LIKE '%".$getSearch."%' OR item_quality LIKE '%".$getSearch."%')  AND steamid = '".$getUser."' GROUP BY item_id ORDER BY item_name ASC LIMIT $start_from, 28");
            }
        }
        else 
        {
            if($order)
            {
                if($order == 1)
                {
                    $order_query = "item_name ASC";
                }

                if($order == 2)
                {
                    $order_query = "item_name DESC";
                }

                if($order == 3)
                {
                    $order_query = "item_id ASC";
                }

                if($order == 4)
                {
                    $order_query = "item_id DESC";
                }

                $shop = $mysqli->query("SELECT * FROM store_inventory WHERE  steamid = '".$getUser."' GROUP BY item_id ORDER BY ".$order_query." LIMIT $start_from, 28");
            }
            else
            {
                $shop = $mysqli->query("SELECT * FROM store_inventory WHERE  steamid = '".$getUser."' GROUP BY item_id ORDER BY item_name ASC LIMIT $start_from, 28");
            }
        }
    }

    $countarticles = $mysqli->query("SELECT COUNT(item_id) FROM store_inventory");
    $row = $countarticles->fetch_row();
    $total_records = $row[0]; 
    $total_pages = ceil($total_records / 28);

    $Pages[] = "";        
    for ($i = 1; $i <= $total_pages; $i++) { 
        $Pages[] = "<li><a href='?page=".$i."'>".$i."</a></li>";
    }

    while($fetch_market = $shop->fetch_array())
    {
        $iInv[] = $fetch_market;
    }

How can I reduce the loading time of the script/page significantly?

Upvotes: 0

Views: 318

Answers (2)

radgex
radgex

Reputation: 143

Use PHP 7, Prepared Statements and Functions!

Why use PHP 7? PHP 7 is twice faster than PHP 5.6 according to [Anna Monus][1]

Why use Prepared Statements? PHP7 + MySQLi gives better performance and security. However, your code is still vulnerable to SQL injection. NOTE that Prepared Statements can be slow. Prepared statements become faster only when you're preparing the statement and then executing it multiple times. But since you're using mysqli_real_escape_string, which does a roundtrip to the database, by replacing all of those roundtrips with a single prepared statement makes your code faster.

Why use functions Functions makes your code shorter, easier to maintain and in most cases, execute faster. Since you're constantly repeating your code. You can shorten it up by using function(s).

I know this is long enough so here is your code:

$getPage = $mysqli->real_escape_string(@$_GET["page"]); 
$getSearch = $mysqli->real_escape_string(@$_GET["search"]);
$getUser = $mysqli->real_escape_string(@$_GET["user"]);

if(isset($getPage)) { $page  = $getPage; } else { $page = 1; }; 
$start_from = ($page-1) * 12;

$order = $mysqli->real_escape_string(@$_GET["ord"]); 

$order_query = "item_name ASC";

function order_item($order) {
    if($order == 1) {
        $order_query = "item_name ASC";
    }
    if($order == 2) {
        $order_query = "item_name DESC";
    }
    if($order == 3) {
        $order_query = "item_id ASC";
    }
    if($order == 4) {
        $order_query = "item_id DESC";
    }
return $order_query;
}

function shop_type($type, $orderBy) {
    $getSearch = "%$getSearch%";
    if ( $type = 'searched' ) {
        $add = "AND (item_name LIKE ? OR item_type LIKE ? OR item_quality LIKE ?)";
    } else {
        $add = "";
    }
    $stmt = $con->prepare("SELECT * FROM store_inventory 
        WHERE steamid = ? 
        ".$add."
        GROUP BY item_id
        ORDER BY ".$orderBy." ASC
        LIMIT $start_from, 28"); 
    $stmt->bind_param("is", $getUser, $getSearch);
    $stmt->execute();
    $result = $stmt->get_result();
    if($result->num_rows === 0) exit('No rows');
    while($row = $result->fetch_assoc()) {
      $arr[] = $row;
    }
    return $arr;
}

function get_items() {
     if($getSearch) {
        if($order) {
            order_item($order);
            $shop = shop_type('searched',$order_query);
        } else {
            $shop = shop_type('searched','item_name');
        }
    } else {
        if($order) {
            order_item($order);
            $shop = shop_type('unsearched',$order_query);
        } else {
            $shop = shop_type('unsearched','item_name');
        }
    }
}

if($start_from >= 0) {
   get_items();
} else {
    $start_from = 0;
    get_items();
}

$countarticles = $mysqli->query("SELECT COUNT(item_id) FROM store_inventory");
$row = $countarticles->fetch_row();
$total_records = $row[0]; 
$total_pages = ceil($total_records / 28);

$Pages[] = "";        
for ($i = 1; $i <= $total_pages; $i++) { 
    $Pages[] = "<li><a href='?page=".$i."'>".$i."</a></li>";
}

while($fetch_market = $shop->fetch_array())
{
    $iInv[] = $fetch_market;
}

ANOTHER TIP:

Put the function(s) in a separate PHP file and call it using:

require "filename.php";

Hope this helps!

Upvotes: 0

O. Jones
O. Jones

Reputation: 108696

This is your select query.

SELECT * 
  FROM store_inventory
 WHERE (   item_name LIKE '%".$getSearch."%'
        OR item_type LIKE '%".$getSearch."%'
        OR item_quality LIKE '%".$getSearch."%'
       )
   AND steamid = '".$getUser."'
 GROUP BY item_id ORDER BY ".$order_query."  
 LIMIT $start_from, 28

With respect, the way you have written this query is guaranteed to be very slow.

Why?

First, you are using WHERE column LIKE '%value%'. That kind of filter expression cannot possibly be sped up by an index. Why not? Because it has to look at every value in the column to see whether it matches your expression. WHERE column LIKE 'value%', on the other hand, without the leading %, can exploit an index. The way you have it requires a full table scan to perform your filter.

Second, you are using three OR expressions, each with that poorly performing LIKE filter. That turns your one table scan into three, tripling the time taken.

Third, you are misusing GROUP BY here. MySQL lets you get away with all kinds of strange stuff here. Read this: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html I would make a suggestion about how to fix this, but I can't guess what you're trying to do.

Fourth, you are using the notorious SELECT * ... ORDER BY x LIMIT y performance antipattern. That sorts a whole mess of rows, only to discard all but a few of them.

What can you do about this? First of all, make sure you have an index on that steamid column. If it is reasonably selective, that will help a lot by reducing the number of rows needing scanning.

Second, try replacing your steamid index with a compound index on 

steamid, item_name, item_type, item_quality

That may (or may not) make your LIKE scans more efficient.

Third, figure out what you're actually trying to accomplish with GROUP BY and try to do it correctly.

Fourth, try to reduce the burden on ORDER BY. Something like this may help.

SELECT * FROM store_inventory
 WHERE id IN (SELECT id
                FROM store_inventory
               WHERE (    this
                       OR that
                       OR the_other_thing
                     )
                 AND steamid = something
               ORDER BY some_column
               LIMIT start, count
              )

This makes it so MySQL only has to sort the ids. It's faster.

Fifth, look up MySQL's FULLTEXT searching option. It may replace your series of LIKE operations with something far more efficient.

Upvotes: 1

Related Questions