Level 75
Go here https://www.mysqltutorial.org/?s=left+join
Click the tutorial links and really study the differences in a right join vs a left join.
Jan 13, 2020
3
Level 2
Optimizing query
I've been running into this problem for a while now. I've dramatically reduced the loading time of this query from 30s to 3s but it's still taking to long in my opinion. This query is used in our chatbot, to get a response for a customer.
It's all about this Eloquent query:
/**
* Only find sentences that are allowed to say back.
* 1. WHERE NOT EXISTS sentences said by this bot said in the past 2 weeks
* 2. WHERE NOT EXISTS sentences said in the last 8 minutes by any bots
* 3. WHERE NOT EXISTS sentences said to this customer
* 4. WHERE EXIST sentences with [$translation] translation
*/
$sentences = DB::table('sentences')
->where('keyword_id', '=', $keyword)
->whereNotExists(function ($query) use ($bot, $customer, $twoWeeksAgo) {
$query->selectRaw('id')
->from('customer_sentences')
->whereRaw('sentences.id = customer_sentences.sentence_id')
->whereRaw('customer_sentences.bot_id = ?', $bot->id)
->whereRaw('customer_sentences.created_at >= "' . $twoWeeksAgo .'"');
})
->whereNotExists(function ($query) use ($eightMinutesago) {
$query->selectRaw('id')
->from('customer_sentences')
->whereRaw('sentences.id = customer_sentences.sentence_id')
->whereRaw('customer_sentences.created_at >= "' . $eightMinutesago . '"');
})
->whereNotExists(function ($query) use ($customer) {
$query->selectRaw('id')
->from('customer_sentences')
->whereRaw('sentences.id = customer_sentences.sentence_id')
->whereRaw('customer_sentences.customer_id = ?', $customer->id);
})
->whereExists(function ($query) use ($translation) {
$query->selectRaw('id')
->from('sentence_translations')
->whereRaw('sentence_translations.sentence_id = sentences.id')
->whereRaw('sentence_translations.language_id = ?', $translation);
})
->get();
I've stripped the query down, and found out it is this line which is taking long to execute:
->whereNotExists(function ($query) use ($eightMinutesago) {
$query->selectRaw('id')
->from('customer_sentences')
->whereRaw('sentences.id = customer_sentences.sentence_id')
->whereRaw('customer_sentences.created_at >= "' . $eightMinutesago . '"');
})
->
I was then thinking to use a left join (i've very less experience with this):
$sentences = DB::table('sentences')
->where('keyword_id', '=', $keyword)
->whereNotExists(function ($query) use ($bot, $customer, $twoWeeksAgo) {
$query->selectRaw('id')
->from('customer_sentences')
->whereRaw('sentences.id = customer_sentences.sentence_id')
->whereRaw('customer_sentences.bot_id = ?', $bot->id)
->whereRaw('customer_sentences.created_at >= "' . $twoWeeksAgo .'"');
})
->leftJoin('customer_sentences', function ($query) use ($eightMinutesago) {
$query->on('sentences.id', '=', 'customer_sentences.sentence_id')
->where('customer_sentences.created_at', '=', $eightMinutesago);
})
->whereNull('customer_sentences.id')
->whereNotExists(function ($query) use ($customer) {
$query->selectRaw('id')
->from('customer_sentences')
->whereRaw('sentences.id = customer_sentences.sentence_id')
->whereRaw('customer_sentences.customer_id = ?', $customer->id);
})
->whereExists(function ($query) use ($translation) {
$query->selectRaw('id')
->from('sentence_translations')
->whereRaw('sentence_translations.sentence_id = sentences.id')
->whereRaw('sentence_translations.language_id = ?', $translation);
})
->get();
The above query is running much faster, but I'm unsure this is the way to go.
I am already having the following indexes:
$table->index(['created_at', 'bot_id']);
$table->index(['bot_id']);
$table->index(['customer_id']);
$table->index(['bot_id', 'created_at', 'sentence_id']);
I am really wondering how to solve this problem. I am stuck on this for a long time now.
Level 73
I suggest you change ->get() to ->toSql() and dump the query. Then you log into your favorite sql gui or the mysql prompt and run explain <your query> that should tell you what is taking time. It can be a bit tricky to understand but look for Full table scan and Cartesian products
The output will look something like this
MariaDB [mediabase]> explain select * from book_views;
+------+--------------------+-------+-------+---------------+---------+---------+------+------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+-------+-------+---------------+---------+---------+------+------+--------------------------------------------------------+
| 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | g | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer (flat, BNL join) |
| 1 | PRIMARY | f | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer (incremental, BNL join) |
| 6 | DEPENDENT SUBQUERY | bi | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 5 | DEPENDENT SUBQUERY | s | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | a | ALL | PRIMARY | NULL | NULL | NULL | 2 | |
| 4 | DEPENDENT SUBQUERY | ab | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |
| 3 | DEPENDENT SUBQUERY | a | index | PRIMARY | PRIMARY | 8 | NULL | 2 | Using index |
| 3 | DEPENDENT SUBQUERY | ab | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) |
+------+--------------------+-------+-------+---------------+---------+---------+------+------+--------------------------------------------------------+
9 rows in set (0.001 sec)
Level 122
have you indexed your customer_sentences table? You are querying against combination of sentence_id and customer_id.
Same for sentence_translations.
also watch this Laracon talk https://youtu.be/HubezKbFL7E
Please or to participate in this conversation.