普通列和全文列的MySQL索引 [英] MySQL index for normal column and full text column
本文介绍了普通列和全文列的MySQL索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图加快对以下内容的查询：
我的表格有大约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 |表| |键入| possible_keys |键| key_len | ref |行|额外|
+ ------ + ------------- + ----------- + ------ + ----- ------------- + ------ + --------- + ------ + --------- + - ----------- +
| 1 | SIMPLE | chrecords | ALL | i_company_number | NULL | NULL | NULL | 2208348 |使用where |
+ ------ + ------------- + ----------- + ------ + ----- ------------- + ------ + --------- + ------ + --------- + - ----------- +
1行（0.00秒）
使用以下命令创建了两个索引：
ALTER TABLE`chapp`.`chrecords` ADD INDEX`i_company_number `（`company_number`）; （
`company_name`，
`registered_office_address_address_line_1`，
`registered_office_address_address_line_2`
）;
$
然而，如何结合这两个指数呢？由于上述查询需要15+秒才能执行（仅使用一个索引）。
整个表格定义：
< $ p $ lt; code> CREATE TABLE`chapp`.`chrecords`（
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT，
`company_name` VARCHAR（100）NULL，$ b （100）NULL，
`registered_number` VARCHAR（100）NULL，
`registered_office_care_of` VARCHAR（100）
`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`）; （
`company_name`，
`registered_office_address_address_line_1`，
`registered_office_address_address_line_2`
）;
$
解决方案
（
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
限制10
）
ORDER BY something
LIMIT 10
：
- 不需要外部
SELECT - 显式地说
DISTINCT（默认）或ALL（这会更快），以便您知道您考虑是否需要进行重复数据删除，而不是速度。 > - 没有
ORDER BY的LIMIT没有意义 - 然而，如果你只是想要看一些行，你可以删除
ORDER BYS。 - 是的
ORDER BY和LIMIT需要在外面重复，以便您可以获得正确的排序并将其限制为10。 li>
如果你需要一个 OFFSET ，那么里面需要一个完整的计数， code> LIMIT 50 5页，外部需要跳到第5页： LIMIT 40,10 p>
I'm trying to speed up a query for the below:
My table has around 4 million records.
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)
I've created two indexes using the below:
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`
);
How can "combine" the two indexes however? As the above query takes 15+ seconds to execute (only using one index).
The entire table definition:
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`
);
解决方案
(
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
Notes:
- No need for an outer
SELECT - Explicitly say
DISTINCT(the default) orALL(which is faster) so that you will know that you thought about whether dedupping was needed, versus speed. - A
LIMITwithout anORDER BYis not very meaningful - However, if you just want some rows to look at, you can remove the
ORDER BYs. - Yes the
ORDER BYandLIMITneed to be repeated outside so that you can get the ordering correct and limit to 10.
If you need an OFFSET, the the inside need a full count, say LIMIT 50 for 5 pages, the n the outside needs to skip to the 5th page: LIMIT 40,10.
这篇关于普通列和全文列的MySQL索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
