Optimize mysql query

MySQL & MariaDB Other MySQL® Questions
1

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.

2

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.

3

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

4

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?

5

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.

6

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;

7

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.