MySQL Bugs: #35223: MyISAM to falcon convertion with NULL values and UNIQUE KEY takes too long

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

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

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>