ZF-7570: add "FORCE INDEX" parameter to Zend_Db_Select::from() method

Description

One can do:

SELECT * FROM table_name FORCE INDEX (index_name) WHERE where_statement;

Comments

This may be database specific. As long as the individual select objects for each adapter can either (a) emulate it or (b) discard the state when creating the SQL, it should be okay.

I'd actually recommend this being a "forceIndex()" method, and not a parameter on the from() clause.

it is indeed a mysql specific statement. our DBA told me that there is too an USE INDEX and IGNORE INDEX statement related to this. I guess will not be too difficult to implenet an useIndex() and an ignoreIndex() method too. thank you and best regards.

I am curious to know the progress on this... From experience I can tell you that having forceindex and useindex as methods is a must have feature for Zend_Db_Select.

Same. What good is a framework if I'd have to replace it when the app becomes popular?

I've added support for useIndex and forceIndex for my own purposes, someone else may find this useful.

As mentioned above, this is database specific, but my implementation is ignorant to this, and just generates the MySQL syntax.

Replacement Zend/Db/Select.php: http://pastie.org/1354770

Usage:

$select->useIndex('index'); // Or $select->useIndex(array('index1', 'index2'));

$select->forceIndex('index'); // Or $select->forceIndex(array('index1', 'index2'));

Richard,

You will need to sign the CLA before your code submission can be included (and this will also give you the needed permissions to attach patches in the issue tracker).

Once you have signed the CLA, could you supply your patch as a svn diff, and also provide unit tests?

You will find the needed information about signing the CLA here: http://framework.zend.com/wiki/display/… And more information that you may find useful here: http://framework.zend.com/wiki/display/…

Sorry I missed you on IRC earlier, I would have given you this info then.

Hi Ryan,

Thanks a lot for the info. I'll complete this as soon as I get access to a printer/scanner.

I've now reimplemented the functionality to be adapter specific, with hinting for MySQL, SQL Server and Oracle, and written unit tests for it. Hopefully you should get a patch from me in the next few days.

Best wishes, Richard

Hi Richard, any progress with the patch?

Danail Nedyalkov

Hi Danail,

At the time I emailed my CLA and didn't hear anything back. If there's still interest I can chase it up (could somebody advise who I should email?) and update the patch.

Best wishes, Richard

Hi Richard, I've got no clue about the email address. :-( I use often MySql and really need this patch in official release. I hope that someone will add your patch in next release, until this I will use my own paginator in which I modify the db query. Thanks for the quick response.

Best wishes, Danail

Hi Daniel,

If you require this functionality, a better solution might be to extend Zend_Db_Adapter_Pdo_Mysql, add the method from the patch and use that instead.

This is a better place to do it than a paginator, and would mean it wouldn't require any changes (other than optionally switching back to the stock adapter) if this patch finds its way into a release.

Best wishes, Richard

Hi Richard, I guess you are right and I'll give it a try, thanks!

Best wishes, Danail

Hi Danail,

No problem, feel free to email me if you need any assistance!

Best wishes, Richard

@Richard: Have you received notification back from Zend on your CLA submission?

Hi there!

I am also quite interested in this functionallity.

I have replaced my Zend_Db_Select class with the one that @Richard proposed (THANKS!!): http://pastie.org/1354770

Ererything seemed to be okay, but something weird happens:

Look at the simple code below: $select->forceIndex('index'); echo $select->assemble(); // :-) I get the right SELECT query with the force index hint $this->fecthAll($select); // :-( The server execute a wrong SELECT query without the force index hint

Am I missing something?

Thanks in advance for your help!

Hi Emilio, can you try this: $select->forceIndex('index'); $sqlQuery = $select->assemble(); Zend_Db_Table_Abstract::getDefaultAdapter()->fetchAll($sqlQuery);

Best wishes, Danail