Re: Queries taking 60 seconds+
Right thanks for all the tips the 3 column index has done the job, queries coming back in 0.7 secconds now which is just the job before they get cached. Don't know how I missed that one as it was abovious...i even tried countyid and old...forgot about price.. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
It is a property of Mysql that such a query will benefit greatly from a composite index. So I would not consider anything else without having tried this. Am Thursday 11 November 2004 16:29 schrieb John Smith: > On Thu, 2004-11-11 at 14:59, Victor Pendleton wrote: > > If you build the composit indexes as suggested, does your performance > > improve? > > Erm, do you think it would? Its just that with such a large table and it > being compressed it takes ages? -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) ZÃlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-7786 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
On Thu, 2004-11-11 at 15:51, mos wrote: > John, > Create a second table (MyISAM) but this time don't use compression > on the table. > > create table newtable select * from oldtable; > Right will run that just now, good idea...just have to avoid the wife as no doubt it will bog the site down and she has a production site on the same server ;-) > Create the compound index as someone else had suggested using Alter Table. > > Repeat the query and it should return 9000 rows in a couple of seconds. We > have tables with tens of millions of rows in them and doing an index > retrieval on a few thousand rows is very fast. The problem is either the > compression or index. Will report back on success etc Cheers John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
On Thu, 2004-11-11 at 15:45, Jigal van Hemert wrote: > `price` is still in the ORDER BY, so removing it only from the WHERE clause > will not help really. No the query I ran didn't have an order by clause (sorry if the one I pasted did..) > - create an INDEX on the columns in the WHERE clause _and_ ORDER BY / GROUP > BY, etc. This is the only way to ensure that all data is retrieved using an > INDEX OK, ta. > - the DESC direction will be slower than ASC (but you'll probably need it > anyway) Ah right, I do need that but I will set the default to ASC > - the extra speed you could gain from the LIMIT will be removed by the > SQL_CALC_FOUND_ROWS option. This option makes sure that the query will be > executed as if the limit was not present to calculate the number of rows in > the entire result set. This is really for formating on the site and I suppose isn't really needed, I used to run 2 queries until I found that option. I use it to work out page numbers though, but for the beta search until I can prove I can make money out of this I will remove it. > - run OPTIMIZE TABLE regularly to help MySQL optimize execution paths; the > cardinality of the indexes are used to optimize the execution path. I have only done about 160-170 queries since I ran: myisamchk -rq --sort-index --analyze On the table, would this make any difference so soon? > All sites where huge result sets are possible will limit the set no matter > what. Sites like google _estimate_ the number of results. > You could also retrieve the id's of the desired records (with a maximum of > say 500 records) and store the id's in e.g. session data. Paging through the > results will only require you to retrieve the data of 10 or 20 records at a > time. You've lost me there, its late in the working day so that might be the problem ;-) Cheers John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
At 07:52 AM 11/11/2004, you wrote: Afternoon All, The table is read only for most of the day and will get updated (once I get the queries down to an acceptable level) nightly...if that helps. Any suggestions or further tips would be very helpful as its taken me months to get my code to input the data in the way I need its now trying to get the data back out that is the problem. Thanks John John, Create a second table (MyISAM) but this time don't use compression on the table. create table newtable select * from oldtable; Create the compound index as someone else had suggested using Alter Table. Repeat the query and it should return 9000 rows in a couple of seconds. We have tables with tens of millions of rows in them and doing an index retrieval on a few thousand rows is very fast. The problem is either the compression or index. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
From: "John Smith" <[EMAIL PROTECTED]> > On Thu, 2004-11-11 at 14:36, Philippe Poelvoorde wrote: > > Hi, > > > > could you try adding a key with > > ALTER TABLE properties ADD INDEX(countyid,old,price); > > It could maybe help getting less rows at a time. > > I dropped the old and price for the where clause and the number of rows > scanned were the same as without the limit which is good so I am > guessing the 3 coloum index will do the same. `price` is still in the ORDER BY, so removing it only from the WHERE clause will not help really. - create an INDEX on the columns in the WHERE clause _and_ ORDER BY / GROUP BY, etc. This is the only way to ensure that all data is retrieved using an INDEX - the DESC direction will be slower than ASC (but you'll probably need it anyway) - the extra speed you could gain from the LIMIT will be removed by the SQL_CALC_FOUND_ROWS option. This option makes sure that the query will be executed as if the limit was not present to calculate the number of rows in the entire result set. - run OPTIMIZE TABLE regularly to help MySQL optimize execution paths; the cardinality of the indexes are used to optimize the execution path. All sites where huge result sets are possible will limit the set no matter what. Sites like google _estimate_ the number of results. You could also retrieve the id's of the desired records (with a maximum of say 500 records) and store the id's in e.g. session data. Paging through the results will only require you to retrieve the data of 10 or 20 records at a time. Hope this will help, Regards, Jigal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
On Thu, 2004-11-11 at 14:59, Victor Pendleton wrote: > If you build the composit indexes as suggested, does your performance > improve? Erm, do you think it would? Its just that with such a large table and it being compressed it takes ages? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
If you build the composit indexes as suggested, does your performance improve? John Smith wrote: On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote: What does the explain plan look like? id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE properties ref old,price,countyid countyid 3 const 9233 Using where; Using filesort The filesort I know is a problem but I thought I read sorting it using myisamchk by the price key helps this. Also if I remove ORDER BY and the price >=1 AND old = 0 the query still takes 46 seconds which is too long for a dynamic site that I am trying to build. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
On Thu, 2004-11-11 at 14:36, Philippe Poelvoorde wrote: > Hi, > > could you try adding a key with > ALTER TABLE properties ADD INDEX(countyid,old,price); > It could maybe help getting less rows at a time. I dropped the old and price for the where clause and the number of rows scanned were the same as without the limit which is good so I am guessing the 3 coloum index will do the same. Problem is that it still took 30+ seconds. To return 9000 rows. Not an acceptable performace really. Any other ideas? John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries taking 60 seconds+
On Thu, 2004-11-11 at 14:37, Andy Eastham wrote: > Have you got a single multi-column index on countyid, price and old, or do > you have individual indexes on each of these fields? The former would be > much better. Its a single column on countyid, when I ran a select and just used countyid = in the where clause it still took over 30 seconds. It did take back less rows without the price and old in the query (the exact number of rows it should look at) But its still 30 seconds...I would love it to be under 3 seconds. The performace I get from the 3 column index should be the same as the single column index using the single index where should it notsaves me going off for an hour or so unpacking, adding, packing etc to find out that it still takes 30 seconds. I am pretty sure its probably a hardware issue but I am hoping I am wrong...as this application is not ready to pay for its self just yet. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
Hi, could you try adding a key with ALTER TABLE properties ADD INDEX(countyid,old,price); It could maybe help getting less rows at a time. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries taking 60 seconds+
John, Have you got a single multi-column index on countyid, price and old, or do you have individual indexes on each of these fields? The former would be much better. Andy > -Original Message- > From: John Smith [mailto:[EMAIL PROTECTED] > Sent: 11 November 2004 14:15 > To: Victor Pendleton > Cc: [EMAIL PROTECTED] > Subject: Re: Queries taking 60 seconds+ > > On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote: > > What does the explain plan look like? > > > > id select_type table type possible_keys key key_len ref > rows Extra > 1 SIMPLE properties ref old,price,countyid countyid 3 const 9233 Using > where; Using filesort > > The filesort I know is a problem but I thought I read sorting it using > myisamchk by the price key helps this. > > Also if I remove ORDER BY and the price >=1 AND old = 0 the query still > takes 46 seconds which is too long for a dynamic site that I am trying > to build. > > John > > > -- > 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]
Re: Queries taking 60 seconds+
On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote: > What does the explain plan look like? > id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE properties ref old,price,countyid countyid 3 const 9233 Using where; Using filesort The filesort I know is a problem but I thought I read sorting it using myisamchk by the price key helps this. Also if I remove ORDER BY and the price >=1 AND old = 0 the query still takes 46 seconds which is too long for a dynamic site that I am trying to build. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries taking 60 seconds+
What does the explain plan look like? John Smith wrote: Afternoon All, I have the following table structure: CREATE TABLE properties ( id int(11) NOT NULL auto_increment, propid varchar(14) NOT NULL default '0', townid varchar(255) NOT NULL default '', countyid mediumint(5) NOT NULL default '0', address text NOT NULL, price int(14) NOT NULL default '0', image text NOT NULL, description text NOT NULL, link text NOT NULL, underoffer tinyint(1) NOT NULL default '0', sold tinyint(1) NOT NULL default '0', added int(14) NOT NULL default '0', `new` tinyint(1) NOT NULL default '1', old tinyint(1) NOT NULL default '0', PRIMARY KEY (id), KEY old (old), KEY `new` (`new`), KEY sold (sold), KEY underoffer (underoffer), KEY propid (propid), KEY price (price), KEY countyid (countyid), FULLTEXT KEY address (address) ) ENGINE=MyISAM Which I have ran the following commands on: myisamchk -rq --sort-index --analyze --sort-records=7 properties.MYI myisampack properties.MYI myisamchk -rq --sort-index --analyze properties.MYI It contains just over 400,000 rows and compressed is 163 Meg in size. I have just upgraded to 4.1 as well to see if I can squeeze any more performance out. This query: SELECT SQL_CACHE SQL_CALC_FOUND_ROWS address,price, image, description, link , underoffer, sold ,added ,new FROM properties WHERE countyid = 44 AND price >= 1 AND old=0 ORDER BY price desc LIMIT 100, 10; Takes 123 seconds and examins 19068 rows according to the query log, without the limit it returns 9512 rows. I am all resourced out and have spent weeks googleing and reading the docs etc. I am pretty sure returing all properties containing the countyid should not take 2 minutes and I am confused at why its examining 19068 rows also. It could be down to my server I suppose which is a "jailed" (no idea what that is its just what my hosting company call it) freebsd virtial server. I have been playing with mysqld variables in my.cnf also...these are probably all set too high...i am not really sure of the specs of my server as I don't get that info.. set-variable = query_cache_type=2 set-variable= key_buffer=300M set-variable= query_cache_size=200M set-variable= query_cache_limit=50M set-variable= max_allowed_packet=10M set-variable= table_cache=50 set-variable= sort_buffer=5M set-variable= read_rnd_buffer=5M set-variable= record_buffer=5M set-variable= tmp_table_size=64M set-variable= thread_cache=9 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=2 set-variable= ft_min_word_len=3 set-variable = long_query_time=2 log-long-format log-slow-queries = /var/log/mysqld.slow.log The table is read only for most of the day and will get updated (once I get the queries down to an acceptable level) nightly...if that helps. Any suggestions or further tips would be very helpful as its taken me months to get my code to input the data in the way I need its now trying to get the data back out that is the problem. Thanks John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
