Sfrgttk

I have almost 3 Million records and table is quite simple. the columns are indexed in the main table but still this query takes 5 minutes to execute in Mysql command line.

SELECT  t1.*

    FROM  asins as t1

    WHERE  t1.Category LIKE 'Toy'

      AND  t1.SalesRank>=200

      AND  t1.SalesRank<=6000

      AND  t1.Price>=15

      AND  t1.Price<=40

      AND  t1.Title NOT LIKE '%lego%'

    ORDER BY  -SalesRank DESC

    LIMIT  0,10;

10 rows in set (5 min 46.43 sec)

here is the Explain.

+----+-------------+-------+-------+------------------------------------------+-----------------------+---------+------+--------+---------------------------------------------------------------+

| id | select_type | table | type  | possible_keys                            | key                   | key_len | ref  | rows   | Extra                                                         |

+----+-------------+-------+-------+------------------------------------------+-----------------------+---------+------+--------+---------------------------------------------------------------+

|  1 | SIMPLE      | t1    | range | Category,SalesRank,SalesRankStockUpdated | SalesRankStockUpdated | 5       | NULL | 180934 | Using index condition; Using where; Using MRR; Using filesort |

+----+-------------+-------+-------+------------------------------------------+-----------------------+---------+------+--------+---------------------------------------------------------------+

I have just moved my DB to Amazon RDS ( 1GB RAM) and i am running the query via command line. My EC2 and database are in the same region.

• There is some thing going on with the table, I am running another
  Cron that is selecting the values form the same table and updating
  another table. Is that locking the database or some thing ?

The results are different,
what can i do to optimize it further.

any thoughts.

Table Structure

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 utf8mb4 */;



--

--



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



--

-- Table structure for table `asins`

--



CREATE TABLE `asins` (

  `Id` int(10) UNSIGNED NOT NULL,

  `ASIN` char(10) COLLATE utf8mb4_unicode_ci NOT NULL,

  `sASIN` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,

  `SalesRank` int(10) UNSIGNED DEFAULT NULL,

  `TotalNew` int(11) DEFAULT NULL,

  `Weight` float DEFAULT NULL,

  `Category` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `Title` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `ReviewsNum` int(10) UNSIGNED DEFAULT NULL,

  `ReviewsAvgRating` decimal(2,0) DEFAULT NULL,

  `Price` decimal(10,2) UNSIGNED DEFAULT NULL,

  `Currency` enum('USD') COLLATE utf8mb4_unicode_ci DEFAULT 'USD',

  `MonthlyCalculatedSales` int(10) UNSIGNED DEFAULT NULL,

  `SmallImageURL` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `LargeImageURL` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `Brand` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `BrowseNodeId` bigint(20) UNSIGNED DEFAULT NULL,

  `Manufacturer` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `MerchantName` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `Updated` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',

  `RankUpdated` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',

  `ReviewsNumUpdated` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',

  `StockUpdated` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',

  `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `imagehash` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  `fetched_asins` tinyint(1) NOT NULL DEFAULT '0',

  `monthly_cron` tinyint(4) NOT NULL,

  `deleted` tinyint(1) NOT NULL DEFAULT '0',

  `Stock` int(10) UNSIGNED DEFAULT NULL,

  `Priority` int(10) UNSIGNED NOT NULL DEFAULT '2',

  `round2` tinyint(1) NOT NULL DEFAULT '0'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;



--

-- Indexes for dumped tables

--



--

-- Indexes for table `asins`

--

ALTER TABLE `asins`

  ADD PRIMARY KEY (`Id`),

  ADD UNIQUE KEY `ASIN` (`ASIN`),

  ADD KEY `Category` (`Category`),

  ADD KEY `Priority` (`Priority`),

  ADD KEY `SalesRank` (`SalesRank`),

  ADD KEY `Stock` (`Stock`),

  ADD KEY `StockUpdated` (`StockUpdated`),

  ADD KEY `RankUpdated` (`RankUpdated`),

  ADD KEY `SalesRankStockUpdated` (`SalesRank`,`StockUpdated`) USING BTREE,

  ADD KEY `fetched_asins` (`fetched_asins`),

  ADD KEY `ReviewsNumUpdated` (`ReviewsNumUpdated`) USING BTREE,

  ADD KEY `monthly_cron` (`monthly_cron`);



--

-- AUTO_INCREMENT for dumped tables

--



--

-- AUTO_INCREMENT for table `asins`

--

ALTER TABLE `asins`

  MODIFY `Id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

/*!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 */;

1. Since you are not using wildcards, replace t1.Category LIKE 'Toy' with t1.Category = 'Toy'
   


2. Add an index over (Category, SalesRank, Price)

Change ORDER BY -SalesRank DESC to ORDER BY SalesRank ASC (so that an index can be used), and add this composite index:

INDEX(category, SalesRank)

(Adding on Price will make the index bulkier, but not better.)

Also, don't use SELECT t1.* unless you really need all the columns. Instead, spell out the desired columns. This will cut back on the bulkiness of the intermediate table, if one is used.

After making those changes, provide EXPLAIN SELECT ....