Hello everyone,
I'm working with MySQL 3.23.41-log developing a chat application. I need to run a
query, order the results in
descending order, then get the last 0-15 entries that are less than 20 minutes old.
The query and tables in question
are explained at the bottom of this posting. In the query shown below, the number
1054889629 indicates at 20 minute
old UNIX Timestamp.
The query below returns the correct results, but as of yet, I have been unable to get
the query to take advantage of
the indexes in place. When sorting ASC, I can get a reasonable efficient query,
however that gives me the oldest 0-15
when I need the newest. If I sort by DESC, which would give me the incorrect answer,
the query uses Filesort, which
is unacceptable for my application.
The order the fields are selected in is unimportant and can be rearranged if need be.
The WHERE and ORDER BY sections
can be change freely so long as the resultant data is the same and indexes can be
added or removed as needed. This is
the only major query being run against the database.
Ideally, I'd like the query to use index, but I'd be satisfied if it just didn't use
filesort. Can anyone help me
tighten this up?
Thanks!
Jacob
---------- Table/Query/Index Info ----------
Some field explanations:
Message_ID - Aribrary message ID
Username - Standard UNIX username
Date_Time - Date and time message was stored
Unix_Timestamp - UNIX_TIMESTAMP(Date_Time)
Sample table data is avaliable if needed.
CREATE TABLE Chat_2 (
Message_ID mediumint(8) unsigned NOT NULL auto_increment,
Username varchar(8) NOT NULL default '',
Date_Time datetime NOT NULL default '0000-00-00 00:00:00',
Message tinytext NOT NULL,
Visible enum('Y','N') NOT NULL default 'Y',
Unix_Timestamp int(11) NOT NULL default '0',
PRIMARY KEY (Message_ID),
KEY A_1 (Unix_Timestamp,Visible),
KEY A_3 (Unix_Timestamp,Visible,Username),
KEY A_2 (Unix_Timestamp,Visible,Date_Time,Username,Message_ID),
KEY A_4 (Unix_Timestamp,Visible,Unix_Timestamp)
) TYPE=MyISAM;
CREATE TABLE Users (
Username varchar(8) NOT NULL default '',
Access_Level tinyint(3) unsigned NOT NULL default '0',
Is_Active enum('Y','N') NOT NULL default 'Y',
Display_Name varchar(20) default NULL,
Picture_URL varchar(100) default NULL,
Added datetime default NULL,
Show_Icons enum('Y','N') NOT NULL default 'Y',
Clock_Format enum('24-hour','12-hour') NOT NULL default '24-hour',
Last_Modified timestamp(14) NOT NULL,
PRIMARY KEY (Username)
) TYPE=MyISAM;
mysql> EXPLAIN SELECT Date_Time, Display_Name, Chat_2.Username,
Message, Users.Picture_URL, Users.Access_Level, Chat_2.Message_ID
FROM Chat_2, Users WHERE Unix_Timestamp > 1054889629 &&
Chat_2.Visible = 'Y' && Chat_2.Username = Users.Username ORDER BY
Unix_Timestamp DESC LIMIT 0,15;
+------+------+--------+-------+-------+---------+----+--------------+
|table |type |possible|key |key_len|ref |rows|Extra |
| | | _keys | | | | | |
+------+------+--------+-------+-------+---------+----+--------------+
|Chat_2|range |A_1,A_3,|A_1 | 4|NULL | 8|where used; |
| | |A_2,A_4 | | | | |Using filesort|
| | | | | | | | |
|Users |eq_ref|PRIMARY |PRIMARY| 8|Chat_2. | 1| |
| | | | | | Username| | |
+------+------+--------+-------+-------+---------+----+--------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT Date_Time, Display_Name, Chat_2.Username, Message,
Users.Picture_URL, Users.Access_Level, Chat_2.Message_ID FROM Chat_2,
Users WHERE Unix_Timestamp > 1054889629 && Chat_2.Visible = 'Y' &&
Chat_2.Username = Users.Username ORDER BY Unix_Timestamp LIMIT 0,15;
+------+------+--------+-------+-------+--------+----+----------+
|table |type |possible|key |key_len|ref |rows|Extra |
| | | _keys | | | | | |
+------+------+--------+-------+-------+--------+----+----------+
|Chat_2|range |A_1,A_3,|A_1 | 4|NULL | 8|where used|
| | |A_2,A_4 | | | | | |
| | | | | | | | |
|Users |eq_ref|PRIMARY |PRIMARY| 8|Chat_2. | 1| |
| | | | | |Username| | |
+------+------+--------+-------+-------+--------+----+----------+
2 rows in set (0.00 sec)
--- Msg sent via [EMAIL PROTECTED] - http://mail.statisticalanomaly.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
