Edit Question - KnowledgeHub

Title Body

I have a partitioned table in MySQL that looks like this: CREATE TABLE `table1` ( `id` bigint(19) NOT NULL AUTO_INCREMENT, `field1` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `field2_id` int(11) NOT NULL, `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`,`created_at`), KEY `index1` (`field2_id`,`id`) ) ENGINE=InnoDB AUTO_INCREMENT=603221206 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITION BY RANGE (to_days(created_at)) (PARTITION p_0 VALUES LESS THAN (730485) ENGINE = InnoDB, ..... lots more partitions ..... PARTITION p_20130117 VALUES LESS THAN (735250) ENGINE = InnoDB) */; And this is a typical SELECT query on the table: SELECT field1 from TABLE1 where field2_id = 12345 and id > 13314313; Doing an explain on it, MySQL sometimes decides to use PRIMARY instead of index1. This seems to be pretty consistent when you do a first explain. However, after a few repeated explains, MySQL finally decides to use the index. The problem is, this table has millions of rows, and inserts and selects are hitting it on the order of several times per second. Choosing the wrong index was causing these SELECT queries to take up to ~40 seconds, instead of sub second times. Can't really schedule downtime, so I can't run an optimize on the table (because of the size, it would probably take a long time), and not sure it would help in this case anyway. I fixed this by forcing the index, so it looks like this: SELECT field1 from TABLE1 FORCE INDEX (index1) WHERE field2_id = 12345 and id > 13314313; We're running this on MySQL 5.1.63, which we can't move away from at the moment. My question is, why is MySQL choosing the wrong index? And is there something that can be done to fix it, besides forcing the index on all queries? Is partitioning confusing the InnoDB engine? I've w

Tags (comma-separated) Cancel