Re: MySQL MyISAM sorting query makes it slower

[Timothy Mifsud]

Hi Rick,

That count query you suggested yields the following results (after 3 minutes)

PositionCode, ct
---------------------
669312116, 2510094
452677825, 1577921
1607434195, 1030005

As can be seen, the PositionCode 669312116 is the most common, yet it is NOT one of the problematic PositionCodes (even though the execution plan would be the same).

The schema of the gameposition table follows:

DROP TABLE IF EXISTS `DB2`.`gameposition`;
CREATE TABLE `DB2`.`gameposition` (
`GameNumber` int(10) unsigned NOT NULL,
`PositionCode` int(10) unsigned NOT NULL,
KEY `IX_PositionCode` (`PositionCode`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

> Since you made GameNumber the PK, it works well with the ORDER BY. A thought... Instead of saying PRIMARY KEY(GameNumber) construct the CREATE with INDEX(...) with the var_OrderBy filling in what to index.

Unfortunately, I cannot do this, as I am getting the GameNumber (Primary Key) from the DB2.gameposition table, not the DB1.games table (which contains the other fields I want the user to sort on).

So I still do not know why certain PositionCodes take longer than others to sort, could it be some setting in the my.ini file? I have pasted it below:

[MySQLD]
port=3306
basedir=C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL51\
datadir=C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\Data
character-set-server=latin1
default-storage-engine=myisam
key_buffer_size=512M
max_allowed_packet=1M
table_open_cache=512
read_buffer_size=8M
read_rnd_buffer_size=32M
myisam_sort_buffer_size=8M
sort_buffer_size=2M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=400
old_passwords=0
max_user_connections=30
thread_cache_size=4
thread_stack=196608
binlog_cache_size=32768
net_read_timeout=30
net_retry_count=10
net_write_timeout=30
thread_concurrency=10
open_files_limit=0
event_scheduler=ON
[client]
port=3306

Thanks,
Tim