SQL select query taking long time

Question

I am using the below SQL query but it takes more than 180 sec to execute. Is there a way to speed it up ? This SQL give me the pic_id and of all the females.

SELECT pic_id, small
FROM picture
WHERE hide =0
AND userhide =0
AND  `fbid` 
IN (

      SELECT fbid
      FROM user
      WHERE gender =  "female"
   )

ORDER BY  `picture`.`pic_id` ASC 
LIMIT 1500 , 200

The Explain SQL

id select_type         table    type   possible_keys  key      key_len  ref   rows  Extra
1  PRIMARY             picture  index  NULL           PRIMARY  4        NULL  1700  Using where
2  DEPENDENT SUBQUERY  user     ALL    NULL           NULL     NULL     NULL  7496  Using where

--- Result of explain statement for Tim's sql answer --

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  picture ALL NULL    NULL    NULL    NULL    41443   Using where; Using temporary; Using filesort
1   SIMPLE  user    ALL NULL    NULL    NULL    NULL    7501    Using where; Using join buffer

-- Structure ---

CREATE TABLE `user` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 NOT NULL,
`first_name` varchar(255) CHARACTER SET utf8 NOT NULL,
`username` varchar(255) CHARACTER SET utf8 NOT NULL,
`birthday` date NOT NULL,
`location` varchar(255) CHARACTER SET utf8 NOT NULL,
`gender` varchar(6) CHARACTER SET utf8 NOT NULL,
`created` date NOT NULL,
`fbid` bigint(50) NOT NULL,
`token` varchar(255) CHARACTER SET utf8 NOT NULL,
`relationship_status` varchar(20) CHARACTER SET utf8 NOT NULL,
`smallest` varchar(255) CHARACTER SET utf8 NOT NULL,
`email` varchar(40) CHARACTER SET utf8 NOT NULL,
`ref` varchar(15) NOT NULL,
PRIMARY KEY (`sid`),
KEY `gender` (`gender`),
KEY `fbid` (`fbid`)
) ENGINE=MyISAM AUTO_INCREMENT=7595 DEFAULT CHARSET=latin

---- structure of picture table ---

CREATE TABLE `picture` (
 `fbid` bigint(50) NOT NULL,
 `pic_id` int(11) NOT NULL AUTO_INCREMENT,
 `pic_location` varchar(255) NOT NULL,
 `hide` int(1) NOT NULL,
 `small` varchar(255) NOT NULL,
 `userhide` int(1) NOT NULL,
 `likes` int(10) NOT NULL,
 `hot` int(1) NOT NULL,
 PRIMARY KEY (`pic_id`),
 UNIQUE KEY `pic_location` (`pic_location`),
 UNIQUE KEY `small` (`small`),
 KEY `fbid` (`fbid`),
 KEY `hide` (`hide`),
 KEY `userhide` (`userhide`)
) ENGINE=MyISAM AUTO_INCREMENT=42749 DEFAULT CHARSET=latin1

Add `EXPLAIN` to the front of the query and post the results — Adrian Cornish, Sep 20 '12 at 04:56
@AdiMathur Tim is basically asking for the CREATE statements of the tables so the structure can be seen with the indexes — Adrian Cornish, Sep 20 '12 at 05:06
Quick look at the explain (not sure because of formating alignment) but basically you are getting 1700 rows * 7496 rows - which seems a lot — Adrian Cornish, Sep 20 '12 at 05:08
@AdrianCornish - Could you please tell me the SQL statement for that ? — Yahoo, Sep 20 '12 at 05:16
@AdiMathur http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html — Tim G, Sep 20 '12 at 05:22
@TimG - you mean the ``pic_id`` or `sid` which is currently `int(11)` should be unsigned ? :) — Yahoo, Sep 20 '12 at 05:46
auto incrementing fields will never be < 0 due to the nature of auto increment. you're accidentally restricting the maximum value that field can hold by half. — Tim G, Sep 20 '12 at 05:50

score 4 · Accepted Answer · edited May 23 '17 at 12:21

4

try something like this:

SELECT pic_id, small
FROM picture
INNER JOIN user ON ( picture.fbid = user.fbid and user.gender='female' )
WHERE hide =0
AND userhide =0
ORDER BY  `picture`.`pic_id` ASC 
LIMIT 1500 , 200

I put gender in the join because a query will not return rows that don't have a match on an inner join.

You should also read this stack overflow topic

EDIT:

make sure you have indexed the following fields:

picture.fbid
user.fbid
user.gender
picture.hide
picture.userhide

edited May 23 '17 at 12:21

Community

1
1

answered Sep 20 '12 at 04:59

Tim G

1,812
12
25

Thanks , It is now 5 seconds. Is this fine or should it be more faster ? – Yahoo Sep 20 '12 at 05:10
1

post another explain for this query. :) 5 seconds seems slow to me still. – Tim G Sep 20 '12 at 05:11
Please have a look . I updated the Question with the `CREATE TABLE `picture` ` values – Yahoo Sep 20 '12 at 05:33
I think the Indexing was also a problem , Its giving now `( 200 total, Query took 0.0138 sec) ` :) I hope the structure that I posted up is correct :) PS: Accepting Answer , Thank you :) – Yahoo Sep 20 '12 at 05:41
.138 seconds sounds more like it. :) glad to help – Tim G Sep 20 '12 at 05:43

score 1 · Answer 2 · answered Sep 20 '12 at 04:58

1

Try using a join instead:

SELECT p.pic_id, p.small
FROM picture p
INNER JOIN fbid f USING ( fbid )
WHERE p.hide =0
AND p.userhide =0
AND f.gender = 'female'
ORDER BY  `picture`.`pic_id` ASC 
LIMIT 1500 , 200

answered Sep 20 '12 at 04:58

Andrew Jackman

13,781
7
35
44

SQL select query taking long time

2 Answers2