This post is automatically translated to English by Google Translate.

USE INDEX the CakePHP way

Well ok not 100% the cakephp way but better than just using $this->query(...).

I wanted it to look like this:

$this->find('all', array(

'fields' => array('id', 'name'),

'use' => 'index_name'


To get this working you need to modify dbo_source. Copy it from cake/libs/model/datasources/dbo_source.php to app/models/datasources/dbo_source.php.

You can download my modified dbo_source.php . I have commented the modified lines with "added". I have only tested this on MySQL. I do not know if it will work on other databases. Also I am not sure if my solution is the best. Please feel free to comment if you have any improvements.


To manually modify the dbo_source to support the USE INDEX syntax:

in function read:

/* added */

if (!isset($queryData['use']))

    $queryData['use'] = null;

/* /added */

in function generateAssociationQuery:

    return $this->buildStatement(


'fields' => array_unique($queryData['fields']),

'table' => $this->fullTableName($model),

'alias' => $model->alias,

'limit' => $queryData['limit'],

'offset' => $queryData['offset'],

'joins' => $queryData['joins'],

'conditions' => $queryData['conditions'],

'order' => $queryData['order'],

'group' => $queryData['group'],

'use' => $queryData['use'] // added

    ), $model


in function buildStatement:

return $this->renderStatement('select', array(

    'conditions' => $this->conditions($query['conditions'], true, true, $model),

    'fields' => implode(', ', $query['fields']),

    'table' => $query['table'],

    'alias' => $this->alias . $this->name($query['alias']),

    'order' => $this->order($query['order'], 'ASC', $model),

    'limit' => $this->limit($query['limit'], $query['offset']),

    'joins' => implode(' ', $query['joins']),

    'group' => $this->group($query['group'], $model),

    'use' => $query['use'] // added


in function renderStatement:

    case 'select':

/* added */

if (empty($use)) // added

    return "SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order} {$limit}";


    return "SELECT {$fields} FROM {$table} {$alias} {$joins} USE INDEX ({$use}) {$conditions} {$group} {$order} {$limit}";

/* /added */

Posted in CakePHP
3 Comments »USE INDEX the CakePHP way
  1. dev says:

    Nice Tuts

  2. dev says:

    ADD this method

    public function uses($fields, Model $Model = null) {
    if (empty($fields)) {
    return null;

    if (!is_array($fields)) {
    $fields = array($fields);

    if ($Model !== null) {
    foreach ($fields as $index => $key) {
    if ($Model->isVirtualField($key)) {
    $fields[$index] = '(' . $Model->getVirtualField($key) . ')';

    $fields = implode(', ', $fields);

    return ' FOREC INDEX ' . $this->_quoteFields($fields);

    IN Function
    public function buildStatement($query, Model $Model) {}

    'use'=>$this->uses($query['use'], $Model)

  3. Shafira Holloway says:

    Pariatur Anim incididunt eum amet neque

Leave a Reply

Your email address will not be published. Required fields are marked *