Blog-plugin: Slow query

[allfreelancers]

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

[w20k]

@allfreelancers how many records do you have?
Have you tried adding an additional index?

[allfreelancers]

about 3000-4000 records

all index a set

PRIMARY | BTREE | Да | Нет | id | 4603 | A | Нет |  
-- | -- | -- | -- | -- | -- | -- | -- | --
  |   | BTREE | Нет | Нет | locale | 4 | A | Нет |  
  |   | BTREE | Нет | Нет | model_id | 4603 | A | Да |  
  |   | BTREE | Нет | Нет | model_type | 4 | A | Да |  
  |   | BTREE | Нет | Нет | item | 2 | A | Да |  
  |   | BTREE | Нет | Нет | value (256) | 4603 | A | Да

[w20k]

@allfreelancers I think your best bet would be to do something similar (link to comment below). Joining the translated index should be your main issue here.

https://github.com/rainlab/blog-plugin/issues/321#issuecomment-309293121

[allfreelancers]

sorry, but it is wrong

Time: 2019-06-05T14:36:24.986001Z

User@Host: @ localhost [] Id: 1210

Schema: cms Last_errno: 0 Killed: 0

Query_time: 75.246369 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 8076190 Rows_affected: 0

Bytes_sent: 21604

SET timestamp=1559745384;
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 = 'RainLab\Blog\Models\Post' and rainlab_translate_indexes.locale = 'kk' where (rainlab_blog_posts.slug = 'molodye-predprinimateli-i-startapery-na-ploshchadke-mayzdy-v-sck-obsudili-proekty-na-predstoyashchij-god-molodezhi' or (rainlab_translate_indexes.item = 'slug' and rainlab_translate_indexes.value = 'molodye-predprinimateli-i-startapery-na-ploshchadke-mayzdy-v-sck-obsudili-proekty-na-predstoyashchij-god-molodezhi')) and published is not null and published = 1 and published_at is not null and published_at < '2019-06-05 20:35:09' limit 1;

wtf????

[allfreelancers]

@w20k ?

[w20k]

Currently, you (@allfreelancers) have only a single option to go for, don't think that we'd improve it right away.
And it's described in the link I've provided (credit to @jan-vince ).

 was thinking about a more simple approach on template level (page/layout) and this is how I would solve it:

<?php

use \RainLab\Blog\Models\Post;

function onStart() {

    $posts = Post::all();

    foreach( $posts as $key => $post) {

        $post->noFallbackLocale()->lang('en');

        if( empty($post->title) ) {
            unset($posts[$key]);
        }

    }

    $this['filteredPosts'] = $posts;

}

?>
Definitelly not the best solution but should work :)

[allfreelancers]

@w20k you see what:
Query_time: 75.246369 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 8076190 Rows_affected: 0

???

[bennothommo]

@allfreelancers It's because of this part of the query:

(rainlab_translate_indexes.item = 'slug' and rainlab_translate_indexes.value = 'molodye-predprinimateli-i-startapery-na-ploshchadke-mayzdy-v-sck-obsudili-proekty-na-predstoyashchij-god-molodezhi')

Whilst the item field is indexed, the value field isn't. When it is a LEFT JOIN and you've got 8 million+ records in there, it is going to be slow. If you can suggest some improvements to the query to make it faster, please feel free to make a comment here or submit a PR.

[w20k]

@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:

[bennothommo]

@w20k It might benefit from a fulltext index, however, only certain database engines support it.

[w20k]

@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]

@allfreelancers At this stage there is little we can do to improve the query without causing backwards compatibility issues. You will need to look into options such as caching results or adding resources to the database server.

[allfreelancers]

@bennothommo you are joking? Why do you offer this product with such possible consequences?

there are two possible options:

1. use INNER JOIN
2. add all translation options to the table

[bennothommo]

@allfreelancers Instead of being flippant, feel free to submit a PR improving it.

[LukeTowers]

@allfreelancers I suggest that if you want results you treat people with more respect. This "product" is a 100% free plugin made possible entirely by the unpaid contributions of several community members. If you have an idea to improve it, then make a PR and explain why it should be accepted politely and we'd be more than happy to look at including it.

Please read the Contributing Guidelines and do your best to be respectful of the time that the people you are engaging with are freely contributing to help you out. Life is too short to spend on helping people that make it painful to help them; so the simple solution is we won't. We're more than happy to help people that are respectful of our time and want to reach a solution. Remember, it is a lot harder to determine tone and intent over text, so think carefully about what you are saying and how it could be interpreted before posting a comment, especially if you're annoyed or otherwise irritated when writing it.

[allfreelancers]

@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!