Level 75
Select the fields before group. And do you need the array.
Dec 3, 2024
17
Level 5
SQL query very fast in phpMyAdmin but slow in Laravel application
here is my sql query and it returns 1000 rows.
SELECT COUNT(*) AS `Rows`, `OrderID`
FROM `order_items`
WHERE `OrderID` IN(1,2,3,...,1000) AND `Status` != 0
GROUP BY `OrderID`
here OrderID, Status columns are indexed,
when run this query in phpmyadmin it takes 0.0082 seconds, but same query takes 0.5 upto 0.9 sec in laravel eloquent
OrderItems::selectRaw('count(*) as `Rows`, `OrderID`')
->whereIn('OrderID', $ids)
->where('Status', '!=', OrderItems::STATUS_UNCOMPLETE)
->groupBy('OrderID')
->get()
->pluck('Rows', 'OrderID')
->toArray()
I am using AWS aurora and Laravel 5.2, read write host are different.
Level 5
hello, thanks for reply.
"Select the fields before group." Sorry, not clear can you please show me the code ?
"And do you need the array." I turn off ->toArray() and tested but no improvement.
Level 11
try
$results = DB::table('order_items')
->selectRaw('count(*) as `Rows`, `OrderID`')
->whereIn('OrderID', $ids)
->where('Status', '!=', OrderItems::STATUS_UNCOMPLETE)
->groupBy('OrderID')
->pluck('Rows', 'OrderID');
Level 5
Level 122
your pluck() is working on the collection, remove the get()
you should probably also add an index on the Status column, but running EXPLAIN woukd help.
How are you measuring the query duration?
Level 5
hi, thanks for reply
yes i have already tried without get(), but not much improvement
i have checked with explain , Status col is already indexed and its very fast in phpmyadmin with existing indexing.
inside laravel app i use microtime(true); to measuring query duration.
Level 11
@smartadmin you should try what @snapey suggested add index on the Status column then try this refactored code
$results = DB::table('order_items')
->whereIn('OrderID', $ids)
->where('Status', '!=', OrderItems::STATUS_UNCOMPLETE)
->groupBy('OrderID')
->selectRaw('COUNT(*) as Rows, OrderID')
->pluck('Rows', 'OrderID');
Note: Use EXPLAIN to analyze query performance.
Level 5
move selectRaw after groupBy increase the time double !
and i have already checked with EXPLAIN Select ... , that is okay.
as i said this query is very fast in phpMyAdmin , so far i understand no issue with indexing.
Level 75
Also, If you've already done the select you should not even need pluck. In which case get should work.
Level 5
actually i need pluck here because i need the results as key : value array format for mapping.
but i have tried w/o pluck but no improvement .
Level 122
run the query twice in succession and time both, any difference might be explained as establishing connection
Level 5
hi, no issue with establishing DB connection, because I have tested with another query at same time which is return same time both in phpMyAdmin and Laravel app.
Level 73
@smartadmin show us the execution plan of both queries.
Level 5
hi,
// start time
Order::query() ->uncomplete()->running() ->orderBy('id', 'asc') ->get();
// end time , it return same time both phpmyadmin and laravel
but other query does not.
// start time
OrderItems::selectRaw('count(*) as `Rows`, `OrderID`')
->whereIn('OrderID', $ids)
->where('Status', '!=', OrderItems::STATUS_UNCOMPLETE)
->groupBy('OrderID')
->pluck('Rows', 'OrderID')
->toArray()
// end time
Level 73
@smartadmin That is not what I asked for, I want to see the execution plans for each of the queries.
You can get that by adding EXPLAIN in front of the queries, for example.
EXPLAIN SELECT authors.name, books.title
FROM books
JOIN authors
ON books.author_id = authors.id;
Would give something like
MariaDB [foreign_keys]> EXPLAIN SELECT authors.name, books.title FROM books JOIN authors ON books.author_id = authors.id;
+------+-------------+---------+--------+---------------+---------+---------+------------------------------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+--------+---------------+---------+---------+------------------------------+-------+-------+
| 1 | SIMPLE | books | ALL | NULL | NULL | NULL | NULL | 10000 | |
| 1 | SIMPLE | authors | eq_ref | PRIMARY | PRIMARY | 8 | foreign_keys.books.author_id | 1 | |
+------+-------------+---------+--------+---------------+---------+---------+------------------------------+-------+-------+
2 rows in set (0,000 sec)
Level 5
id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE order_items NULL range order_items_id_index,St... order_items_id_index 4 NULL 23364 50 Using index condition; Using where
Level 5
I am using AWS aurora and Laravel 5.2, read write host are different.
Please or to participate in this conversation.