Bug #1614
Updated by Ohad Levy about 9 years ago
MySQL is not using the index for queries, I caught it doing table scans while a host is applying it's manifests: (localhost) [foreman]> explain SELECT * FROM sources WHERE value = '//darts.foo.bar/Puppet'; +----+-------------+---------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | sources | ALL | value | NULL | NULL | NULL | 140037 | Using where | +----+-------------+---------+------+---------------+------+---------+------+--------+-------------+ 1 row in set (0.00 sec) (localhost) [foreman]> show create table sources; +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | sources | CREATE TABLE `sources` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` text, PRIMARY KEY (`id`), FULLTEXT KEY `value` (`value`) ) ENGINE=MyISAM AUTO_INCREMENT=295256 DEFAULT CHARSET=latin1 | +---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) (localhost) [foreman]> show indexes from sources; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | sources | 0 | PRIMARY | 1 | id | A | 122065 | NULL | NULL | | BTREE | | | sources | 1 | value | 1 | value | NULL | NULL | NULL | NULL | YES | FULLTEXT | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) In the schema.rb: create_table "sources", :force => true do |t| t.text "value" end add_index "sources", ["value"], :name => "value" This appears to create a fulltext index for the sources.value column in foreman's db. Adding a non fulltext-type index solves this, I added an index for the first 120 characters to speed things up. (localhost) [foreman]> create index index_sources_value on sources(value(120)); Query OK, 140705 rows affected (5.46 sec) Records: 140705 Duplicates: 0 Warnings: 0 (localhost) [foreman]> explain SELECT * FROM sources WHERE value = '//darts.foo.bar/Puppet'; +----+-------------+---------+------+--------------------+--------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+--------------------+--------------+---------+-------+------+-------------+ | 1 | SIMPLE | sources | ref | sourcevalues,value | sourcevalues | 123 | const | 1 | Using where | +----+-------------+---------+------+--------------------+--------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) I love this project, wanted to contribute something back that I'd experienced today.