Make sure when you are creating the database that U_Number | int(9) unsigned | | PRI | NULL
is U_Number | numeric() | | PRI | NULL -----Original Message----- From: Petre Agenbag [mailto:internet@;boesmanland.com] Sent: Tuesday, November 12, 2002 4:27 AM To: [EMAIL PROTECTED] Subject: slow queries 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 --------------------------------------------------------------------- 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
