You have not mentioned the use of a multiple column index, for that matter, no indexes at all. I would suggest a multiple column index in the form of hidden, touser. That index form should speed up your queries and solve the problem. Mike
----- Original Message ----- From: "Fagyal Csongor" <[EMAIL PROTECTED]> To: "Dirk Schippers" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: September 14, 2004 12:24 PM Subject: [Bulk] Re: MySQL speed > Dirk, > > > Hello, > > > > For several years I am hosting a popular website using PHP and MySQL. > > As the site is growing and evolving, speed is becoming more and more > > important. > > With my latest inventions on the website, I notice that the website is > > becoming slow and I want to find out what's causing this. > > > > And this is the question that makes me totally confused: > > How is it possible that the following query: > > "SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0" with > > a key on touser can take up to 1 second (I even had moments where it > > would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to > > walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an > > ANALYSE on the table) > > > > If I think of making my own program walking trough a datafile with > > 2500 items, checking every item for the flag "hidden" I would think > > that should not take up to even 0.01 second! Of course MySQL is more > > complicated than this, but I think it still is a very big difference. > > > > More information: table messages is an MyISAM table of 48MB which has > > 67000 rows, a primary key on id, a key on touser and a key on isread > > with the following fields: id - bigint(20) autoincrement, fromuser - > > int(10) unsigned, touser - int(10) unsigned, ts - datetime, message - > > text, isread - tinyint(1) unsigned, ipnumber - varchar(20), hidden - > > tinyint(1). > > > > I experience this problem also with other tables and queries but as > > this is the most simple one, I show this one as an example. > > > > So: is it normal that the difference is so big, and that tables which > > are not so very big (I think 67000 rows, or 48MB is not that huge for > > a good database) are so hard to find rows into, keeping in mind that > > the key makes the query only walk trough 2500 rows? Or is this normal? > > Just some ideas for you. > > 1: I think you should use "CHAR" instead of "VARCHAR". Might take more > place, but using fixed length fields in general mean faster access time. > > 2: To walk through 2500 rows might take some time, as the "hidden" > fields for each rows must be fetched (IMHO). > > 3: You should use query caching. That might speed up your query > tremendously. > > 4: You can try a hack: instead of using a field do denote that a user is > not hidden, you might want to consider using some other method, e.g. > using a signed integer for userid, and storing inactive users with a > negative userid. Then you could count on users "where userid > 0". > > > Regards, > - Csongor Fagyal > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
