Where is equal to zero is so slow

user_6756858 0
Viewed 108

I'm using Laravel 5.6 and MySQL for DB

public function getTopPaid(){
    $books = Book::with('users')->where('price', '>', 0 )->get()
        ->sortByDesc(function ($book){
            return $book->users->count();//sorting by purchased users count
        })->take(25);
    return new BooksWithAuthors($books);
}

I want to get the most purchased paid books with code above. And this is fine and response time from this is 1700 milliseconds. And about 400 records.

However the below code is almost the same:

public function getTopFree(){
    $books = Book::with('users')->where('price', '=', 0 )->get()
        ->sortByDesc(function ($book){
            return $book->users->count();
        })->take(25);
    return new BooksWithAuthors($books);
}

only 34 records in result,but the RESPONSE is in 8000 milliseconds. And the only difference in the code is "equal" 

where('price', '>', 0 )

and

where('price', '=', 0 )

Why the second query is so slow? And how to fix this

laravel-5.6 mysql-8.0 laravel mysql php
2 Answers

MySQL stores it's indexes by default in a BTREE. No hashing in general.

The short answer for the performance difference is that the > form evaluates more nodes then the = form.

user_36411030

If you often use the price column for searches you can consider adding an index to the that column to improve performance:

CREATE INDEX books_price_idx ON books (price)
user_102851470
Related