Slow DB query even after moving to Amazon RDS












0















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









share|improve this question
















bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.











  • 1





    ORDER BY -SalesRank DESC? Does this mean ORDER BY SalesRank ASC? If so, write it that way and see what happens.

    – Michael - sqlbot
    Mar 9 '17 at 8:55











  • no effect "1000 rows in set (5 min 40.58 sec)" :(

    – rsn
    Mar 9 '17 at 9:47











  • please attach table structure, also - how many records total returned? if change SELECT t1.* for SELECT count(*)?

    – a_vlad
    Mar 9 '17 at 10:37













  • updated the question. The total as u said was returned as :1042

    – rsn
    Mar 9 '17 at 10:44
















0















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









share|improve this question
















bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.











  • 1





    ORDER BY -SalesRank DESC? Does this mean ORDER BY SalesRank ASC? If so, write it that way and see what happens.

    – Michael - sqlbot
    Mar 9 '17 at 8:55











  • no effect "1000 rows in set (5 min 40.58 sec)" :(

    – rsn
    Mar 9 '17 at 9:47











  • please attach table structure, also - how many records total returned? if change SELECT t1.* for SELECT count(*)?

    – a_vlad
    Mar 9 '17 at 10:37













  • updated the question. The total as u said was returned as :1042

    – rsn
    Mar 9 '17 at 10:44














0












0








0








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









share|improve this question
















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






mysql amazon-rds






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Mar 13 '17 at 5:03









Rick James

43k22259




43k22259










asked Mar 9 '17 at 7:02









rsnrsn

114




114





bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.










  • 1





    ORDER BY -SalesRank DESC? Does this mean ORDER BY SalesRank ASC? If so, write it that way and see what happens.

    – Michael - sqlbot
    Mar 9 '17 at 8:55











  • no effect "1000 rows in set (5 min 40.58 sec)" :(

    – rsn
    Mar 9 '17 at 9:47











  • please attach table structure, also - how many records total returned? if change SELECT t1.* for SELECT count(*)?

    – a_vlad
    Mar 9 '17 at 10:37













  • updated the question. The total as u said was returned as :1042

    – rsn
    Mar 9 '17 at 10:44














  • 1





    ORDER BY -SalesRank DESC? Does this mean ORDER BY SalesRank ASC? If so, write it that way and see what happens.

    – Michael - sqlbot
    Mar 9 '17 at 8:55











  • no effect "1000 rows in set (5 min 40.58 sec)" :(

    – rsn
    Mar 9 '17 at 9:47











  • please attach table structure, also - how many records total returned? if change SELECT t1.* for SELECT count(*)?

    – a_vlad
    Mar 9 '17 at 10:37













  • updated the question. The total as u said was returned as :1042

    – rsn
    Mar 9 '17 at 10:44








1




1





ORDER BY -SalesRank DESC? Does this mean ORDER BY SalesRank ASC? If so, write it that way and see what happens.

– Michael - sqlbot
Mar 9 '17 at 8:55





ORDER BY -SalesRank DESC? Does this mean ORDER BY SalesRank ASC? If so, write it that way and see what happens.

– Michael - sqlbot
Mar 9 '17 at 8:55













no effect "1000 rows in set (5 min 40.58 sec)" :(

– rsn
Mar 9 '17 at 9:47





no effect "1000 rows in set (5 min 40.58 sec)" :(

– rsn
Mar 9 '17 at 9:47













please attach table structure, also - how many records total returned? if change SELECT t1.* for SELECT count(*)?

– a_vlad
Mar 9 '17 at 10:37







please attach table structure, also - how many records total returned? if change SELECT t1.* for SELECT count(*)?

– a_vlad
Mar 9 '17 at 10:37















updated the question. The total as u said was returned as :1042

– rsn
Mar 9 '17 at 10:44





updated the question. The total as u said was returned as :1042

– rsn
Mar 9 '17 at 10:44










2 Answers
2






active

oldest

votes


















0















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

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






share|improve this answer
























  • already i have many fields with Index. Is it a good practice to keep adding index for every field ?

    – rsn
    Mar 9 '17 at 11:28











  • No, since indexes slow down inserts, updates and deletes. Only create indexes that benefit the queries you use.

    – Twinkles
    Mar 9 '17 at 11:30













  • above fields are indexed already.. i am still looking for solution since days.. :(

    – rsn
    Mar 13 '17 at 6:56








  • 1





    A composite index (a.k.a. as multi-column index) is not the same as indexing columns one by one. Check dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html - I also agree with @RickJames that the column Price in the index isn't probably going to help much (basically, because you request a range of SalesRank, not just a single value).

    – joanolo
    Mar 13 '17 at 8:29













  • On second thought I agree: Price can be left out.

    – Twinkles
    Mar 13 '17 at 9:49



















0














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 ....






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f166641%2fslow-db-query-even-after-moving-to-amazon-rds%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0















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

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






    share|improve this answer
























    • already i have many fields with Index. Is it a good practice to keep adding index for every field ?

      – rsn
      Mar 9 '17 at 11:28











    • No, since indexes slow down inserts, updates and deletes. Only create indexes that benefit the queries you use.

      – Twinkles
      Mar 9 '17 at 11:30













    • above fields are indexed already.. i am still looking for solution since days.. :(

      – rsn
      Mar 13 '17 at 6:56








    • 1





      A composite index (a.k.a. as multi-column index) is not the same as indexing columns one by one. Check dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html - I also agree with @RickJames that the column Price in the index isn't probably going to help much (basically, because you request a range of SalesRank, not just a single value).

      – joanolo
      Mar 13 '17 at 8:29













    • On second thought I agree: Price can be left out.

      – Twinkles
      Mar 13 '17 at 9:49
















    0















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

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






    share|improve this answer
























    • already i have many fields with Index. Is it a good practice to keep adding index for every field ?

      – rsn
      Mar 9 '17 at 11:28











    • No, since indexes slow down inserts, updates and deletes. Only create indexes that benefit the queries you use.

      – Twinkles
      Mar 9 '17 at 11:30













    • above fields are indexed already.. i am still looking for solution since days.. :(

      – rsn
      Mar 13 '17 at 6:56








    • 1





      A composite index (a.k.a. as multi-column index) is not the same as indexing columns one by one. Check dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html - I also agree with @RickJames that the column Price in the index isn't probably going to help much (basically, because you request a range of SalesRank, not just a single value).

      – joanolo
      Mar 13 '17 at 8:29













    • On second thought I agree: Price can be left out.

      – Twinkles
      Mar 13 '17 at 9:49














    0












    0








    0








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

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






    share|improve this answer














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

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







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Mar 9 '17 at 11:16









    TwinklesTwinkles

    1,9461921




    1,9461921













    • already i have many fields with Index. Is it a good practice to keep adding index for every field ?

      – rsn
      Mar 9 '17 at 11:28











    • No, since indexes slow down inserts, updates and deletes. Only create indexes that benefit the queries you use.

      – Twinkles
      Mar 9 '17 at 11:30













    • above fields are indexed already.. i am still looking for solution since days.. :(

      – rsn
      Mar 13 '17 at 6:56








    • 1





      A composite index (a.k.a. as multi-column index) is not the same as indexing columns one by one. Check dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html - I also agree with @RickJames that the column Price in the index isn't probably going to help much (basically, because you request a range of SalesRank, not just a single value).

      – joanolo
      Mar 13 '17 at 8:29













    • On second thought I agree: Price can be left out.

      – Twinkles
      Mar 13 '17 at 9:49



















    • already i have many fields with Index. Is it a good practice to keep adding index for every field ?

      – rsn
      Mar 9 '17 at 11:28











    • No, since indexes slow down inserts, updates and deletes. Only create indexes that benefit the queries you use.

      – Twinkles
      Mar 9 '17 at 11:30













    • above fields are indexed already.. i am still looking for solution since days.. :(

      – rsn
      Mar 13 '17 at 6:56








    • 1





      A composite index (a.k.a. as multi-column index) is not the same as indexing columns one by one. Check dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html - I also agree with @RickJames that the column Price in the index isn't probably going to help much (basically, because you request a range of SalesRank, not just a single value).

      – joanolo
      Mar 13 '17 at 8:29













    • On second thought I agree: Price can be left out.

      – Twinkles
      Mar 13 '17 at 9:49

















    already i have many fields with Index. Is it a good practice to keep adding index for every field ?

    – rsn
    Mar 9 '17 at 11:28





    already i have many fields with Index. Is it a good practice to keep adding index for every field ?

    – rsn
    Mar 9 '17 at 11:28













    No, since indexes slow down inserts, updates and deletes. Only create indexes that benefit the queries you use.

    – Twinkles
    Mar 9 '17 at 11:30







    No, since indexes slow down inserts, updates and deletes. Only create indexes that benefit the queries you use.

    – Twinkles
    Mar 9 '17 at 11:30















    above fields are indexed already.. i am still looking for solution since days.. :(

    – rsn
    Mar 13 '17 at 6:56







    above fields are indexed already.. i am still looking for solution since days.. :(

    – rsn
    Mar 13 '17 at 6:56






    1




    1





    A composite index (a.k.a. as multi-column index) is not the same as indexing columns one by one. Check dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html - I also agree with @RickJames that the column Price in the index isn't probably going to help much (basically, because you request a range of SalesRank, not just a single value).

    – joanolo
    Mar 13 '17 at 8:29







    A composite index (a.k.a. as multi-column index) is not the same as indexing columns one by one. Check dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html - I also agree with @RickJames that the column Price in the index isn't probably going to help much (basically, because you request a range of SalesRank, not just a single value).

    – joanolo
    Mar 13 '17 at 8:29















    On second thought I agree: Price can be left out.

    – Twinkles
    Mar 13 '17 at 9:49





    On second thought I agree: Price can be left out.

    – Twinkles
    Mar 13 '17 at 9:49













    0














    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 ....






    share|improve this answer




























      0














      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 ....






      share|improve this answer


























        0












        0








        0







        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 ....






        share|improve this answer













        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 ....







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Mar 13 '17 at 5:10









        Rick JamesRick James

        43k22259




        43k22259






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f166641%2fslow-db-query-even-after-moving-to-amazon-rds%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh