How mysql database work flow in real time or in search request?

Question

I would like to have a question that I am facing a condition where I have to bring the result based on search query parameter and I have to implement paging in it too.

Let suppose a search string has PEPSI

I am querying it as below

Select * from product where product_title Like '%pepsi%' LIMIT 0,100

The table products have 50 million rows.

So, my question is that how MySQL will work? Is MySQL go through all the 50 million rows against the keyword PEPSI in the product table? If this is the case the query will be very slow and will take many minutes even I add pagination offset?

Do read: [Why is SELECT * considered harmful?](https://stackoverflow.com/q/3639861/2469308) — Madhur Bhaiya, Oct 07 '18 at 18:24
`LIKE %...%` will not be able to use Indexing. You may look into implementing [Full Text Search](http://www.mysqltutorial.org/mysql-full-text-search.aspx) instead. — Madhur Bhaiya, Oct 07 '18 at 18:25
`Limit` clause would ensure that query will stop scanning further rows as soon as it finds 100 rows which have **pepsi** substring in them — Madhur Bhaiya, Oct 07 '18 at 18:27
@MadhurBhaiya thank you for highlighting this. But, I have just 3 fields in my table id, product_title, product_description and I require all these columns thats why I used * here :) — Sohail Ahmed Siddiqui, Oct 07 '18 at 18:50
It should not matter; whether it is just 3 or 300. Please read the link - there are various reasons to stop using `Select *` — Madhur Bhaiya, Oct 07 '18 at 18:52

score 0 · Answer 1 · answered Oct 07 '18 at 18:27

Is MySQL go through all the 50 million rows against the keyword PEPSI in the product table?

Yes, though depending on settings it may block on the network side. That said, why should it work more than 100 hits? You ask for 0-100, so after 100 hits it is over.

If this is the case the query will be very slow and will take many minutes even I add pagination offset?

Yes, On a 30 year old computer. On a modern machine, sorry, 50 million rows are SMALL. A handfull of seconds sounds more like it. Oh, and it stops after the first 100 results, obviously. Even 100000 results is small. Please, put some decent modern machine there with SSD storage and see thigns fly.

score 0 · Answer 2 · answered Oct 07 '18 at 18:30

First optimization you can do is avoid using Select *. It fetches more columns (data) than required, hence blocking the network. Do read: Why is SELECT * considered harmful?
LIKE '%...%' will not be able to use Indexing (Like '..%' can use the indexing though). You may look into implementing Full Text Search instead.
Limit clause would ensure that query will stop scanning further rows as soon as it finds 100 rows which have pepsi substring in them. So it won't be that slow.

How mysql database work flow in real time or in search request?

2 Answers2