Announcement

Announcement Module
Collapse
No announcement yet.

Query optimization

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

  • Query optimization

    Sorry for this question, but Im not a mysql performance guru. Please help me to optimize this query:


    SELECT a.SESSION_ID, a.DATE_CREATED, a.IP, a.AID, a.ANONYMOUS_FLAG, a.UID, a.NICKNAME FROM user_access a WHERE a.DATE_CREATED >= 1173206430 -- online timeout AND a.AID = 'karostesti' AND a.DATE_CREATED = (SELECT MAX(b.DATE_CREATED) FROM user_access b WHERE b.SESSION_ID = a.SESSION_ID ) GROUP BY 1 ORDER BY 2 DESC;


    Additional information
    Output from explain
    1 PRIMARY a ref user_access_I_1,user_access_FI_1,aid_date_created_ index user_access_FI_1 32 const 1 Using where; Using temporary; Using filesort
    2 DEPENDENT SUBQUERY b ref session_id_index session_id_index 194 community.a.session_id 4

    Table definition

    CREATE TABLE `user_access` ( `id` int(11) NOT NULL default '0' COMMENT 'User Access ID', `session_id` varchar(64) NOT NULL default '' COMMENT 'Session ID', `ip` varchar(15) NOT NULL default '' COMMENT 'IP', `aid` varchar(10) NOT NULL default '' COMMENT 'Account ID', `anonymous_flag` int(11) NOT NULL default '0' COMMENT 'Anonymous Flag', `uid` int(11) default NULL COMMENT 'Optional User ID', `nickname` varchar(40) default NULL COMMENT 'Optional User Nickname', `new_visit_flag` int(11) NOT NULL default '0' COMMENT 'New Visit Flag', `date_created` int(11) NOT NULL default '0' COMMENT 'Date Created', PRIMARY KEY (`id`), KEY `user_access_I_1` (`date_created`), KEY `user_access_FI_1` (`aid`), KEY `user_access_FI_2` (`uid`), KEY `session_id_index` (`session_id`), KEY `aid_date_created_index` (`aid`,`date_created`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='User Access'



    This query output is used to display online users in a forum. Please help me to optimize it.

  • #2
    I think you just need to do it different way.
    Why not to store online users separately, especially if you're to show them on about every page ?

    Comment


    • #3
      Thank you, Peter! The other way requires a big architecture change. It is not critical, but this query is logged in slow-query-log file and may be that there is a way, how to optimize it (using other index or something like that)

      Comment


      • #4
        Is it possible to drop this index: user_access_FI_1 ?
        Index for AID already exists in aid_date_created_index

        Comment

        Working...
        X