Hi, Philip, RE: > What is the EXPLAIN output of each?
OK, first I naively typed: explain create table test2 select * from TEST where MMi < 9000; but of course, this does not work. The simple select that uses MMi_m as index (and takes up to an hour): mysql> explain select * from TEST where MMi_m < 9000; +----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+ | 1 | SIMPLE | TEST | range | MMi_m | MMi_m | 3 | NULL | 406649 | Using where | +----+-------------+-------+-------+---------------+-------+---------+------+--------+-------------+ The select with ignoreing the index (takes only 11 minutes) mysql> explain select * from TEST ignore key (MMi_m) where MMi_m < 9000; +----+-------------+-------+------+---------------+------+---------+------+-----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-----------+-------------+ | 1 | SIMPLE | TEST | ALL | NULL | NULL | NULL | NULL | 470992970 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-----------+-------------+ > Have you tried > analyze table x; > optimize table x; Not yet. As regards "optimize table", I thought it would not make too much sense, because: "OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns)." But I have just uploaded this table from ASCII, and made no changes. Nevertheless, I will give a try, maybe there is some feature of "OPTIMIZE TABLE" I don't know of. What did you think of? > Is it MyISAM or Innodb ? MyISAM. I'll keep you posted. I am very curious about how this can be resolved. Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
