douyun4524 2018-06-13 09:40
浏览 97
已采纳

Laravel Eloquent的Mysql索引

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

  • 写回答

3条回答 默认 最新

  • douzhan8652 2018-06-14 12:53
    关注

    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])
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?