MySQL Forums
Forum List  »  Performance

Improving Slow Query
Posted by: Pankaj joshi
Date: May 03, 2010 01:03AM

Hello Guys,

I want to improve the execution of this particular query on the item table which has around 100MN records.There are mostly 2 queries running on this table



1) select id, title, link, description, author, image, guid, feature_id, feature_name, source, source_name, keyword, pub_date, feature_username, feature_url,

featured, lat, lon from item where feature_id in (867,307,1779,2363,2377,199,2231,2607) order by pub_date DESC limit 0,20


which is at times very slow depending upon the combinations of the feature_id

Execution time for the above mentioned query is 9 Seconds.


Explain for the query
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE feed_item index PRIMARY item_pubdate_featureid 8 \N 6701 Using where

2) select id, title, link, description, author, image, guid, feature_id, feature_name, source, source_name, keyword, pub_date, feature_username, feature_url,

featured, lat, lon from item where feature_id in (867) order by pub_date DESC limit 0,20

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE item ref PRIMARY PRIMARY 4 const 128076 Using where


This query is performing good and mostly under 1 sec.


Table Structure

CREATE TABLE item (
id int(11) NOT NULL AUTO_INCREMENT,
pub_date int(10) unsigned NOT NULL DEFAULT '0',
title varchar(1000) DEFAULT NULL,
link varchar(1000) DEFAULT NULL,
description text,
author varchar(255) DEFAULT NULL,
image varchar(1000) DEFAULT NULL,
guid varchar(255) DEFAULT NULL,
feature_id int(11) NOT NULL DEFAULT '0',
source int(11) DEFAULT NULL,
keyword varchar(255) DEFAULT NULL,
feature_name varchar(255) DEFAULT NULL,
source_name varchar(255) DEFAULT NULL,
feature_username varchar(2000) DEFAULT NULL,
feature_user_id int(11) DEFAULT NULL,
feature_url varchar(2000) DEFAULT NULL,
feature_location varchar(2000) DEFAULT NULL,
hide varchar(20) DEFAULT 'false',
featured varchar(10) DEFAULT NULL,
location varchar(1000) DEFAULT NULL,
full_address varchar(1000) DEFAULT NULL,
city varchar(255) DEFAULT NULL,
state varchar(255) DEFAULT NULL,
country varchar(255) DEFAULT NULL,
zip int(11) DEFAULT NULL,
profession varchar(1000) DEFAULT NULL,
categories varchar(1000) DEFAULT NULL,
keywords varchar(2000) DEFAULT NULL,
sentiment varchar(255) DEFAULT NULL,
gender varchar(60) DEFAULT NULL,
feature_category varchar(255) DEFAULT NULL,
last_updated int(10) unsigned DEFAULT NULL,
lat varchar(255) DEFAULT NULL,
lon varchar(255) DEFAULT NULL,
generic_vc_3 varchar(255) DEFAULT NULL,
generic_int_1 int(10) unsigned DEFAULT NULL,
generic_int_2 int(10) unsigned DEFAULT NULL,
generic_int_3 int(10) unsigned DEFAULT NULL,
groups int(10) unsigned DEFAULT NULL,
PRIMARY KEY (feature_id,pub_date,id),
UNIQUE KEY kk (id,pub_date),
KEY item_pubdate_featureid (pub_date,feature_id)
) ENGINE=InnoDB AUTO_INCREMENT=341575000 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (pub_date)
(PARTITION p0 VALUES LESS THAN (1231056000) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (1231660800) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (1232265600) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (1232870400) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (1233475200) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (1234080000) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (1234684800) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (1235289600) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (1235894400) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (1236499200) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (1237104000) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (1237708800) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (1238313600) ENGINE = InnoDB,
PARTITION p13 VALUES LESS THAN (1238918400) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (1239523200) ENGINE = InnoDB,
PARTITION p15 VALUES LESS THAN (1240128000) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (1240732800) ENGINE = InnoDB,
PARTITION p17 VALUES LESS THAN (1241337600) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN (1241942400) ENGINE = InnoDB,
PARTITION p19 VALUES LESS THAN (1242547200) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN (1243152000) ENGINE = InnoDB,
PARTITION p21 VALUES LESS THAN (1243756800) ENGINE = InnoDB,
PARTITION p22 VALUES LESS THAN (1244361600) ENGINE = InnoDB,
PARTITION p23 VALUES LESS THAN (1244966400) ENGINE = InnoDB,
PARTITION p24 VALUES LESS THAN (1245571200) ENGINE = InnoDB,
PARTITION p25 VALUES LESS THAN (1246176000) ENGINE = InnoDB,
PARTITION p26 VALUES LESS THAN (1246780800) ENGINE = InnoDB,
PARTITION p27 VALUES LESS THAN (1247385600) ENGINE = InnoDB,
PARTITION p28 VALUES LESS THAN (1247990400) ENGINE = InnoDB,
PARTITION p29 VALUES LESS THAN (1248595200) ENGINE = InnoDB,
PARTITION p30 VALUES LESS THAN (1249200000) ENGINE = InnoDB,
PARTITION p31 VALUES LESS THAN (1249804800) ENGINE = InnoDB,
PARTITION p32 VALUES LESS THAN (1250409600) ENGINE = InnoDB,
PARTITION p33 VALUES LESS THAN (1251014400) ENGINE = InnoDB,
PARTITION p34 VALUES LESS THAN (1251619200) ENGINE = InnoDB,
PARTITION p35 VALUES LESS THAN (1252224000) ENGINE = InnoDB,
PARTITION p36 VALUES LESS THAN (1252828800) ENGINE = InnoDB,
PARTITION p37 VALUES LESS THAN (1253433600) ENGINE = InnoDB,
PARTITION p38 VALUES LESS THAN (1254038400) ENGINE = InnoDB,
PARTITION p39 VALUES LESS THAN (1254643200) ENGINE = InnoDB,
PARTITION p40 VALUES LESS THAN (1255248000) ENGINE = InnoDB,
PARTITION p41 VALUES LESS THAN (1255852800) ENGINE = InnoDB,
PARTITION p42 VALUES LESS THAN (1256457600) ENGINE = InnoDB,
PARTITION p43 VALUES LESS THAN (1257062400) ENGINE = InnoDB,
PARTITION p44 VALUES LESS THAN (1257667200) ENGINE = InnoDB,
PARTITION p45 VALUES LESS THAN (1258272000) ENGINE = InnoDB,
PARTITION p46 VALUES LESS THAN (1258876800) ENGINE = InnoDB,
PARTITION p47 VALUES LESS THAN (1259481600) ENGINE = InnoDB,
PARTITION p48 VALUES LESS THAN (1260086400) ENGINE = InnoDB,
PARTITION p49 VALUES LESS THAN (1260146880) ENGINE = InnoDB,
PARTITION p50 VALUES LESS THAN (1260691200) ENGINE = InnoDB,
PARTITION p51 VALUES LESS THAN (1261296000) ENGINE = InnoDB,
PARTITION p52 VALUES LESS THAN (1261900800) ENGINE = InnoDB,
PARTITION p53 VALUES LESS THAN (1262505600) ENGINE = InnoDB,
PARTITION p55 VALUES LESS THAN (1263110400) ENGINE = InnoDB,
PARTITION p56 VALUES LESS THAN (1263715200) ENGINE = InnoDB,
PARTITION p57 VALUES LESS THAN (1264320000) ENGINE = InnoDB,
PARTITION p58 VALUES LESS THAN (1264924800) ENGINE = InnoDB,
PARTITION p59 VALUES LESS THAN (1265529600) ENGINE = InnoDB,
PARTITION p60 VALUES LESS THAN (1266134400) ENGINE = InnoDB,
PARTITION p61 VALUES LESS THAN (1266739200) ENGINE = InnoDB,
PARTITION p62 VALUES LESS THAN (1267344000) ENGINE = InnoDB,
PARTITION p63 VALUES LESS THAN (1267948800) ENGINE = InnoDB,
PARTITION p64 VALUES LESS THAN (1268553600) ENGINE = InnoDB,


Show table Status

Name: item
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 105117815
Avg_row_length: 1106
Data_length: 116263878656
Max_data_length: 0
Index_length: 3834183680
Data_free: 3796893696
Auto_increment: 341575000
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: checksum=1 delay_key_write=1 row_format=DYNAMIC partitioned
Comment:

Please help me in figuring out the solution to this problem , Please let me know if some more information is required.

Thanks in Advance
Pankaj Joshi

Options: ReplyQuote


Subject
Views
Written By
Posted
Improving Slow Query
3967
May 03, 2010 01:03AM
1297
May 04, 2010 11:52PM
1134
May 05, 2010 04:26AM
1304
May 05, 2010 09:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.