MySQL Query-rewriters?

[OhLordy]

I've just sorted out a big messy wuery after about a day of playing around with it (I've reduced it from searching about 25 million rows to just 2!!). Now for a lot of this I was just playing around with the joins to see what would help but it would have been far easier if I could have just plugged the database schema and the query into a program and let it run through and optimize it for me. I have heard that you can EQRs (External Query Re-writers) for Oracle databases where you have a dedicated machine just for optimizing difficult queries. Is there a similar sort of program for MySQL if not how difficult would it be to create one?
Cheers
Rob

[bad76]

Hi,
i've recently played with the optimizer for DB2. But i personally never heard any same tool for Mysql.

I think this kind of tool very powerfull, and i'm interesting to develop any for mysql (naturally a static optimizer...). But i haven't the needed experience with DB to do this.

I've looked around for common politicy of optimizer and found just some hint like that: http://www.devx.com/ibm/Article/11179/0/page/1

Time ago, I've thinked two way:

Any way, shorter, is to submit info like this

+--------------------------+--------+-------------------+-------------------+---------+-------------------------------------+------+-------------+
| table                    | type   | possible_keys      | key               | key_len | ref                                 | rows | Extra       |
+--------------------------+--------+-------------------+-------------------+---------+-------------------------------------+------+-------------+
| new_products             | ALL    | price_code        | NULL              |    NULL | NULL                                | 5771 |             |
| w_fuzzy_search           | ALL    | PRIMARY           | NULL              |    NULL | NULL                                |   19 |             |
| price_lookup             | eq_ref | pc1,currency_code | pc1               |      12 | new_products.price_code,const,const |    1 | Using where |
+--------------------------+--------+-------------------+-------------------+---------+-------------------------------------+------+-------------+

and checks for order and kind of each operation, puts this in a decision tree and resort with a minimized path. My trouble is to know a list of kind of operation and assign a weight to it.

The second, powerfull, way is to submit the query directly, but needs a SQL parser, generating the prew info then optimze it...

Both case needs to know very well the mysql, as it work, to get the right choice...