CODEWITHSUNDEEP
phpmysqllaravel
Nitish Patra
Nitish Patra

Reputation: 329

Mysql Indexing for Laravel Eloquent

I have an ever growing table in MySQL Database. I need to perform a select operation which looks like:

$discountedBooks=
\App\Entities\Books::select('id','published_date','status','chargeable','author')
->whereRaw('DATE(published_date) = "'.$date.'"')
->whereStatus('Completed')
->whereChargeable(1);

I have assigned an index to books table

But when I explain the executed query, I don't see the index is being used. Any suggestions for how should i implement it?

enter image description here enter image description here

Upvotes: 0

Views: 5377

Answers (3)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25936

Use where('published_date', $date) or wherePublishedDate($date).

MySQL can only use an index if you query the whole column or a left prefix. While DATE(published_date) technically returns the whole column, MySQL doesn't know that and therefore can't use the index.

You should only use whereRaw() if you have to. If you use it, always use bindings (SQL injection):

->whereRaw('DATE(published_date) = ?', [$date])

Upvotes: 3

Xuchen ZHANG
Xuchen ZHANG

Reputation: 51

You have indexed the schedule_date but used published_date. Jus

Upvotes: 1

Leo Rams
Leo Rams

Reputation: 739

Even though you have indexed scheduled_date, you are not using it. You are using published_date which you should have indexed

Upvotes: 1

Related Questions