Query Analyzer / Tune up Selects

[Smeagle]

Hi,

is there a MSSQL-like “Query Analyzer” available “out there in the net”?

I know, this is very hard to do, but this would be a quite usefull addition for sqlyog…

I would like to have some help in setting up Indexes and tuning select statements for optimum speed.

I switched from MSSQL/ASP (converting a project) and find mysql/php quite slow – I wonder why… With MSSQL there was no “feelable” lag when opening Pages, with mysql/php there is a ~ 0,5s lag with even simple pages, and some pages even time out, where the mssql/asp equivalent was below 1s… And, I use a 2GHz machine with mysql/php for developing and testing and the production machine with MSSQL/APS is a 400MHz box… Can't believe it.

Ok, I had to recode many statements for mysql because of missing features (I want to use the “production” version of mysql)…

– Oliver

[Shadow]

MySql provides the EXPLAIN statement for analyzing queries (only SELECT statements, though). It's quite impossible for a client to tell how MySql optimizes a query if no API is supplied… See the corresponding sections in the MySql manual (How MySql optimizes…).

[Smeagle]

Hi,

thanks for that info (explain) it's better than nothing 🙂

I understand, a query analyzer would be very hard to do… But it's the feature I miss most, after switching vom MSSQL…

I now noticed why I had problems in optimizing indicies: mySQL seems not to use a newly created index at the first few querys after generating it!? If I send the same query a couple of times after setting up index it gets faster and faster…. 🙁

– Oliver

[Shadow]

The speed gain must not be attributed to the indices, it is the result of the query cache. I noticed that sometimes MySql does disregard indices created for speeding up queries. Try using FORCE INDEX to make MySql take advantage of your work!

[Author]

Posts