The following snippet of code is taken from my global.class.php file. I am currently having problems with the time it takes to to perform an SQL query when the search "type" equals 'postcode' or 'part-number'. Please see below the snippet for an explanation including processing times.
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
");
}
}
Please see below execution times:
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 is the time taken to call findOrder('type', 'query');
SQL time is the time taken when I repeat the query from directly within phpMyAdmin.
The only difference I can see between the order-id and surname queries against the postcode and part-number queries is that there is an extra LEFT JOIN. This doesn't explain why the same query performed in phpMyAdmin returns so quickly. I have no idea why it's taking nearly 3 seconds to return a query that should take less than 50ms.
Can anyone else see why this is happening? If you need anything else please let me know.
EXPLAIN QUERIES AS REQUESTED
order-id:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE details ALL NULL NULL NULL NULL 1975 Using where; Using temporary; Using filesort
1 SIMPLE users ALL NULL NULL NULL NULL 1992
surname:
1 SIMPLE users ALL NULL NULL NULL NULL 1992 Using where; Using temporary; Using filesort
1 SIMPLE details ALL NULL NULL NULL NULL 1975
postcode:
1 SIMPLE details ALL NULL NULL NULL NULL 1966 Using temporary; Using filesort
1 SIMPLE users ALL NULL NULL NULL NULL 1983 Using where; Using join buffer
1 SIMPLE addresses ALL NULL NULL NULL NULL 1983 Using where; Using join buffer
part-number:
1 SIMPLE details ALL NULL NULL NULL NULL 1975 Using temporary; Using filesort
1 SIMPLE users ALL NULL NULL NULL NULL 1992 Using where; Using join buffer
1 SIMPLE carts ALL NULL NULL NULL NULL 3611 Using where; Using join buffer
CREATE TABLE QUERIES AS REQUESTED
--
-- 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 ;
