StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POmysql Sending data slow on inner join
text
Body
copied!<p>I have tow <strong>SAME</strong> dedicated servers. SQL-query on first work fine and quick.</p> <pre><code>SELECT `store_item`.`product_id` FROM `store_item` INNER JOIN `store` ON ( `store_item`.`store_id` = `store`.`id` ) WHERE ( `store_item`.`product_id` IN ( <many id (up to 500)> ) AND `store`.`is_active` = 1 AND `store_item`.`is_available` = 1 ) LIMIT 200 ; </code></pre> <p><img src="https://i.stack.imgur.com/vGJdh.png" alt="enter image description here"> <img src="https://i.stack.imgur.com/XSMIk.png" alt="enter image description here"></p> <p>On other server with <strong>SAME</strong> configuration and <strong>SAME</strong> version mysql:</p> <p><img src="https://i.stack.imgur.com/ZUl1Y.png" alt="enter image description here"> <img src="https://i.stack.imgur.com/mjGaT.png" alt="enter image description here"></p> <p>What could be wrong?</p> <p><strong>update 1.</strong> Table stucture are same in both servers. <strong>Structure Table <code>store</code></strong></p> <pre><code>CREATE TABLE `store` ( `id` int(11) NOT NULL AUTO_INCREMENT, `type` int(11) NOT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `phone` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(75) COLLATE utf8_unicode_ci NOT NULL, `url` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `description` longtext COLLATE utf8_unicode_ci NOT NULL, `delivery_days` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `currency_id` int(11) NOT NULL, `account_currency_id` int(11) NOT NULL, `minimal_margin` decimal(5,2) NOT NULL, `balance_money` decimal(10,2) NOT NULL, `total_count` int(11) NOT NULL, `added_count` int(11) NOT NULL, `empty_count` int(11) NOT NULL, `unknown_count` int(11) NOT NULL, `is_distributor` tinyint(1) NOT NULL, `is_active` tinyint(1) NOT NULL, `group` int(11) NOT NULL, `sort` int(11) NOT NULL, `return_percent` int(11) NOT NULL, `updated` datetime NOT NULL, PRIMARY KEY (`id`), KEY `store_52094d6e` (`name`), KEY `store_41f657b3` (`currency_id`), KEY `store_145a375d` (`sort`), KEY `store_60ef08a5` (`account_currency_id`) ) ENGINE=MyISAM AUTO_INCREMENT=163 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; </code></pre> <p><strong>Structure <code>store_item</code></strong></p> <pre><code>CREATE TABLE `store_item` ( `id` int(11) NOT NULL AUTO_INCREMENT, `store_id` int(11) NOT NULL, `code` varchar(54) COLLATE utf8_unicode_ci NOT NULL, `product_id` int(11) NOT NULL, `manufacturer_id` int(11) NOT NULL, `num` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `price_in` decimal(10,2) NOT NULL, `price_out` decimal(10,2) NOT NULL, `count` int(11) NOT NULL, `is_available` tinyint(1) NOT NULL, `delivery_days` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `discount_id` int(11) DEFAULT NULL, `discount_code` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `store_id` (`store_id`,`product_id`), KEY `store_item_47799232` (`store_id`), KEY `store_item_44bdf3ee` (`product_id`), KEY `store_item_516bb1bd` (`count`), KEY `store_item_4ac7f441` (`manufacturer_id`), KEY `store_item_3aac1984` (`num`), KEY `store_item_52094d6e` (`name`), KEY `store_item_5c536bf3` (`discount_id`), KEY `store_item_65da3d2c` (`code`), KEY `store_item_5436e97a` (`modified`), KEY `store_item_64b158cd` (`discount_code`), KEY `store_item_5e0d2c81` (`is_available`) ) ENGINE=MyISAM AUTO_INCREMENT=16070923 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload