有谁能帮我弄清楚为什么这些SQL查询花了这么长时间吗？

Question

下面的代码片段摘自我的global.class.php文件。当搜索“类型”等于“postcode”或“part-number”时，执行SQL查询所需的时间是我当前遇到的问题。请参阅下面的代码片段以获得包括处理时间在内的解释。

function findOrder($type, $query) {
    $db = new db;
    $tasks = new tasks;
    
    if($type == 'order-id') {
        return $db->query("
            SELECT details.id, details.order_id, users.title, users.first_name, users.last_name, details.date_time, details.status
            FROM orders_details AS details
            LEFT JOIN orders_users AS users
            ON users.order_id = details.order_id
            WHERE details.order_id LIKE '$query'
            ORDER BY id DESC
        ");
    } elseif($type == 'postcode') {
        return $db->query("                
            SELECT addresses.id AS id, addresses.order_id, addresses.postcode, users.title, users.first_name, users.last_name, details.date_time, details.status 
            FROM orders_users as users 
            LEFT JOIN orders_details as details 
            ON users.order_id = details.order_id
            LEFT JOIN orders_addresses as addresses 
            ON addresses.order_id = details.order_id
            WHERE REPLACE(addresses.postcode, ' ','') LIKE '$query'
            ORDER BY id DESC
        ");
    } elseif($type == 'surname') {
        return $db->query("
            SELECT users.id AS id, users.order_id, users.title, users.first_name, users.last_name, details.date_time, details.status
            FROM orders_users AS users
            LEFT JOIN orders_details AS details 
            ON users.order_id = details.order_id
            WHERE REPLACE(users.last_name, ' ','') LIKE '$query'
            ORDER BY id DESC
        ");
    } elseif($type == 'part-number') {
        $query = $tasks->getProductID($query); //Change $query to product ID
        return $db->query("
            SELECT carts.id AS id, carts.order_id, carts.product_id, users.title, users.first_name, users.last_name, details.date_time, details.status
            FROM orders_users AS users
            LEFT JOIN orders_details AS details 
            ON users.order_id = details.order_id
            LEFT JOIN orders_carts AS carts 
            ON carts.order_id = details.order_id
            WHERE carts.product_id = '$query'
            ORDER BY id DESC
        ");
    }
    
}

请参阅以下执行时间：

type = order-id
query = 106696 //(1 result returned)
page time = 0.0074
sql time = 0.0065

type = surname
query = smith //(23 results returned)
page time = 0.0725
sql time = 0.0700

type = postcode
query = **hidden** //(1 result returned)
page time = 2.6505 
sql time = 0.0125

type = part-number
query = **hidden** //(13 results returned)
page time = 2.8564 //(I also checked the getProductID() function, and this takes 0.0123) 
sql time = 0.0470

Page time是调用findOrder（“type”,“query”）所用的时间；

SQL time是直接在phpMyAdmin内重复查询所用的时间。

在订单ID和姓氏查询与邮政编码和零件号查询之间，我能看到的唯一区别是多了一个左联接。这并不能解释为什么在phpMyAdmin中执行的相同查询返回得如此之快。我不知道为什么返回一个应该少于50ms的查询要花近3秒的时间。

还有人能看出为什么会这样吗？如果你还需要什么，请告诉我。

订单ID：

id select_type表类型possible_keys key key_len ref rows Extra

1使用WHERE的简单详细信息ALL NULL NULL NULL NULL 1975；使用临时的；使用文件端口

1简单用户ALL NULL NULL NULL NULL 1992

姓氏：

1简单用户ALL NULL NULL NULL NULL 1992使用WHERE；使用临时的；使用文件端口

1简单详细信息全部NULL NULL NULL NULL 1975

邮编：

1简单详细信息使用临时的所有NULL NULL NULL NULL 1966；使用文件端口

1简单用户ALL NULL NULL NULL NULL 1983使用WHERE；使用联接缓冲区

1简单地址全部NULL NULL NULL NULL 1983使用where；使用联接缓冲区

零件号：

1简单详细信息所有空空空空1975使用临时；使用文件端口

1简单用户ALL NULL NULL NULL NULL 1992使用WHERE；使用联接缓冲区

1使用WHERE的简单购物车全部为NULL NULL NULL NULL NULL 3611；使用联接缓冲区

--
-- Table structure for table `orders_addresses`
--

CREATE TABLE IF NOT EXISTS `orders_addresses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(255) NOT NULL,
  `active` tinyint(4) NOT NULL,
  `company` varchar(255) NOT NULL,
  `line1` varchar(255) NOT NULL,
  `line2` varchar(255) NOT NULL,
  `line3` varchar(255) NOT NULL,
  `town` varchar(255) NOT NULL,
  `county` varchar(255) NOT NULL,
  `postcode` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2004 ;

-- --------------------------------------------------------

--
-- Table structure for table `orders_carts`
--

CREATE TABLE IF NOT EXISTS `orders_carts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(255) NOT NULL,
  `product_id` int(11) NOT NULL,
  `part_name` varchar(255) NOT NULL,
  `qty_ord` int(11) NOT NULL,
  `qty_rcv` int(11) NOT NULL,
  `cost_net` int(11) NOT NULL,
  `cost_gross` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3629 ;

-- --------------------------------------------------------

--
-- Table structure for table `orders_details`
--

CREATE TABLE IF NOT EXISTS `orders_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(255) NOT NULL,
  `transaction_id` varchar(255) NOT NULL,
  `date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `postage` int(11) NOT NULL,
  `sub_total` int(11) NOT NULL,
  `cart_vat` int(11) NOT NULL,
  `total` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1985 ;

-- --------------------------------------------------------

--
-- Table structure for table `orders_users`
--

CREATE TABLE IF NOT EXISTS `orders_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(255) NOT NULL,
  `active` tinyint(4) NOT NULL,
  `title` varchar(255) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `telephone` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2005 ;

Answer 1

试试这个,

function findOrder($type, $query) {
$db = new db;
$tasks = new tasks;

if($type == 'order-id') {
    return $db->query("
        SELECT details.id, details.order_id, users.title, users.first_name, users.last_name, details.date_time, details.status
        FROM orders_details AS details
        LEFT JOIN orders_users AS users
        ON users.order_id = details.order_id
        WHERE details.order_id LIKE '$query'
        ORDER BY id DESC
    ");
} elseif($type == 'postcode') {
    return $db->query("                
        SELECT addresses.id AS id, addresses.order_id, addresses.postcode, users.title, users.first_name, users.last_name, details.date_time, details.status 
        FROM orders_users as users 
        LEFT JOIN orders_details as details 
        ON details.order_id = users.order_id
        LEFT JOIN orders_addresses as addresses 
        ON details.order_id = addresses.order_id
        WHERE REPLACE(addresses.postcode, ' ','') LIKE '$query'
        ORDER BY id DESC
    ");
} elseif($type == 'surname') {
    return $db->query("
        SELECT users.id AS id, users.order_id, users.title, users.first_name, users.last_name, details.date_time, details.status
        FROM orders_users AS users
        LEFT JOIN orders_details AS details 
        ON users.order_id = details.order_id
        WHERE REPLACE(users.last_name, ' ','') LIKE '$query'
        ORDER BY id DESC
    ");
} elseif($type == 'part-number') {
    $query = $tasks->getProductID($query); //Change $query to product ID
    return $db->query("
        SELECT carts.id AS id, carts.order_id, carts.product_id, users.title, users.first_name, users.last_name, details.date_time, details.status
        FROM orders_users AS users
        LEFT JOIN orders_details AS details 
        ON details.order_id = users.order_id
        LEFT JOIN orders_carts AS carts 
        ON details.order_id = carts.order_id
        WHERE carts.product_id = '$query'
        ORDER BY id DESC
    ");
}

}