MySQL index for regular column and full text column
I am trying to speed up the request for below:
My table contains about 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 created two indexes using 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 two indexes be "merged"? As the above query takes 15+ seconds to execute (using only one index).
Defining the entire table:
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`
);
source to share
(
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 external
SELECT
- Saying explicitly
DISTINCT
(default) orALL
(whichever is faster) so you know what you think of deduplication versus speed. - A
LIMIT
without isORDER BY
not very significant - However, if you just want some lines, you can delete
ORDER BYs
. - Yes,
ORDER BY
andLIMIT
it needs to be repeated outside so that you can order the order correctly and limit to 10.
If you want OFFSET
, you need to complete in number, say LIMIT 50
5 pages, n out you need to go to the 5th page: LIMIT 40,10
.
source to share
Try to use UNION
instead OR
.
SELECT *
FROM (
SELECT *
FROM chrecords
WHERE company_number = 'test'
) a
UNION (
SELECT *
FROM cbrecords
WHERE MATCH (company_name,
registered_office_address_address_line_1,
registered_office_address_address_line_2)
AGAINST('test')
LIMIT 0, 10
) b
If that helps, it's because MySQL is trying to use more than one index in one subquery. This gives the query planner two queries.
You can run EXPLAIN
on each of the subqueries separately to understand their performance. UNION
just merges your results and eliminates duplicates. If you want to keep duplicates, do UNION ALL
.
Note that many single column indexes on MySQL tables are usually detrimental to performance. You should refrain from creating indexes unless they are built to support specific queries.
source to share