MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing index use in select statement
Posted by: Andrey Kondakov
Date: April 15, 2007 07:23AM

Hi,

I have a table where I keep all users' HTTP sessions against particular servers, say there are around 200 distinct servers in the table. Each session row contains session start time and end time defined as bigint as well as datetime field for partitioning purposes.
Now I would like to get all sessions for particular server within defined time interval.
If I use datetime field as time criterion for the search, it works nice. To my sorrow, not all select statements can contain datetime in where clause that's why some of them need to use bigint fields as time interval conditions. In this case I have much worse timing for select statements. I still hope that it is possible to optimize such selects, any hint will be appreciated.
For example, select with using of bigint timestamp as time interval criterion looks like

mysql> set @a = 1176411692282 + 60*60*1000;
mysql> select serverName from session_summary where ((startTime between 1176411692282 and @a) and (endTime >=0)) and serverName = '10.0.0.100';

The following is output of show index from statement for mentioned table.

mysql> show index from session_summary;
+-----------------+------------+-------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+-------
--+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Commen
t |
+-----------------+------------+-------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+-------
--+
| session_summary | 0 | PRIMARY | 1 | GUID | A | 1163062 | NULL | NULL | | BTREE |
|
| session_summary | 0 | PRIMARY | 2 | sessionDateTime | A | 1163062 | NULL | NULL | | BTREE |
|
| session_summary | 1 | DATES_INDEX | 1 | startTime | A | 1163062 | NULL | NULL | YES | BTREE |
|
| session_summary | 1 | DATES_INDEX | 2 | endTime | A | 1163062 | NULL | NULL | YES | BTREE |
|
| session_summary | 1 | ServerNameIndex | 1 | serverName | A | 982 | NULL | NULL | YES | BTREE |
|

Show create table may be useful as well

session_summary | CREATE TABLE `session_summary` (
`GUID` binary(16) NOT NULL,
`applicationID` smallint(5) unsigned DEFAULT NULL,
`sessionDateTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`startTime` bigint(20) unsigned DEFAULT NULL,
`endTime` bigint(20) unsigned DEFAULT NULL,
`serverName` varchar(50) DEFAULT NULL,
PRIMARY KEY (`GUID`,`sessionDateTime`),
KEY `DATES_INDEX` (`startTime`,`endTime`),
KEY `ServerNameIndex` (`serverName`)
) engine=innodb

Your ideas are welcome.

Thank you,

Andrey

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing index use in select statement
6164
April 15, 2007 07:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.