So I have a blog posts table with a title field and I would like for users to be able to query posts via this field. For this, constructed a query where the string the user types into the search bar could be anywhere in the title field like so:
SELECT id, title, body FROM posts
WHERE title LIKE "%users_query%";My question is could indexing the title field enhance the performance of the query? My thinking is at scale when there can be millions of posts, this search would take a very long time and thus indexing the field would decrease response time.
I have taken a look at this post and this post and they say that indexing won't really make a difference. However they are quite dated so perhaps over time this has become a viable solution.
If this still won't work all too well, what could I potentially do to speed up such queries.
Side question: I am also ordering these posts by a created_at field descending. Should I index that field as well?
Thank you!
3条答案
按热度按时间disho6za1#
There are three circumstances where indexing might improve that query.
WHERE title LIKE "users_query%", then there's a chance you could use the index to pull out only the rows beginning with 'users_query.Otherwise, yes, it's not worth it.
3df52oht2#
Indexing the title probably won't help. The leading
%in your query means the search term can appear anywhere in the title, so there is no way for the database engine to seek to the search term.You can think about it like this - imagine you have the following index on some address:
| address |
| ------------ |
| 163 pExample Street |
| 276 dExample Street |
| 373 aExample Street |
| 490 tExample Street |
| 512 bExample Street |
| ... |
Now if someone asked you to find addresses on bExample Street, you'd have no choice but to scan the entire index.
idfiyjo83#
LIKE with a leading wildcard (and any REGEX) will check every row. No indexing helps.
INDEX(created_at)together withORDER BY created_at DESC LIMIT 5will help only if it finds 5 matching rows created recently. Otherwise, it won't help.Anyway,
INDEXcannot handle aTEXTcolumns. AndINDEX(col(255)), though possible, limits the search to the first 255 characters.A
FULLTEXTindex, together withMATCH(...) AGAINST(...)can help a lot. But there are caveats. Study the manual, then we can discuss some of its difficulties and workarounds.Limiting an index to just
(title), not(title, msg)may help some, but not as much as you would like.