Mysql query optimization - its really slow

Question

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

(1) it is possible end_date has to be cast to string for this (what is its column definition? Can you use end_date > 1381959736 without the quotes instead? (2) have you configured any indexes which may speed this up? (3) SELECT * & GROUP BY somecolumnname does not totally make sense to me, but that's nitpicking. — Wrikken
– Wrikken, Commented Oct 16, 2013 at 23:03
By all means, forget answering the 2 other questions that were in that comment... — Wrikken
– Wrikken, Commented Oct 16, 2013 at 23:18
Add a SHOW CREATE TABLE deals output to your answer, so people can see what your table, columns, and indexes actually look like. While we're at it: Add the output of EXPLAIN ..yourquery.. & SHOW INDEXES FROM deals. — Wrikken
– Wrikken, Commented Oct 16, 2013 at 23:26

Bohemian · Accepted Answer · 2013-10-17 01:02:50Z

1

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);

answered Oct 17, 2013 at 1:02

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query optimization - its really slow

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related