Re: myisamchk sorting records descending

[JJWR]

>If you have a suitable key you might try using a where in the select and processing
>many batches, perhaps one for each letter of the alphabet.
I am not sure if I understood well. I can divide the table in various parts concerning a key, but as far as I know I can not query the database using 2 keys (this and the full text key), also I think it will be very slow to join all the results and sort them…


>If you can keep the size of the records within the amount of RAM you have left to the
>operating system for caching MyISAM data that could be significantly faster.
Unfortunately I can’t, the server has only 512 MB RAM, I can not buy more RAM since this is a rented server… the database size is 560 MB of data + 1.2 GB indexes.

>If it's worth it for your application you could do something like sorting on a slave with
>replication stopped then turning on replication so the slave catches up and finally
>copying the files to the main system. Not a nice approach but it's one way to get things
>done if the benefit is worth it.
This is stand alone rented server on the Internet, I would do very slow replication with a server at my office, but since I can not transfer more than a limited number of MB at month this is not viable.

>If you say more about the size of the data and indexes for this table on disk and say
>how much RAM you have and your key_buffer_size and other my.cnf settings that
>might povide some possibility for suggesting an optimization in those settings.
Thanks I provide you some data, I hope you can help me.
RAM: 512 MB
Operating system: Windows 2003
Mysql configured with UTF8
my.ini variables:
key_buffer_size=190M
query_cache_size=25M
read_buffer_size=4M
read_rnd_buffer_size=6M
sort_buffer_size=16M
table_cache=256
tmp_table_size=26M
myisam_max_sort_file_size=20M
myisam_max_extra_sort_file_size=1M
myisam_sort_buffer_size=51M

The big table has 10 fields: 4 integers, 1 timestamp, 3 VARCHAR(15), 1 VARCHAR(40), 1 TEXT (fulltext indexed)
The TEXT field use to have less than 100 characters.
Besides the full text index it has indexes for 1 varchar and for 3 integer fields.
The typical query uses the full text index, Limit clause with about only 25 rows, and one of the indexed fields to sort (but I can see MYSQL uses filesorting instead of using the key, I tested it in Mysql 4.1 and 5.0 with the same behaviour)

The querys without the sort are very very fast (the limit clause also helps a lot), but when I add the sorting the performace falls a lot.

>If it wasn't for the fulltext part I'd suggest consideing InnoDB with a primary/clustered
>key which matches the order you want, because InnoDB would automatically store the
>records in the right order. Fulltext search is planned for InnoDB but not for some time.
That would be perfect… until then, the only short term solution I can imagine is to modify the myisamchk tool to allow reverse sorting...

Thanks for your interest !