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]
