blog-plugin: Slow query
select `rainlab_blog_posts`.* from `rainlab_blog_posts` left join `rainlab_translate_indexes` on rainlab_blog_posts.id = `rainlab_translate_indexes`.`model_id` and `rainlab_translate_indexes`.`model_type` = ? and `rainlab_translate_indexes`.`locale` = ? where (`rainlab_blog_posts`.`slug` = ? or (`rainlab_translate_indexes`.`item` = ? and `rainlab_translate_indexes`.`value` = ?)) and `published` is not null and `published` = ? and `published_at` is not null and `published_at` < ? limit 1
these requests are very long, about 29 seconds
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Comments: 16 (10 by maintainers)
@LukeTowers I’m not trying to offend anyone. I am a simple user of this product. I don’t know how it functions in general. I only see that with 3 thousand records, the search goes on 8 million or more. and I am suggested to use ElasticSearch as a layer. Perhaps there is something to think about?
I solved this problem in my website, but I do not think that the solution will suit you. Thank you very much for your understanding!
@allfreelancers Instead of being flippant, feel free to submit a PR improving it.
@bennothommo I’ve used it once, and there are too many issues with that (rules to follow). Performance wise it’s better to organize your DB layer better or add a elasticsearch in between (or something similar).
@bennothommo don’t think there is a lot of option to go for. Better to use, not a straightforward approach, that I’ve mentioned above.
Simple example. Every column, despite Id, is varchar or worse - text. Even if you add indexes, it’s just won’t perform, only in case you have less then 100k rows: