MySQL Query Timeout in PHP script, Upgrading to VPS

Question 1

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

Question 2

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.

Question 3

Really appreciate the advice. This was/is an ambitious first real SQL project, so I am sure things are not entirely optimized properly. I added the SHOW CREATE TABLE results in the original question. The logic behind the MasterSKU table is a QtySKU which reflects the Quantity to be deducted for the SKU. MultSKU is a tinyint 0 or 1. If 1, the PHP script knows the SKU has Multiple SKU's associated with it, up to 20 SKU_1 - SKU_20. If 0 then there is just one SKU associated with it, and it looks only to AltSKU.

Question 4

The SKU column has no reference to the actual SKU, just taken from the eBay API Listings SKU. SKU is just a reference SKU to get the actual SKU/SKUs associated with the eBay listing (as I said previously, either in AltSKU or SKU_1 all the way up to a possible SKU_20

Question 5

"Up to" -- all the more reason to put them in another table, not in columns.

Question 6

Surely SKU is always shorter than 255? Recommend you find some civilized limit.

Question 7

Please use InnoDB, not MyISAM. The latter is going away.

Rick James Rick James 80.7k5 gold badges52 silver badges119 bronze badges · Accepted Answer · 2017-03-21 21:22:57Z

1

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.

Share

Improve this answer

edited Mar 22, 2017 at 2:00

answered Mar 21, 2017 at 21:22

Rick James's user avatar

Rick James Rick James

80.7k5 gold badges52 silver badges119 bronze badges

9

Really appreciate the advice. This was/is an ambitious first real SQL project, so I am sure things are not entirely optimized properly. I added the SHOW CREATE TABLE results in the original question. The logic behind the MasterSKU table is a QtySKU which reflects the Quantity to be deducted for the SKU. MultSKU is a tinyint 0 or 1. If 1, the PHP script knows the SKU has Multiple SKU's associated with it, up to 20 SKU_1 - SKU_20. If 0 then there is just one SKU associated with it, and it looks only to AltSKU.

bbruman
– bbruman

2017年03月21日 22:05:14 +00:00
Commented Mar 21, 2017 at 22:05
The SKU column has no reference to the actual SKU, just taken from the eBay API Listings SKU. SKU is just a reference SKU to get the actual SKU/SKUs associated with the eBay listing (as I said previously, either in AltSKU or SKU_1 all the way up to a possible SKU_20

bbruman
– bbruman

2017年03月21日 22:10:41 +00:00
Commented Mar 21, 2017 at 22:10
"Up to" -- all the more reason to put them in another table, not in columns.

Rick James
– Rick James

2017年03月22日 01:52:27 +00:00
Commented Mar 22, 2017 at 1:52
Surely SKU is always shorter than 255? Recommend you find some civilized limit.

Rick James
– Rick James

2017年03月22日 01:53:24 +00:00
Commented Mar 22, 2017 at 1:53
Please use InnoDB, not MyISAM. The latter is going away.

Rick James
– Rick James

2017年03月22日 01:54:04 +00:00
Commented Mar 22, 2017 at 1:54

| Show 4 more comments

Stack Exchange Network

MySQL Query Timeout in PHP script, Upgrading to VPS

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

MySQL Query Timeout in PHP script, Upgrading to VPS

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions