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
2 Comments »USE INDEX the CakePHP way
  1. 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)

Leave a Reply

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