Does anyone know how Innobase would handle this query? If it would lock all of the rows within the table, or attempt to find all of the rows within the table instead of just finding the first X in my LIMIT and locking those and updating those? I have a table with 1 million rows as my example WHERE c1 is equal to 5 (c1 = 5) and when I run this query, it takes roughly 16 seconds, which it should not because of the index:
UPDATE t1 SET c1 = 1 WHERE c1 = 5 LIMIT 5; This should go rather fast, because SELECT * from t1 WHERE c1 = 5 LIMIT 5 returns the 5 rows in 0.x seconds, however when I update it takes an extended period of time. Creation Statement: CREATE TABLE `t1` ( `id` int(11) unsigned NOT NULL default '0', `c1` tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `c1` (`c1`) ) TYPE=InnoDB Example select mysql> select id, c1 from t1 where c1 = 5 limit 5; +------+-----+ | id | c1 | +------+-----+ | 1433 | 5 | | 1434 | 5 | | 1435 | 5 | | 1436 | 5 | | 1437 | 5 | +------+-----+ 5 rows in set (0.00 sec) Example update: mysql> update t1 set c1 = 1 where c1 = 5 LIMIT 5; Query OK, 5 rows affected (15.69 sec) Rows matched: 5 Changed: 5 Warnings: 0 Example count: mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.90 sec) Version: mysql> show variables like 'version'; +---------------+--------------+ | Variable_name | Value | +---------------+--------------+ | version | 3.23.49a-max | +---------------+--------------+ 1 row in set (0.01 sec) database, mysql, query, update, select, filter nickg --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
