doushuangai9733 2016-01-08 16:48
浏览 27
已采纳

加速MySQL Query + PHP

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
  • 写回答

3条回答 默认 最新

  • dousong4777 2016-01-09 04:36
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 PointNet++的onnx模型只能使用一次
  • ¥20 西南科技大学数字信号处理
  • ¥15 有两个非常“自以为是”烦人的问题急期待大家解决!
  • ¥30 STM32 INMP441无法读取数据
  • ¥15 R语言绘制密度图,一个密度曲线内fill不同颜色如何实现
  • ¥100 求汇川机器人IRCB300控制器和示教器同版本升级固件文件升级包
  • ¥15 用visualstudio2022创建vue项目后无法启动
  • ¥15 x趋于0时tanx-sinx极限可以拆开算吗
  • ¥500 把面具戴到人脸上,请大家贡献智慧,别用大模型回答,大模型的答案没啥用
  • ¥15 任意一个散点图自己下载其js脚本文件并做成独立的案例页面,不要作在线的,要离线状态。