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

Issue Type: Improvement Created: 2009-08-12T05:13:28.000+0000 Last Updated: 2012-06-01T19:34:25.000+0000 Status: Open Fix version(s): Reporter: Ionut Dinu (tunder) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db_Select

Related issues: Attachments:


One can do:

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


Posted by Matthew Weier O'Phinney (matthew) on 2009-08-12T05:52:21.000+0000

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.

Posted by Matthew Weier O'Phinney (matthew) on 2009-08-12T05:52:58.000+0000

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

Posted by Ionut Dinu (tunder) on 2009-08-12T06:54:29.000+0000

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.

Posted by Bogdan Martinescu (yolau) on 2010-03-30T10:08:54.000+0000

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.

Posted by Josh Ribakoff (jshpro2) on 2010-05-17T21:43:39.000+0000

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

Posted by Richard John (richardjohn) on 2010-12-07T00:07:23.000+0000

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:


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

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

Posted by Ryan Mauger (bittarman) on 2010-12-07T01:44:53.000+0000


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:… And more information that you may find useful here:…

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

Posted by Richard John (richardjohn) on 2010-12-07T16:11:52.000+0000

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

Posted by Danail Nedyalkov (bliznaka) on 2011-07-18T18:40:48.000+0000

Hi Richard, any progress with the patch?

Danail Nedyalkov

Posted by Richard John (richardjohn) on 2011-07-18T19:26:32.000+0000

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

Posted by Danail Nedyalkov (bliznaka) on 2011-07-18T19:42:25.000+0000

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

Posted by Richard John (richardjohn) on 2011-07-18T20:00:11.000+0000

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

Posted by Danail Nedyalkov (bliznaka) on 2011-07-18T20:08:43.000+0000

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

Best wishes, Danail

Posted by Richard John (richardjohn) on 2011-07-18T20:26:06.000+0000

Hi Danail,

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

Best wishes, Richard

Posted by Adam Lundrigan (adamlundrigan) on 2011-10-31T15:03:33.000+0000

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

Posted by Emilio (nicoliky) on 2012-06-01T19:11:23.000+0000

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!!):

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!

Posted by Danail Nedyalkov (bliznaka) on 2012-06-01T19:34:25.000+0000

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

Best wishes, Danail

Have you found an issue?

See the Overview section for more details.


© 2006-2018 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.