Using FORCE INDEX with zend

Question

I try to find how to translate the following MySQL query into Zend Db Table Select:

SELECT ColA,ColB,ColC
FROM MyTable
FORCE INDEX(ColA,ColB)
WHERE ColA = 'val0002'
AND ColB = 'val0045'

i try to use something like this:

$select = $dbTable->select()
            ->from('MyTable',array('ColA','ColB')
            ->forceIndex(array('ColA','ColB'))
            ->where("ColA = 'val0002'")
            ->where("ColB = 'val0045'");

I found " forceIndex(array('ColA','ColB')) " in a forum, but it does not work :(

and thank you for helping me :)

Hawkie

Answer 1

I think Zend_Db_Select doesn't support it yet. There seems to be an improvement request about it here: http://framework.zend.com/issues/browse/ZF-7570

(The report comments contain some links to code that could be useful to you).

Hope that helps,

Glyphography

is too bad that this method MySQL, has not equivalent in Zend, it greatly increases the performance of a query. waiting to find a solution in the next Zend version. – Hawkie

Answer 2

here's a solution that can help interested by the problem :
http://pastie.org/1354770

we can add the following two methods to the zend class "Zend_Db". I hope it will help you as it helped me (but partially).

/**
 * Specify index to use
 *
 * @return Zend_Db_Select
 */
public function useIndex($index)
{
    if(empty($this->_parts[self::FORCE_INDEX])) {
        if(!is_array($index)) {
            $index = array($index);
        }
        $this->_parts[self::USE_INDEX] = $index;
        return $this;
    } else {
        throw new Zend_Db_Select_Exception("Cannot use 'USE INDEX' in the same query as 'FORCE INDEX'");
    }
}

/**
 * Force index to use
 *
 * @return Zend_Db_Select
 */
public function forceIndex($index)
{
    if(empty($this->_parts[self::USE_INDEX])) {
        if(!is_array($index)) {
            $index = array($index);
        }
        $this->_parts[self::FORCE_INDEX] = $index;
        return $this;
    } else {
        throw new Zend_Db_Select_Exception("Cannot use 'FORCE INDEX' in the same query as 'USE INDEX'");
    }
}

Hawkie