ODBC Indexes / Access Queries

[warmexxus]

If I create an Index on the MySql server is it still effective for an Access Query? The table analyzer tells me to create indexes on certain fields which I have done to the server. Do I still have to create the indexes locally for the ODBC Linked tables?

I was just wondering. Any suggestions?

Thank you,
Dan

 

[Banana]

Well, it depends.

(Don't you hate that answer? )

The main thing we need to realize is that we have two engines working together, MySQL and Jet. In general, Jet will try and hand off as much work as it can to MySQL and let MySQL use its own indices and optimizations. But if you force Jet to execute a poorly written query*, then Jet may end up making unreasonable request that doesn't employ MySQL's optimization and pull over too many rows.

* A easy way to do this is to use Access-only functionality that is not a part of SQL Standard. Few examples:

SELECT * FROM t WHERE IsNull(MyField, "") = 0;

SELECT Price, Iif([Product] = "OurProduct", [Price] * 1.1, [Price]) As TotalPrice FROM t

Those queries' performance will be abysmal because Jet is forced to pull all rows and do a local evaluation upon the rows, missing out on any optimization MySQL may have to offer.

For that reason, I usually encourage anyone who work with ODBC sources to go and read the Jet/ODBC connectivity whitepaper which is freely available for download from support.microsoft.com. It includes several hints and loads of information on how you can use both engine effectively.

Good luck.