How to write a query with "USE INDEX (artist_2)" using kohana 3 orm?

Question

MySQL

There is such a code

ORM::factory('Mp3')->where('artist', '=', $artist_name)->where('id', '>', $start_mp3_id)->order_by('id')->limit(10)->find_all();

It was discovered that mysql was using the wrong index during this query. The "USE INDEX (artist_2)" construct must be added to this query. How to do it? It should turn out something like:

SELECT * FROM `mp3s` AS `mp3` USE INDEX (artist_2) WHERE `artist` = 'Аркадий Кобяков' AND `id` > '453509' ORDER BY `id` LIMIT 10

Answer 1

Thank you all for your help, for myself I chose the path of overriding class methods in the application.
Add a method to the ORM (application/classes/ORM.php):

<?
class ORM extends Kohana_ORM {

  public function use_index($index)
  {
    $this->_db_pending[] = array(
      'name' => 'use_index',
      'args' => array($index),
    );
    return $this;
  }
}

And write what you need in application/classes/Database/Query/Builder/Select.php

<?
class Database_Query_Builder_Select extends Kohana_Database_Query_Builder_Select {

  protected $_use_index = NULL;

  public function use_index($index)
  {
    $this->_use_index = $index;

    return $this;
  }

  public function compile($db = NULL)
  {
    if ( ! is_object($db))
    {
      // Get the database instance
      $db = Database::instance($db);
    }

    // Callback to quote columns
    $quote_column = array($db, 'quote_column');

    // Callback to quote tables
    $quote_table = array($db, 'quote_table');

    // Start a selection query
    $query = 'SELECT ';

    if ($this->_distinct === TRUE)
    {
      // Select only unique results
      $query .= 'DISTINCT ';
    }

    if (empty($this->_select))
    {
      // Select all columns
      $query .= '*';
    }
    else
    {
      // Select all columns
      $query .= implode(', ', array_unique(array_map($quote_column, $this->_select)));
    }

    if ( ! empty($this->_from))
    {
      // Set tables to select from
      $query .= ' FROM '.implode(', ', array_unique(array_map($quote_table, $this->_from)));
    }

    if ($this->_use_index !== NULL)
    {
      // Use index
      $query .= " USE INDEX (" . $this->_use_index . ")";
    }

    if ( ! empty($this->_join))
    {
      // Add tables to join
      $query .= ' '.$this->_compile_join($db, $this->_join);
    }

    if ( ! empty($this->_where))
    {
      // Add selection conditions
      $query .= ' WHERE '.$this->_compile_conditions($db, $this->_where);
    }

    if ( ! empty($this->_group_by))
    {
      // Add grouping
      $query .= ' '.$this->_compile_group_by($db, $this->_group_by);
    }

    if ( ! empty($this->_having))
    {
      // Add filtering conditions
      $query .= ' HAVING '.$this->_compile_conditions($db, $this->_having);
    }

    if ( ! empty($this->_order_by))
    {
      // Add sorting
      $query .= ' '.$this->_compile_order_by($db, $this->_order_by);
    }

    if ($this->_limit !== NULL)
    {
      // Add limiting
      $query .= ' LIMIT '.$this->_limit;
    }

    if ($this->_offset !== NULL)
    {
      // Add offsets
      $query .= ' OFFSET '.$this->_offset;
    }

    if ( ! empty($this->_union))
    {
      foreach ($this->_union as $u) {
        $query .= ' UNION ';
        if ($u['all'] === TRUE)
        {
          $query .= 'ALL ';
        }
        $query .= $u['select']->compile($db);
      }
    }

    $this->_sql = $query;

    return parent::compile($db);
  }

}

On a note, in the compile method, I added lines to the original compile method

if ($this->_use_index !== NULL)
    {
      // USE INDEX
      $query .= " USE INDEX (" . $this->_use_index . ")";
    }

Now you can use the ORM:

ORM::factory('Mp3')->use_index('artist_2')->where('artist', '=', $artist_name)->where('id', '>', $start_mp3_id)->order_by('id')->limit(10)->find_all();

Answer 2

With this type of writing queries, the Query Builder is used. He doesn't support that.
Without crutches and improvements of the Query Builder itself, you can use DB::query for example.

Answer 3

And you try like this:

$query = Database::instance('ssd2')->query(Database::SELECT, "SELECT * FROM `mp3s` USE INDEX (artist_2) WHERE `artist` = 'test' AND `id` > 453509 ORDER BY `id` LIMIT 10");

foreach($query as $row)
{
   print_r($row);
}

I checked the code, `query` already gives an iterator that can be unwound.
The signature of your method is:

public function query($type, $sql, $as_object = FALSE, array $params = NULL);