加速MySQL Query + PHP

Question

I want to speed up this code. It is the query that takes time. If I change the amount of rows returned from 100 to 10, it takes almost the same amount of time (about 2 seconds). The GETs are based on user sort/search input. How do I improve the speed of this? This item table has about 2374744 rows, and the bot table about 20 rows.

$bot = " && user_items_new.bot_id != '0'";
if ($_GET['bot'] != 0) {
    $bot = " && user_items_new.bot_id='".$_GET['bot']."'";
}

$name = '';
if (strlen($_GET['name']) > 0) {
    $name = " && user_items_new.name LIKE '%".$_GET['name']."%'";
}

$min = '';
if (strlen($_GET['min']) > 0) {
    $min = " && steam_price >= '".$_GET['min']."'";
}

$max = '';
if (strlen($_GET['max']) > 0) {
    $max = " && steam_price <= '".$_GET['max']."'";
}

$order = '';
if ($_GET['order'] == 'price_desc') {
    $order = "ORDER BY steam_price DESC, user_items_new.name ASC";
} elseif ($_GET['order'] == 'price_asc') {
    $order = "ORDER BY steam_price ASC, user_items_new.name ASC";
} elseif ($_GET['order'] == 'name_desc') {
    $order = "ORDER BY user_items_new.name DESC";
} else {
    $order = "ORDER BY user_items_new.name ASC";
}

$limit = $_GET['start'];
$limit .= ', 100';

$i = 0;
$sql = mysql_query("SELECT user_item_id, user_items_new.bot_id AS item_bot_id, sticker, `key`, `case`, exterior, stattrak, image, user_items_new.name AS item_name, steam_price, color, bots_new.bot_id, bots_new.name AS bot_name, withdraw_enabled FROM user_items_new LEFT JOIN bots_new ON user_items_new.bot_id=bots_new.bot_id WHERE steam_price > '0.1' && deposit_start='0' && deposited='0' && user_id='0' && withdraw_enabled='1' ".$bot." ".$name." ".$min." ".$max." ".$order." LIMIT ".$limit)or die(mysql_error());
while ($item = mysql_fetch_assoc($sql)) {
    //...
}

The item table looks like this (dumped from phpMyAdmin):

