Databases
StackOverflow2013
Postsdbo
POMysql query optimization - its really slow
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql query optimization - its really slow
    text
    copied!<p>I have next log. Please somebody explain me where is the problem. My table deals is 142906 why is it taking so much time?</p> <pre><code> # 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 &gt; '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 </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