0

I've recently migrated my myisam-tables to InnoDB and some things are a bit weird for me. For starters, the indexes looks a bit different (and maybe is the reason why the new InnoDB-implementation is slow)?

The table holds 1,2m unique items.

Database: Mysql 8+, Aws RDS

CREATE TABLE `books` (
  `isbn` bigint NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;


ALTER TABLE `books`
  ADD UNIQUE KEY `isbn` (`isbn`),
  ADD KEY `created` (`created`);
ALTER TABLE `books` ADD FULLTEXT KEY `fulltext_title_text` (`title`,`text`);
ALTER TABLE `books` ADD FULLTEXT KEY `title` (`title`);
ALTER TABLE `books` ADD FULLTEXT KEY `text` (`text`);
COMMIT;

And a typical and slow query:

SELECT `b`.`isbn`,
    MATCH(`b`.`title`) 
       AGAINST(:keywords IN BOOLEAN MODE) AS `score_title`,
    MATCH(`b`.`text`) 
       AGAINST(:keywords IN BOOLEAN MODE) AS `score_text`
  FROM `books` AS `b`
  WHERE MATCH(`b`.`title`, `b`.`text`)
           AGAINST(:keywords IN BOOLEAN MODE)
  GROUP BY `b`.`isbn`
  HAVING (`score_title` + `score_text`) > 0
  ORDER BY (`score_title` * 1.5) +
           (`score_text`) DESC
  LIMIT 0, 20

---
:keywords = "+Administrator*"

When inspecting the new table, I can see this about my indexes: enter image description here

Also: enter image description here

Any suggestion how to speed up the query?

3
  • 1
    Does it run as fast without the GROUP BY? And get the same results? Ditto for the HAVING? How many rows match "+Administrator*"? (Remove theLIMIT and use COUNT(*).)
    – Rick James
    Jan 7 at 17:22
  • Hi Rick! How do you mean 'use COUNT(*)' ? Can you give me any example with abouve query? I understand the rest. My initial thought is that something is wrongly setup with the indexes..
    – Oakleaf
    Jan 7 at 19:20
  • You're surely wasting time trying to group by a unique key. I think you can forget about the having too, since it corresponds to the where clause. I think @RickJames meant that you could do a "select count(*)" iso. individual rows, to know the number of books matching. Jan 7 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.