Query optimization

MySQL & MariaDB Other MySQL® Questions
1

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 ?

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)

4

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