Announcement

Announcement Module
Collapse
No announcement yet.

Optimize mysql query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimize mysql query

    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.

    Comment


    • #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

      Comment


      • #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?

        Comment


        • #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.

          Comment


          • #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;

            Comment


            • #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.

              Comment

              Working...
              X