MySQL Query Timeout in PHP Script – Upgrading to VPS

We've recently had to upgrade to a VPS from shared hosting because we were getting timeouts on a vital SQL query running from a PHP script.

This PHP script runs hundreds of times a day at random occasions, many times concurrently.

The error I found out while manually testing the PHP script was with this line:

while($rs = mysqli_fetch_assoc($result) { . . .

It would occasionally get the (2013) “Lost connection to MySQL server during query”. error.

Here is the query in question:

SELECT DISTINCT
  Inventory.id,
  Inventory.SKU AS inventory_sku,
  Inventory.Quantity AS inventory_quantity,
  MasterSKU.SKU_1,
  MasterSKU.SKU_2,
  MasterSKU.SKU_3,
  MasterSKU.SKU_4,
  MasterSKU.SKU_5,
  MasterSKU.SKU_6,
  MasterSKU.SKU_7,
  MasterSKU.SKU_8,
  MasterSKU.SKU_9,
  MasterSKU.SKU_10,
  MasterSKU.SKU_11,
  MasterSKU.SKU_12,
  MasterSKU.SKU_13,
  MasterSKU.SKU_14,
  MasterSKU.SKU_15,
  MasterSKU.SKU_16,
  MasterSKU.SKU_17,
  MasterSKU.SKU_18,
  MasterSKU.SKU_19,
  MasterSKU.SKU_20,
  MasterSKU.MultSKU,
  MasterSKU.QtySKU,
  MasterSKU.AltSKU,
  MasterSKU.SKU,
      '$qty' AS sold_quantity,
      '$sku' AS sold_sku
FROM eBayOrders
  LEFT OUTER JOIN MasterSKU
    ON MasterSKU.SKU = '$sku'
  LEFT OUTER JOIN Inventory
    ON Inventory.SKU IN (MasterSKU.AltSKU, MasterSKU.SKU_1, MasterSKU.SKU_2, MasterSKU.SKU_3, MasterSKU.SKU_4, MasterSKU.SKU_5, MasterSKU.SKU_6, MasterSKU.SKU_7, MasterSKU.SKU_8, MasterSKU.SKU_9, MasterSKU.SKU_10, MasterSKU.SKU_11, MasterSKU.SKU_12, MasterSKU.SKU_13, MasterSKU.SKU_14, MasterSKU.SKU_15, MasterSKU.SKU_16, MasterSKU.SKU_17, MasterSKU.SKU_18, MasterSKU.SKU_19, MasterSKU.SKU_20);

On localhost testing, a single instance of this query was running anywhere from 5 – 6.5 seconds.

I was able to optimize this a little bit and get it around to 4 seconds by taking away DISTINCT and adding GROUP BY

SELECT
  Inventory.id,
  Inventory.SKU AS inventory_sku,
  Inventory.Quantity AS inventory_quantity,
  MasterSKU.SKU_1,
  MasterSKU.SKU_2,
  MasterSKU.SKU_3,
  MasterSKU.SKU_4,
  MasterSKU.SKU_5,
  MasterSKU.SKU_6,
  MasterSKU.SKU_7,
  MasterSKU.SKU_8,
  MasterSKU.SKU_9,
  MasterSKU.SKU_10,
  MasterSKU.SKU_11,
  MasterSKU.SKU_12,
  MasterSKU.SKU_13,
  MasterSKU.SKU_14,
  MasterSKU.SKU_15,
  MasterSKU.SKU_16,
  MasterSKU.SKU_17,
  MasterSKU.SKU_18,
  MasterSKU.SKU_19,
  MasterSKU.SKU_20,
  MasterSKU.MultSKU,
  MasterSKU.QtySKU,
  MasterSKU.AltSKU,
  MasterSKU.SKU,
      '$quantitypurchased' AS sold_quantity,
      '$sku' AS sold_sku
FROM eBayOrders
  LEFT OUTER JOIN MasterSKU
    ON MasterSKU.SKU = '$sku'
  LEFT OUTER JOIN Inventory
    ON Inventory.SKU IN (MasterSKU.AltSKU, MasterSKU.SKU_1, MasterSKU.SKU_2, MasterSKU.SKU_3, MasterSKU.SKU_4, MasterSKU.SKU_5, MasterSKU.SKU_6, MasterSKU.SKU_7, MasterSKU.SKU_8, MasterSKU.SKU_9, MasterSKU.SKU_10, MasterSKU.SKU_11, MasterSKU.SKU_12, MasterSKU.SKU_13, MasterSKU.SKU_14, MasterSKU.SKU_15, MasterSKU.SKU_16, MasterSKU.SKU_17, MasterSKU.SKU_18, MasterSKU.SKU_19, MasterSKU.SKU_20)
GROUP BY Inventory.id;

With this I was able to get it down to around 4 seconds (again, for a single instance… while running concurrently on our shared hosting it was giving a timing out error after 30 seconds)

Now, with Shared Hosting, we were not able to change our MySQL configurations such as wait_timeout net_read_timeout and net_write_timeout.

My idea is to change these configurations to a near maximum number, and also enable the MySQL Slow Query Log to get log files.

Additionally, a thought came into my head to a processed tinyint column to my table and check for a successful UPDATE which is at the while($rs = mysqli_fetch_assoc($result), and if it gets this far, look for a successful query and add another query to UPDATE processed = 1. This way I can make sure the entire script has run till its end, and reference this in my database.

I've been working on this project for several months, and these timeout issues are killing the system so need to be fixed.

We are in process of doing the migration to VPS right now, I am wondering if anyone can think of a further way to optimize the query or tables, or any mysql configuration settings we can set to ensure my script and queries run 100% of the time.

Tables in question:

: MasterSKU

CREATE TABLE `MasterSKU` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `SKU` varchar(255) NOT NULL,
 `AltSKU` varchar(255) DEFAULT NULL,
 `QtySKU` int(11) NOT NULL DEFAULT '1',
 `MultSKU` tinyint(1) NOT NULL DEFAULT '0',
 `SKU_1` varchar(255) DEFAULT NULL,
 `SKU_2` varchar(255) DEFAULT NULL,
 `SKU_3` varchar(255) DEFAULT NULL,
 `SKU_4` varchar(255) DEFAULT NULL,
 `SKU_5` varchar(255) DEFAULT NULL,
 `SKU_6` varchar(255) DEFAULT NULL,
 `SKU_7` varchar(255) DEFAULT NULL,
 `SKU_8` varchar(255) DEFAULT NULL,
 `SKU_9` varchar(255) DEFAULT NULL,
 `SKU_10` varchar(255) DEFAULT NULL,
 `SKU_11` varchar(255) DEFAULT NULL,
 `SKU_12` varchar(255) DEFAULT NULL,
 `SKU_13` varchar(255) DEFAULT NULL,
 `SKU_14` varchar(255) DEFAULT NULL,
 `SKU_15` varchar(255) DEFAULT NULL,
 `SKU_16` varchar(255) DEFAULT NULL,
 `SKU_17` varchar(255) DEFAULT NULL,
 `SKU_18` varchar(255) DEFAULT NULL,
 `SKU_19` varchar(255) DEFAULT NULL,
 `SKU_20` varchar(255) DEFAULT NULL,
 `processed` tinyint(1) NOT NULL DEFAULT '0',
 `comments` text,
 PRIMARY KEY (`id`),
 UNIQUE KEY `SKU` (`SKU`)
) ENGINE=MyISAM AUTO_INCREMENT=7815 DEFAULT CHARSET=latin1

: Inventory

CREATE TABLE `Inventory` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `SKU` varchar(255) NOT NULL,
 `Quantity` int(11) NOT NULL DEFAULT '0',
 `soldout` tinyint(1) NOT NULL DEFAULT '0',
 `reorderamt` int(11) NOT NULL DEFAULT '0',
 `zeroday` int(11) DEFAULT NULL,
 `category` text,
 `supplier` text,
 `channel` text,
 `location` text,
 `incoming` varchar(255) DEFAULT NULL,
 `lastmodified` timestamp NULL DEFAULT NULL,
 `createdtime` datetime DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `SKU` (`SKU`)
) ENGINE=InnoDB AUTO_INCREMENT=4753 DEFAULT CHARSET=latin1

: eBayOrders

CREATE TABLE `eBayOrders` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `OrderLineItemID` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `SalesRecordNumber` int(11) DEFAULT NULL,
 `BuyerUserID` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `BuyerEmail` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `Title` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `SKU` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
 `Quantity` int(11) NOT NULL,
 `TransactionPrice` decimal(6,2) DEFAULT NULL,
 `createdtime` datetime DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `ExtendedOrderID` (`OrderLineItemID`)
) ENGINE=MyISAM AUTO_INCREMENT=11668 DEFAULT CHARSET=latin1

Best Answer

Do not splay an array across columns. Instead have another table to JOIN to, with up to 24 rows of SKUs.

In doing so, this un-optimizable clause goes away:

Inventory.SKU IN ( ... 24 columns of `MasterSKU` ... )

In its place will be a simple JOIN that can be optimized.

If you need more help, provide SHOW CREATE TABLE for the tables in question.

More

Start with a table something like:

CREATE TABLE OtherSkus (
    MasterSku VARCHAR(20) CHARSET=latin1 NOT NULL, -- for JOINing
    AltSku VARCHAR(20) CHARSET=latin1 NOT NULL,
    PRIMARY KEY(MasterSku, AltSku)
) ENGINE=InnoDB;

Then see how you can shrink the 24 SKU columns down to perhaps only 1.

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Are two indexes needed

Related Question