mysql,query
Hi List
I have a couple of slow queries listed in my slow log, but I don't know
what to do from here to make them "fast".
Here is an "EXPLAIN table":
mysql> explain w3t_Users;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------------+------------------+------+-----+---------+----------------+
| U_Username | varchar(30) | | MUL |
| |
| U_Password | varchar(32) | | |
| |
| U_Email | varchar(50) | YES | | NULL
| |
| U_Fakeemail | varchar(50) | YES | | NULL
| |
| U_Name | varchar(100) | YES | | NULL
| |
| U_Totalposts | int(9) unsigned | YES | | NULL
| |
| U_Laston | int(11) unsigned | YES | | NULL
| |
| U_Signature | text | YES | | NULL
| |
| U_Homepage | varchar(150) | YES | | NULL
| |
| U_Occupation | varchar(150) | YES | | NULL
| |
| U_Hobbies | varchar(200) | YES | | NULL
| |
| U_Location | varchar(200) | YES | | NULL
| |
| U_Bio | text | YES | | NULL
| |
| U_Status | varchar(15) | | MUL | User
| |
| U_Sort | int(4) unsigned | YES | | NULL
| |
| U_Display | varchar(10) | | |
| |
| U_View | varchar(10) | | |
| |
| U_PostsPer | int(11) unsigned | YES | | NULL
| |
| U_Number | int(9) unsigned | | PRI | NULL |
auto_increment |
| U_EReplies | char(3) | | |
| |
| U_Notify | char(3) | | |
| |
| U_TextCols | char(3) | YES | | NULL
| |
| U_TextRows | char(3) | YES | | NULL
| |
| U_Extra1 | varchar(200) | YES | | NULL
| |
| U_Extra2 | varchar(200) | YES | | NULL
| |
| U_Extra3 | varchar(200) | YES | | NULL
| |
| U_Extra4 | varchar(200) | YES | | NULL
| |
| U_Extra5 | varchar(200) | YES | | NULL
| |
| U_Post_Format | varchar(5) | | |
| |
| U_Registered | int(11) unsigned | YES | | NULL
| |
| U_Preview | varchar(5) | YES | | NULL
| |
| U_Picture | varchar(150) | YES | | NULL
| |
| U_PictureView | char(3) | YES | | NULL
| |
| U_Visible | char(3) | YES | | yes
| |
| U_PicturePosts | char(3) | YES | | NULL
| |
| U_AcceptPriv | char(3) | YES | | yes
| |
| U_RegEmail | varchar(50) | YES | | NULL
| |
| U_RegIP | varchar(15) | YES | | NULL
| |
| U_Groups | varchar(250) | YES | | -1-
| |
| U_Language | varchar(20) | YES | | NULL
| |
| U_Title | varchar(100) | YES | | NULL
| |
| U_FlatPosts | char(2) | YES | | NULL
| |
| U_TempPass | varchar(32) | YES | | NULL
| |
| U_Color | varchar(15) | YES | | NULL
| |
| U_TempRead | text | YES | | NULL
| |
| U_StyleSheet | varchar(50) | YES | | NULL
| |
| U_TimeOffset | varchar(10) | YES | | NULL
| |
| U_Privates | int(4) unsigned | YES | | 0
| |
| U_FrontPage | varchar(20) | YES | | NULL
| |
| U_ActiveThread | int(4) unsigned | YES | | NULL
| |
| U_StartPage | char(2) | YES | | cp
| |
| U_Favorites | varchar(250) | YES | | -
| |
| U_ShowSigs | char(3) | YES | | NULL
| |
| U_OnlineFormat | char(3) | YES | | NULL
| |
| U_Rating | varchar(5) | YES | | 0
| |
| U_Rates | int(4) unsigned | YES | | 0
| |
| U_RealRating | int(1) unsigned | YES | | NULL
| |
| U_PicWidth | int(4) unsigned | YES | | NULL
| |
| U_PicHeight | int(4) unsigned | YES | | NULL
| |
| U_SessionId | varchar(64) | | MUL | 0
| |
| U_Approved | char(3) | YES | | NULL
| |
| U_Palprofile | int(11) unsigned | YES | | NULL
| |
| U_updated | char(3) | | |
| |
+----------------+------------------+------+-----+---------+----------------+
63 rows in set (0.01 sec)
and here are some of the slow queries:
# User@Host: dotorguser[dotorguser] @ localhost []
# Time: 15 Lock_time: 0 Rows_sent: 10
SELECT U_Username,U_Totalposts
FROM w3t_Users
WHERE U_Number >= 2
ORDER BY U_Totalposts DESC
LIMIT 0,10
;
# User@Host: dotorguser[dotorguser] @ localhost []
# Time: 11 Lock_time: 0 Rows_sent: 10
SELECT U_Username,U_Totalposts
FROM w3t_Users
WHERE U_Number >= 2
ORDER BY U_Totalposts DESC
LIMIT 0,10
;
# Time: 021112 11:07:34
# User@Host: dotorguser[dotorguser] @ localhost []
# Time: 15 Lock_time: 0 Rows_sent: 10
SELECT U_Username,U_Totalposts
FROM w3t_Users
WHERE U_Number >= 2
ORDER BY U_Totalposts DESC
LIMIT 0,10
;
# Time: 021112 11:07:35
# User@Host: dotorguser[dotorguser] @ localhost []
# Time: 13 Lock_time: 0 Rows_sent: 10
SELECT U_Username,U_Totalposts
FROM w3t_Users
WHERE U_Number >= 2
ORDER BY U_Totalposts DESC
LIMIT 0,10
;
This application is used by one of the users on my server, so I don't
know much about what it does, but if you can give me some help on
optimizing the queries, I will appreciate it alot.
Thanks
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php