I'd like to be able to tweak the table joins in my SQL statement to see if I can optimize it better than the EDB optimizer.

1) Is it possible to turn off the EDB SQL optimizer altogether?
2) Can I explicitly specify which indexes to use in the SQL statement (whether a join or not)?

MySQL has the capability. See http://dev.mysql.com/doc/refman/5.1/en/index-hints.html.
I don't know how difficult this would be to implement in EDB. Most of the time the join speeds are fine but giving the developer the ability to tweak the joins may prove beneficial.

3) Is it possible to see the Execution Plan in EDB Mgr (or anywhere else) *without* executing the SQL statement?
MySQL has an "Explain" command that you can prepend to a Select statement to get the execution plan without executing the query.

Example:
  Explain select abc, def from table1 left join table2 on table1.col1=table2.col2;

This eliminates the need to wait for the SQL statement to execute and of course doesn't have to send the query results back to the client. It is fast and useful, especially for large slow queries.

Barry
v2.12B2

Hi Barry,

> 1) Is it possible to turn off the EDB SQL optimizer altogether?

Add   NOJOINOPTIMIZE as the last line in your query.  That will force it to
use the joins in the order that you specify.

> 2) Can I explicitly specify which indexes to use in the SQL statement
> (whether a join or not)?

Not that I am aware of.

> 3) Is it possible to see the Execution Plan in EDB Mgr (or anywhere else)
> *without* executing the SQL statement?

No, but when testing your query you can add  RANGE 1 to 1 after the where
clause.  That can sometimes dramatically reduce the amount of time to
execute a query if you are just working on the design of it.


---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

Barry


Apart from +1 for Terry's post I think EXPLAIN depends on maintaining statistics and I remember a vote of some sort (might have been for DBISAM though) about not keeping them.

Roy Lambert

"Terry Swiers - Millennium Software, Inc." wrote:

>Add   NOJOINOPTIMIZE as the last line in your query.  That will force it to
>use the joins in the order that you specify.

Thanks. I thought there was a phrase like that but I was searching for "Optimize" in the EDB SQL PDF manual but had inadvertently had "Search Whole Words" turned on so it never found "NoJoinOptimize".


>> 2) Can I explicitly specify which indexes to use in the SQL statement
>> (whether a join or not)?

>Not that I am aware of.

Ok

>> 3) Is it possible to see the Execution Plan in EDB Mgr (or anywhere else)
>> *without* executing the SQL statement?

>No, but when testing your query you can add  RANGE 1 to 1 after the where
>clause.  That can sometimes dramatically reduce the amount of time to
>execute a query if you are just working on the design of it.

Yes, it sometimes can reduce the amount of rows returned. But I'm afraid in my case the "Range 1 to 1" does not speed up the multi-table join query (it still takes several minutes).

So an "Explain" command would help a lot for these types of queries with no stress on the server or network.
I'm sure EDB can provide the Execution Plan before executing the query, so the "Explain" command does everything up to executing the query, but avoids executing the query (and getting the locks). At least that's how I see it. Maybe Tim has another explanation if it can or cannot be done.

Barry

Barry,

<< So an "Explain" command would help a lot for these types of queries with
no stress on the server or network.  I'm sure EDB can provide the Execution
Plan before executing the query, so the "Explain" command does everything up
to executing the query, but avoids executing the query (and getting the
locks). At least that's how I see it. Maybe Tim has another explanation if
it can or cannot be done. >>

I can see about adding a keyword to the DML statements that will prevent
actual execution.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

>I can see about adding a keyword to the DML statements that will prevent
>actual execution.

That would be great. Thanks.

Barry