1

I have mysql server hosted. Now, If I issue simple SQL statement
SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12')
it takes about 2 hours to execute. What could be wrong? The ORDERS table is tiny, it has about 10-50 records average per day. The structure of table ORDERS is:

-- phpMyAdmin SQL Dump
-- version 4.2.11
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 30, 2015 at 01:34 PM
-- Server version: 5.5.40-cll-lve
-- PHP Version: 5.5.17

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

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

--
-- Table structure for table `ORDERS`
--

CREATE TABLE IF NOT EXISTS `ORDERS` (
  `USER_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `PLACE_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `PRODUCT_ID` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `PRODUCT_QUANTITY` bigint(3) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ORDERS`
--

INSERT INTO `ORDERS` (`USER_ID`, `PLACE_ID`, `PRODUCT_ID`, `PRODUCT_QUANTITY`) VALUES
('c85bc9f7-704f-418e-98af-a1528622dd42', '2', '2bc7277f-c7c3-43bf-9598-9466c96a8f12', 001),
('c85bc9f7-704f-418e-98af-a1528622dd42', '2', '4782e465-fb0a-46a0-9323-aeb3b05bb10e', 001),
('c85bc9f7-704f-418e-98af-a1528622dd42', '2', 'b2bd1c75-6b01-4105-a626-438e365c95f5', 001);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ORDERS`
--
ALTER TABLE `ORDERS`
 ADD PRIMARY KEY (`USER_ID`,`PLACE_ID`,`PRODUCT_ID`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Here is screenshot of situation: SQL Statetement inside myphpadmin

  • Use explain to find out what is going on. Have you got the appropriate indexes – Ed Heal Oct 30 '15 at 12:30
  • @EdHeal if add EXPLAIN at the beginning of statement, same situation as in screenshot appears. – KernelPanic Oct 30 '15 at 12:37
  • Where is the explain plan? BTW Screen shots are not a good idea as some people access this site via mobile devices – Ed Heal Oct 30 '15 at 12:38
  • @EdHeal if I issue EXPLAIN EXTENDED SELECT EXISTS (SELECT * FROM ORDERS WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42' AND PLACE_ID='2' AND PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12') inside phpmyadmin, there is no answer from phpymyadmin, it just writes "Loading..." and I still have to wait – KernelPanic Oct 30 '15 at 12:43
2

I think the problem stems from the one and only nullable: PRODUCT_QUANTITY. Why ?

Look at your query

SELECT EXISTS (SELECT * FROM ORDERS
WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42'
AND   PLACE_ID='2'
AND   PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12');

The subquery has SELECT * and the ORDERS table has 4 columns

  • 3 with NOT NULL
  • 1 with DEFAULT NULL

Based on the MySQL Documentation on Subquery Optimization, the MySQL Query Optimizer performs subquery optimization by means of converting a subquery as follows:

The MySQL query optimizer has different strategies available to evaluate subqueries. For IN (or =ANY) subqueries, the optimizer has these choices:

  • Semi-join
  • Materialization
  • EXISTS strategy

For NOT IN (or <>ALL) subqueries, the optimizer has these choices:

  • Materialization
  • EXISTS strategy

In your particular case, you explicitly wrote the SELECT EXISTS (SELECT * ...). So, you are more responsible to optimize the subquery. Consequently, you should expect the MySQL Query Optimizer to start messing with you since it did not participate in the construction of the EXISTS strategy.

From the MySQL Documentation on Optimizing Subqueries with EXISTS Strategy, this is how checks on done for your SELECT:

Becomes:

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                      AND oe_1 = ie_1
                      AND ...
                      AND oe_N = ie_N) The following discussion assumes a single pair of outer and inner expression values for simplicity.

The conversion just described has its limitations. It is valid only if we ignore possible NULL values. That is, the “pushdown” strategy works as long as both of these two conditions are true:

  • outer_expr and inner_expr cannot be NULL.
  • You do not need to distinguish NULL from FALSE subquery results. (If the subquery is a part of an OR or AND expression in the WHERE clause, MySQL assumes that you do not care.)

The bulletpoint that says outer_expr and inner_expr cannot be NULL might be the problem. The inner_expr could present possible rows with a NULL in one of the columns in spite of all the data being non-null. To me, such a check is unnecessary.

The same document says the following in the evaluation process:

For proper evaluation, it is necessary to be able to check whether the SELECT has produced any rows at all, so outer_expr = inner_expr cannot be pushed down into the subquery. This is a problem, because many real world subqueries become very slow unless the equality can be pushed down.

To make a long story short, slowness is being imposed because your SELECT EXISTS (SELECT * ...) is not optimal and the MySQL Query Optimizer is still abiding by its preset rules. There are checks being done on possible null results from the subquery even though you know there will be a non-null result.

SUGGESTION

Rather that doing a deeper dive into what makes your query's execution slow, I would rewrite the query as follows:

SELECT COUNT(1) FROM ORDERS
WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42'
AND   PLACE_ID='2'
AND   PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12');

What are the advantages of rewriting the query this way ?

  • Subquery evaluation is completely eliminated
  • Return value is always non-null, either 0 or 1
  • Checking for 0/1 should be just as good as checking forTRUE/FALSE

GIVE IT A TRY !!!

  • It works perfectly now, however, besides your observation there was a flaw in hosted firewall! Both were fixed and now is ok! – KernelPanic Oct 31 '15 at 7:34
0

It was some firewall problem on hosted server, the firm responsible for it took care of firewall glitch.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.