GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

different explain results for same query

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

  • different explain results for same query

    HI,

    i am doing explain for a query and getting different results every time, i run explain 5-6 times continuously and get two different output.

    see below:

    mysql> explain SELECT ID, MID, VIEWED, TYPE FROM MUSER WHERE ID > ? AND VIEWEDBYSIP <> 2 AND ATTRIBUTE !='DELETED' and USERID = ? LIMIT 3\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: MUSER
    type: ref
    possible_keys: PRIMARY,muser_1
    key: muser_1
    key_len: 4
    ref: const
    rows: 271504
    Extra: Using where


    in next 10 seconds the same explain command showing different results

    mysql> explain SELECT ID, MID, VIEWED, TYPE FROM MUSER WHERE ID > ? AND VIEWEDBYSIP <> 2 AND ATTRIBUTE !='DELETED' and USERID = ? LIMIT 3\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: MUSER
    type: index_merge
    possible_keys: PRIMARY,muser_1
    key: muser_1,PRIMARY
    key_len: 4,4
    ref: NULL
    rows: 1504
    Extra: Using intersect(muser_1,PRIMARY); Using where
    1 row in set (0.03 sec)


    the database is indexed on id and userid.

    What the problem is, sometimes this query took 2-5 minute and sometimes it took few seconds.

    can anybody explain why is this happening?

    -Yogesh

  • #2
    The issue is that InnoDB's estimated statistics vary (they are only estimates, and there is some randomness) so sometimes the optimizer is choosing one plan, sometimes another. You might have to use STRAIGHT_JOIN or some other hint to prevent this flapping back and forth.

    Comment


    • #3
      i use force index ( 2 indexes ) to minimize row scan

      SELECT ID, MID, VIEWED, TYPE FROM MUSER force index (PRIMARY,muser_1) (WHERE ID > ? AND VIEWEDBYSIP <> 2 AND ATTRIBUTE !='DELETED' and USERID = ? LIMIT 3\G

      in this case index selection is selective i.e. sometimes it choose index1 or index2 and sometimes both the indexes, is there any way to force mysql always use both the indexes?

      -Yogesh

      Comment


      • #4
        I don't think you have a good index on the table for this query. The index_merge query plan is really not very good. Please paste SHOW CREATE TABLE.

        Comment


        • #5
          CREATE TABLE `messagesforuser` (
          `MessageId` int(10) unsigned NOT NULL,
          `UserId` int(10) unsigned NOT NULL,
          `HasBeenViewed` tinyint(1) unsigned NOT NULL DEFAULT '0',
          `ViewedTime` datetime DEFAULT '0000-00-00 00:00:00',
          `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
          `ClientTransaction` int(10) unsigned NOT NULL DEFAULT '0',
          `MessageAttribute` set('SAVE','DELETE','DELETED','TOBEDELETD') NOT NULL DEFAULT 'DELETE',
          `hasbeenviewedbysip` tinyint(1) unsigned DEFAULT '0',
          `messageType` set('OTHER','IM') NOT NULL DEFAULT 'OTHER',
          PRIMARY KEY (`Id`),
          KEY `FK_messagesforuser_1` (`UserId`),
          KEY `FK_messagesforuser_2` (`MessageId`)
          ) ENGINE=InnoDB AUTO_INCREMENT=561207568 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 942080 kB; (`UserId`) REFER `otsdb_2_1/userinfo' |


          Query with complete table and column name is ( i changed column name in last replies)

          SELECT ID, MESSAGEID, HASBEENVIEWED, MESSAGETYPE FROM MESSAGESFORUSER where (USERID = ? AND ID > ?) AND HASBEENVIEWEDBYSIP != 2 AND MESSAGEATTRIBUTE !='DELETED'LIMIT 3


          Table size is 40GB

          Stats for 1 hr at peak time are:
          Table Name select insert update delete

          messagesforuser 895906 184251 265796 133310



          -Yogesh

          Comment


          • #6
            A compound index on (UserId, Id) should improve things for you:

            ALTER TABLE messagesforuser ADD INDEX mf_ix_userid_id(UserId, Id);

            Comment


            • #7
              Creating a new index on big table of size 40 GB would take 4-5 hrs and need database downtime.
              However, i will check it by creating compound index on other production like database.

              thank you for the reply.

              Comment


              • #8
                I agree that the compound index "should" help. But it depends on cardinality. Here is a trick I sometimes use:

                http://www.mysqlperformanceblog.com/2009/06/05/a-rule-of-thu mb-for-choosing-column-order-in-indexes/

                Note that in this example, because of the > operator, sterin71 is right -- the columns have to come in that order. The trick in the above URL is just to show how you can assess the cardinality to determine how many columns would benefit you.

                Comment

                Working...
                X