CODEWITHSUNDEEP
mysql
Vahan
Vahan

Reputation: 534

Mysql query optimization - its really slow

I have next log. Please somebody explain me where is the problem. My table deals is 142906 why is it taking so much time?

 # Query_time: 5.524629  Lock_time: 0.000059 Rows_sent: 3  Rows_examined: 142906
SET timestamp=1381963341;
SELECT *,1  as distance FROM  deals   
WHERE  end_date  > '1381959736'                 
GROUP BY title  
ORDER BY  COALESCE(distance, 999999999) , distance  ASC    LIMIT 0 , 3;


 Here is ddl.


 CREATE TABLE `deals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku` varchar(180) NOT NULL DEFAULT '',
  `price` decimal(8,2) DEFAULT NULL,
  `retail_price` decimal(8,2) DEFAULT NULL,
  `category` int(1) DEFAULT '4',
  `advertiser` varchar(120) DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `tiks` int(5) DEFAULT NULL,
  `buy_url` varchar(255) DEFAULT NULL,
  `description` text,
  `views` int(6) NOT NULL DEFAULT '1',
  `title` varchar(100) DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `api` varchar(50) DEFAULT NULL,
  `discount` int(2) DEFAULT NULL,
  `black_list` smallint(1) DEFAULT '0',
  `gender` tinyint(1) DEFAULT NULL,
  `sort` tinyint(1) DEFAULT NULL,
  `is_home` tinyint(1) DEFAULT NULL,
  `is_quick_shop` tinyint(1) DEFAULT NULL,
  `date_add` int(11) DEFAULT NULL,
  `is_sale` tinyint(1) DEFAULT NULL,
  `is_new` tinyint(1) DEFAULT NULL,
  `is_brand_show` tinyint(1) DEFAULT NULL,
  `date_modified` int(11) NOT NULL DEFAULT '0',
  `modifier_id` smallint(2) DEFAULT NULL,
  `modified` smallint(1) DEFAULT NULL,
  `longitute` decimal(10,8) DEFAULT NULL,
  `latitute` decimal(10,8) DEFAULT NULL,
  `is_deal` tinyint(1) DEFAULT NULL,
  `best_seller` tinyint(1) DEFAULT NULL,
  `home_cat` int(2) DEFAULT NULL,
  `end_date` int(11) DEFAULT NULL,
  `temp_category` varchar(120) DEFAULT NULL,
  `update_cron` smallint(1) unsigned DEFAULT '1',
  `alias` varchar(120) DEFAULT NULL,
  `found_by` text,
  `fb_share` smallint(3) DEFAULT NULL,
  `tw_share` smallint(3) DEFAULT NULL,
  `pin_share` smallint(3) DEFAULT NULL,
  `google_share` smallint(3) DEFAULT NULL,
  `last_tiked` int(11) DEFAULT NULL,
  `is_simple` smallint(1) DEFAULT '0',
  `dealer_id` int(3) DEFAULT NULL,
  `clicks` int(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `tiks` (`tiks`),
  KEY `category` (`category`),
  KEY `bests` (`best_seller`),
  KEY `ne` (`is_new`),
  KEY `qu` (`is_quick_shop`),
  KEY `end_date` (`end_date`),
  KEY `lat` (`latitute`),
  KEY `lon` (`longitute`),
  KEY `alias` (`alias`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `desc` (`description`),
  FULLTEXT KEY `brand` (`brand`),
  FULLTEXT KEY `advertiser` (`advertiser`),
  FULLTEXT KEY `found_by` (`found_by`)
) ENGINE=MyISAM AUTO_INCREMENT=1861942 DEFAULT CHARSET=utf8

Upvotes: 0

Views: 89

Answers (1)

Bohemian
Bohemian

Reputation: 425033

Assuming end_date is a date, convert the number into a date type using FROM_UNIXTIME()

SELECT *, 1 as distance
FROM deals
WHERE end_date > FROM_UNIXTIME(1381959736)
GROUP BY title
ORDER BY COALESCE(distance, 999999999), distance ASC
LIMIT 0, 3;

That way mysql doesn't have the cast every row's end_date to a string, which is very slow. It can also use an index if one exists, so make sure you have one:

create index deals_end_date on deals(end_date);

Upvotes: 1

Related Questions