CREATE TABLE IF NOT EXISTS `user_items_new` (
`user_item_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `bot_id` int(11) NOT NULL,
  `item_original_id` varchar(22) NOT NULL,
  `item_real_id` varchar(22) NOT NULL,
  `class_id` varchar(22) NOT NULL,
  `weapon_id` int(11) NOT NULL,
  `name` text NOT NULL,
  `image` text NOT NULL,
  `case` int(11) NOT NULL,
  `key` int(11) NOT NULL,
  `sticker` int(11) NOT NULL,
  `capsule` int(11) NOT NULL,
  `holo` int(11) NOT NULL,
  `name_tag` int(11) NOT NULL,
  `access_pass` int(11) NOT NULL,
  `stattrak` int(11) NOT NULL,
  `color` varchar(32) NOT NULL,
  `exterior` text NOT NULL,
  `steam_price` double NOT NULL,
  `deposited` int(11) NOT NULL,
  `deposit_start` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5219079 DEFAULT CHARSET=utf8;

ALTER TABLE `user_items_new`
 ADD PRIMARY KEY (`user_item_id`), ADD KEY `user_id` (`user_id`), ADD KEY `bot_id` (`bot_id`);

ALTER TABLE `user_items_new`
MODIFY `user_item_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5219079;

And then the bot table:

CREATE TABLE IF NOT EXISTS `bots_new` (
`bot_id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `username` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `deposit_enabled` int(11) NOT NULL,
  `withdraw_enabled` int(11) NOT NULL,
  `ident` varchar(32) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

ALTER TABLE `bots_new`
 ADD PRIMARY KEY (`bot_id`);

Edit (adding prettyprinted SELECT)

SELECT  user_item_id, user_items_new.bot_id AS item_bot_id, sticker,
        key, case, exterior, stattrak, image, user_items_new.name AS item_name,
        steam_price, color, bots_new.bot_id, bots_new.name AS bot_name,
        withdraw_enabled
    FROM  user_items_new
    LEFT JOIN  bots_new ON user_items_new.bot_id=bots_new.bot_id
    WHERE  user_items_new.bot_id != '0' && deposit_start='0' && deposited='0' && user_id='0' && withdraw_enabled='1'
    ORDER BY  user_items_new.name ASC
    LIMIT  , 100

Answer 1

How to speed this up...

Firstly, add a composite index on the columns that have predicates with equality comparisons first, e.g.

... ON user_items_new (user_id,deposited,deposit_start)

This will be of benefit if the predicates are filtering out a large number of rows. For example, if less than 10% of the rows satisfy the condition user_id = 0.

As an aside, the predicate withdraw_enabled='1' will negate the "outerness" of the LEFT JOIN. The result from the query will be equivalent if the keyword LEFT is omitted.

Another issue is that the ORDER BY will cause a "Using filesort" operation to sort the rows. The entire set will need to be sorted, before the LIMIT clause is applied. So we don't expect LIMIT 10 to be any faster than LIMIT 1000, apart from the additional time for the client to transfer an additional 990 rows. (The bit about sorting the entire set isn't entirely true; in some cases MySQL can abort the sort operation after identifying the first "limit" number of rows. But MySQL will still need to go through the entire set to get those first rows.)

It's possible that adding the column(s) in the ORDER BY clause to the index, following the columns with equality predicates. These would need to appear immediately following the columns referenced in the equality predicates. It may also be necessary to specify those same columns in the ORDER BY clause.

Assuming the current query includes:

... 
  WHERE ...
     && deposit_start='0' && u.deposited='0' && u.user_id='0' ...
...
  ORDER BY steam_price ASC, user_items_new.name ASC 

This index may be appropriate:

... ON user_items_new (user_id,deposited,deposit_start,steam_price,name)

The output from EXPLAIN will show whether that index is used for the query or not. Beyond the equality comparisons of the first three columns, MySQL can use a range scan operation on the index to satisfy the steam_price > predicate.

---

There's also the issue of the InnoDB buffer pool; how much memory is allocated to holding index and data pages in memory, to avoid storage i/o.

To avoid lookups to data pages in the underlying table, you can consider creating a covering index for the query. A covering index includes all of the columns referenced from the table, so the query can be satisfied entirely from the index. The EXPLAIN output will show "Using index" in the Extra column if the query is using a covering index. (But there are limits to the number of columns and the total row size in the index. This would most benefit the performance of the query when the table rows are large, and the size of the columns in the index is a small subset of the total table row.

Answer 2

With a table of that size, one of the simplest tricks you can use for optimizing the query is to add indexes on the fields you use in the where clause. This allows the parser to have stuff presorted for the queries you use most often.

For example, you should see significant gains by doing:

ALTER TABLE user_items_new ADD INDEX (steam_price);

The data and data type go a long way in determining the actual gains made. Adding indexes on all fields will result in going backwards on the efficiency of the query. So more is not necessarily better.

Answer 3

Your query is slow because your query against the user_items_new table requires inspecting 1.2 million rows. While you have indexes for user_item_id, user_id, and bot_id, those can only filter your results so far.

You will want to add indexes on some of your data columns. Which indexes you will want to add (and whether any of them are compound or not) is going to depend on the actual contents of the table and would be difficult to recommend without more information.

You will want to add indexes based on which columns where distinct values reduce the data that must be looked at significantly; an index on withdraw_enabled, for example, is not likely to gain much unless very few rows have withdraw_enabled == 1. An index on steam_price will be beneficial if very few of your rows have a steam_price >= 0.1.