MYSQL server running query very slow on 1 Million records

Question

I am using MYSQL to manage book library data, here is my main table

CREATE TABLE `book` (
`id` int(11) NOT NULL,
`bookId` int(11) DEFAULT NULL,
`pageNum` smallint(6) DEFAULT NULL,
`pageData` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now when I run this query

SELECT c.id, c.bookId,c.pageNum, c.pageData, o.BookName FROM book c  left 
join kotarbooks o on c.bookId=o.BookId WHERE pageData like '%[Search Word]%'"

It takes about 3 minutes

and when I run query

SELECT * FROM book WHERE bookid=[bookid] AND pageNum=[pageNumber]

it takes about 2 minutes Is there any idea to speed up these queries? Thanks a lot

No indexes? No foreign keys? What is `id` and why do you need it as well as `bookId`? — Lightness Races in Orbit, May 29 '19 at 16:24

score 0 · Accepted Answer · answered May 29 '19 at 16:26

I run query SELECT * FROM book WHERE bookid=[bookid] AND pageNum=[pageNumber] it takes about 2 minutes

You could create index to speed up filtering:

CREATE INDEX idx ON book(bookid, pageNum);

As for WHERE pageData like '%[Search Word]%' is not so trivial because you are searching using '%' at the beginning and it makes a query not SARGable.

score 0 · Answer 2 · answered May 29 '19 at 16:28

The LIKE expression:

WHERE book.pageData LIKE '%[Search Word]%'

is not sargable, and therefore any index on the book table likely would not be used by MySQL. So, we can instead approach a strategy of a full table scan on the book table along with an index on the kotarbooks table:

CREATE INDEX kotaridx ON kotarabooks (BookId, BookName);

The inclusion of the BookId column should help the join, and BookName covers this column, since it appears the select clause.

MYSQL server running query very slow on 1 Million records

2 Answers2