Bug #35223 MyISAM to falcon convertion with NULL values and UNIQUE KEY takes too long
Submitted: 11 Mar 2008 16:02 Modified: 26 May 2010 17:49
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S5 (Performance)
Version:6.0.4a OS:Any
Assigned to: Christopher Powers CPU Architecture:Any
Tags: F_PERFORMANCE, qc
Triage: Triaged: D3 (Medium) / R3 (Medium) / E2 (Low)

[11 Mar 2008 16:02] jocelyn fournier
Description:
Hi,

When trying to convert a MyISAM table to falcon with a field containing a lot of NULL values and an UNIQUE KEY, the conversion takes age.
Without the UNIQUE KEY, performances are ok.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (`a` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(20) DEFAULT NULL,  PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 PACK_KEYS=1;
DROP PROCEDURE IF EXISTS fill_table;
DELIMITER //
CREATE PROCEDURE fill_table()
BEGIN
  DECLARE v1 INT DEFAULT 1;
  WHILE v1 < 10000 DO
    INSERT INTO t1 (a) VALUES (v1);
    SET v1 = v1 + 1;
  END WHILE;
END
//
DELIMITER ;
CALL fill_table();
ALTER TABLE t1 ENGINE=falcon;
Query OK, 9999 rows affected, 1 warning (39.98 sec)
Records: 9999  Duplicates: 0  Warnings: 0

no1dev.mysql> ALTER TABLE t1 ENGINE=MyISAM;
Query OK, 9999 rows affected, 1 warning (0.53 sec)
Records: 9999  Duplicates: 0  Warnings: 0

no1dev.mysql> ALTER TABLE t1 DROP KEY b;
Query OK, 9999 rows affected (0.08 sec)
Records: 9999  Duplicates: 0  Warnings: 0

no1dev.mysql> ALTER TABLE t1 ENGINE=falcon;
Query OK, 9999 rows affected, 1 warning (0.15 sec)
Records: 9999  Duplicates: 0  Warnings: 0

Regards,
  Jocelyn
[11 Mar 2008 21:02] Miguel Solorzano
Thank you for the bug report.

mysql> ALTER TABLE t1 ENGINE=falcon;
Query OK, 9999 rows affected (50.86 sec)
Records: 9999  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ENGINE=MyISAM;
Query OK, 9999 rows affected (1.29 sec)
Records: 9999  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 DROP KEY b;
Query OK, 9999 rows affected (0.25 sec)
Records: 9999  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ENGINE=falcon;
Query OK, 9999 rows affected (0.26 sec)
Records: 9999  Duplicates: 0  Warnings: 0

mysql>