普通列和全文列的MySQL索引

Question

我正在尝试加快以下查询的速度:

我的表有大约 400 万条记录。

EXPLAIN SELECT  * FROM chrecords WHERE  company_number = 'test'  OR MATCH (company_name,registered_office_address_address_line_1,registered_office_address_address_line_2) AGAINST('test') LIMIT 0, 10;
+------+-------------+-----------+------+------------------+------+---------+------+---------+-------------+
| id   | select_type | table     | type | possible_keys    | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-----------+------+------------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | chrecords | ALL  | i_company_number | NULL | NULL    | NULL | 2208348 | Using where |
+------+-------------+-----------+------+------------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

我使用以下方法创建了两个索引:

ALTER TABLE `chapp`.`chrecords` ADD INDEX `i_company_number` (`company_number`);

ALTER TABLE `chapp`.`chrecords`ADD FULLTEXT(
    `company_name`,
    `registered_office_address_address_line_1`,
    `registered_office_address_address_line_2`
);

但是如何“结合”这两个索引呢？由于上述查询需要 15 秒以上的时间来执行(仅使用一个索引)。

整个表定义:

CREATE TABLE `chapp`.`chrecords` (
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `company_name` VARCHAR(100) NULL,
  `company_number` VARCHAR(100) NULL,
  `registered_office_care_of` VARCHAR(100) NULL,
  `registered_office_po_box` VARCHAR(100) NULL,
  `registered_office_address_address_line_1` VARCHAR(100) NULL,
  `registered_office_address_address_line_2` VARCHAR(100) NULL,
  `registered_office_locality` VARCHAR(100) NULL,
  `registered_office_region` VARCHAR(100) NULL,
  `registered_office_country` VARCHAR(100) NULL,
  `registered_office_postal_code` VARCHAR(100) NULL
  );

ALTER TABLE `chapp`.`chrecords` ADD INDEX `i_company_name` (`company_name`);
ALTER TABLE `chapp`.`chrecords` ADD INDEX `i_company_number` (`company_number`);
ALTER TABLE `chapp`.`chrecords` ADD INDEX `i_registered_office_address_address_line_1` (`registered_office_address_address_line_1`);
ALTER TABLE `chapp`.`chrecords` ADD INDEX `i_registered_office_address_address_line_2` (`registered_office_address_address_line_2`);

ALTER TABLE `chapp`.`chrecords`ADD FULLTEXT(
    `company_name`,
    `registered_office_address_address_line_1`,
    `registered_office_address_address_line_2`
);

Answer 1

    (
        SELECT  *
            FROM  chrecords
            WHERE  company_number = 'test' 
            ORDER BY something
            LIMIT 10
    )
    UNION DISTINCT
    (
        SELECT  *
            FROM  cbrecords
            WHERE  MATCH (company_name, registered_office_address_address_line_1,
                                        registered_office_address_address_line_2)
                   AGAINST('test')
            ORDER BY something
            LIMIT 10
    ) 
    ORDER BY something
    LIMIT 10

注意事项:

• 不需要外部 SELECT
• 明确地说出 DISTINCT(默认)或 ALL(更快)，这样您就会知道您考虑过是否需要重复数据删除，而不是速度。<
• 没有 ORDER BY 的 LIMIT 意义不大
• 但是，如果您只想查看一些行，您可以删除 ORDER BYs。
• 是的，ORDER BY 和 LIMIT 需要在外部重复，这样您才能获得正确的顺序并限制为 10。

如果你需要一个OFFSET，里面需要一个完整的计数，比如说LIMIT 50 5页，然后外面需要跳到第5页: 限制 40,10。