Laravel Eloquent的Mysql索引

Question

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?

Answer 1

Just 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])