# Optimize mysql query **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [August 6, 2010, 4:11pm UTC](https://forums.percona.com/t/optimize-mysql-query/1472 "2010-08-06T16:11:39Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![webdev5](https://avatars.discourse-cdn.com/v4/letter/w/ac91a4/32.png) [@webdev5](https://forums.percona.com/u/webdev5)\ **Post date:** [August 6, 2010, 4:11pm UTC](https://forums.percona.com/t/optimize-mysql-query/1472/1 "2010-08-06T16:11:39Z")
I want to know which one is better indexed in the below 2 examples: Below query has index(category\_id,date\_created ) EXPLAIN SELECT postid FROM posts WHERE category\_id =53 AND approval = ‘yes’ ORDER BY date\_created DESC LIMIT 0 , 10 id select\_type table type possible\_keys key key\_len ref rows Extra 1 SIMPLE posts index category\_id date\_created 9 NULL 199 Using where Below query has one index(date\_created ), removed index (category\_id) EXPLAIN SELECT postid FROM posts WHERE category\_id =53 AND approval = ‘yes’ ORDER BY date\_created DESC LIMIT 0 , 10 id select\_type table type possible\_keys key key\_len ref rows Extra 1 SIMPLE posts index NULL date\_created 9 NULL 10 Using where =============== Which one among the two is better indexing ? The difference is rows\_examined. Should I be concerned about query execution time as its not much differnce in above two or the rows\_examined? Please guide me. Thanks. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 6, 2010, 4:43pm UTC](https://forums.percona.com/t/optimize-mysql-query/1472/2 "2010-08-06T16:43:29Z")
The first one is probably much better, depending on how many categories you have. rows is just an indication, which is always off if you use limit. Consult the query log for actual values. ---
**Author:** ![Gabriel](https://avatars.discourse-cdn.com/v4/letter/g/9de0a6/32.png) [@Gabriel](https://forums.percona.com/u/Gabriel)\ **Post date:** [August 7, 2010, 1:07am UTC](https://forums.percona.com/t/optimize-mysql-query/1472/3 "2010-08-07T01:07:35Z")
Hi Dude The first query with indexed on date and category is the best indexed query and gives the results faster than the other query which is indexed only on date created.The second query gives results irrespective of category. spector pro ---
**Author:** ![webdev5](https://avatars.discourse-cdn.com/v4/letter/w/ac91a4/32.png) [@webdev5](https://forums.percona.com/u/webdev5)\ **Post date:** [August 7, 2010, 2:00am UTC](https://forums.percona.com/t/optimize-mysql-query/1472/4 "2010-08-07T02:00:45Z")
Thanks gmouse and Gabriel for replying. Even I think that the first query is better than the second. But I have one more question. If I want to retrieve 100 records from below queries for another category I get the following: index(category\_id,date\_created ) EXPLAIN SELECT postid FROM posts WHERE category\_id =33 AND approval = ‘yes’ ORDER BY date\_created DESC LIMIT 0 , 100 id select\_type table type possible\_keys key key\_len ref rows Extra 1 SIMPLE posts ref category\_id category\_id 5 const 2311 Using where; Using filesort * * * index(date\_created ) EXPLAIN SELECT postid FROM posts WHERE category\_id =33 AND approval = ‘yes’ ORDER BY date\_created DESC LIMIT 0 , 100 id select\_type table type possible\_keys key key\_len ref rows Extra 1 SIMPLE posts index NULL date\_created 9 NULL 100 Using where * * * The first query is Using where; Using filesort, key:category\_id and examining 2311 rows which might be more for some other category. The second query is Using where; , key:date\_created and examining 100 rows. Now what is your opinion? does it depend on the limit records? which query is more efficient? ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 7, 2010, 5:17am UTC](https://forums.percona.com/t/optimize-mysql-query/1472/5 "2010-08-07T05:17:53Z")
Could you give the output of ‘CREATE TABLE posts’? As you can see from key\_len in your first post, both use exactly the same number of bytes from the index. In your last post, I can’t see why it says filesort in your explain output. ---
**Author:** ![webdev5](https://avatars.discourse-cdn.com/v4/letter/w/ac91a4/32.png) [@webdev5](https://forums.percona.com/u/webdev5)\ **Post date:** [August 7, 2010, 5:34am UTC](https://forums.percona.com/t/optimize-mysql-query/1472/6 "2010-08-07T05:34:45Z")
CREATE TABLE `posts` ( `postid` int(100) NOT NULL AUTO\_INCREMENT, `post_title` text, `author` varchar(100) DEFAULT NULL, `post_desc` longtext, `status` varchar(50) DEFAULT NULL, `category_id` int(100) DEFAULT NULL, `date_created` datetime DEFAULT NULL, `visits` int(50) NOT NULL DEFAULT ‘0’, `approval` varchar(50) NOT NULL DEFAULT ‘’, `imgname` varchar(100), `imgdesc` varchar(100) NOT NULL DEFAULT ‘’, `layout` varchar(50) DEFAULT ‘lay1’, `approved_by` varchar(50) NOT NULL DEFAULT ‘0’, PRIMARY KEY (`postid`), KEY `category_id` (`category_id`), KEY `date_created` (`date_created`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [August 10, 2010, 7:12am UTC](https://forums.percona.com/t/optimize-mysql-query/1472/7 "2010-08-10T07:12:10Z")
My guess is that you get a filesort due to that you have forgotten to create the index(category\_id, date\_created). But I suggest that you should create the index(category\_id, approval, date\_created) since it looks like you always want to have approval=‘yes’ in your queries. That way all conditions for the query are met by the index without having to fetch the approval data from the table.