mysql full text search with left join is slow

Question

I have 2 table: words with word examples. There is appx 70k words with ~1 million examples. Im trying to add admin with search and paginated results but running into queries taking 6-7 seconds to produce results.
Am I doing something wrong?

Here's the schema:

```
CREATE TABLE IF NOT EXISTS words (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    word VARCHAR(191) DEFAULT NULL,
    fl VARCHAR(191) DEFAULT NULL,
    UNIQUE KEY (word, fl),
    FULLTEXT (word)
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS examples (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    fk_words_id BIGINT UNSIGNED,
    src VARCHAR(50) DEFAULT NULL,
    example VARCHAR(400) DEFAULT NULL,
    cite_author VARCHAR(400) DEFAULT NULL,
    cite_publication VARCHAR(400) DEFAULT NULL,
    cite_title VARCHAR(400) DEFAULT NULL,
    FULLTEXT (example,cite_author,cite_publication),
    PRIMARY KEY (id),
    FOREIGN KEY (fk_words_id) REFERENCES words (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
```

My queries for paginated results look like this:

SELECT DISTINCT 
a.id,
a.word,
a.fl,
a.updated
FROM words AS a
LEFT JOIN examples AS b
ON a.id = b.fk_words_id
WHERE (a.word = :search_string OR MATCH(b.example,b.cite_author,b.cite_publication) AGAINST(+'some word' IN BOOLEAN MODE))
ORDER BY id asc
LIMIT 0,20;

Is there a way I improve performance here?

What's the significance of 191? That's a very odd arbitrary column length. — tadman, Nov 10 '17 at 17:59
@tadman maximal number of utf8 4 byte charcters: https://stackoverflow.com/questions/20123824/how-can-i-alter-an-indexed-varchar255-from-utf8-to-utf8mb4-and-still-stay-unde — Dannyboy, Nov 10 '17 at 18:02
MySQL 5.7 doesn't have this limitation, so if you're having trouble, worth upgrading. — tadman, Nov 10 '17 at 18:03
Test it first, but I think you'll find 5.7 removes a lot of the really peculiar limits from older versions. Finally! Big improvements to the InnoDB engine. — tadman, Nov 10 '17 at 18:04

Paul Maxwell · Answer 1 · 2017-11-10T22:18:19.913

I suggest the following. Performing the distinct on b against one column should be faster than doing the left join then doing distinct over 4 columns.

SELECT
      a.id
    , a.word
    , a.fl
    , a.updated
FROM words AS a
LEFT JOIN (
      SELECT DISTINCT fk_words_id FROM examples
      WHERE MATCH(b.example,b.cite_author,b.cite_publication) AGAINST(+'some word' IN BOOLEAN MODE))
      ) b ON a.id = b.fk_words_id
WHERE a.word = :search_string
ORDER BY
      id ASC
LIMIT 0,20;

mysql full text search with left join is slow

1 Answers1