Getting optimizer hints to mysql through Torque (USE INDEX)

[Bryce Nesbitt <br...@obviously.com>]

I don't hold out much hope: but can I pass mysql optimizer hints through
torque?  I've got a query that is much faster with USE INDEX:

SELECT * FROM CCS_ASSIGNMENT USE INDEX(IDX_STOP)
WHERE CCS_ASSIGNMENT.START<='20070331000000'
AND CCS_ASSIGNMENT.STOP>='20070328000000'
AND CCS_ASSIGNMENT.POD_ID=13;

Without a hint, the mysql optimizer chooses the POD_ID index, when the
STOP index would be better.   Is there a way to convince Torque to pass
through the relevant bit of SQL?

The above query is from torque 3.0-b4, with the USE INDEX() added. 
Criteria.CUSTOM won't work in this case.

http://www.obviously.com/


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org

RE: Getting optimizer hints to mysql through Torque (USE INDEX)

[Greg Monroe <Gr...@DukeCE.com>]

Hmm, I can't think of a way to get that syntax thru even the 
village level stuff.

A couple of things to try rather than depending on specialized
syntax are:

Create an additional index based on the pod and stop fields.

Try reordering your where criteria.  SQL parsers will sometimes 
change the optimization paths based on these.

> -----Original Message-----
> From: Bryce Nesbitt [mailto:bryce1@obviously.com] 
> Sent: Thursday, March 29, 2007 2:39 AM
> To: torque-user@db.apache.org
> Subject: Getting optimizer hints to mysql through Torque (USE INDEX)
> 
> I don't hold out much hope: but can I pass mysql optimizer 
> hints through torque?  I've got a query that is much faster 
> with USE INDEX:
> 
> SELECT * FROM CCS_ASSIGNMENT USE INDEX(IDX_STOP) WHERE 
> CCS_ASSIGNMENT.START<='20070331000000'
> AND CCS_ASSIGNMENT.STOP>='20070328000000'
> AND CCS_ASSIGNMENT.POD_ID=13;
> 
> Without a hint, the mysql optimizer chooses the POD_ID index, when the
> STOP index would be better.   Is there a way to convince 
> Torque to pass
> through the relevant bit of SQL?
> 
> The above query is from torque 3.0-b4, with the USE INDEX() added. 
> Criteria.CUSTOM won't work in this case.
> 
> http://www.obviously.com/
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 
> 

Duke CE Privacy Statement
Please be advised that this e-mail and any files transmitted with it are confidential communication or may otherwise be privileged or confidential and are intended solely for the individual or entity to whom they are addressed.  If you are not the intended recipient you may not rely on the contents of this email or any attachments, and we ask that you  please not read, copy or retransmit this communication, but reply to the sender and destroy the email, its contents, and all copies thereof immediately.  Any unauthorized dissemination, distribution or copying of this communication is strictly prohibited.



---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org