Google Answers Logo
View Question
 
Q: Optimizing a MySQL Query ( No Answer,   0 Comments )
Question  
Subject: Optimizing a MySQL Query
Category: Computers > Programming
Asked by: leotune-ga
List Price: $10.00
Posted: 07 Jun 2003 09:46 PDT
Expires: 07 Jun 2003 19:23 PDT
Question ID: 214380
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.

Then answer I'm looking for is either: a) an optimized query that uses
as few rows as possible and uses index, b) if that is not possible, a
optimized query that uses as few rows as possible and avoids filesort
or temporary, or c) an explanation of why the current results are the
best-case.  If this is the best-case, suggestions of how to improve my
design would be appreciated.  Likewise, if it is possible to optimize
it further, I would appreciate knowing what I was doing wrong.

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|    |          |
+------+------+--------+-------+-------+--------+----+----------+
Answer  
There is no answer at this time.

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy