I've got a project where all SQL queries had been written. the many of them could be improve. and i wish to improve that.
but exactly how i can check and evaluate the two queries [pre-written and that i write same queries using different way].
means i wish to evaluate the quries basically rewrite them i quickly can check i actually do right or old query better then mine.
means a factor which i require a quries analyzer tool for Mysql based RDBMS.
[if any mistake goes then edit it]
If you have a great RDMBS engine, the query parser must decide probably the most performant execution arrange for your query, regardless of how it's written.
I am talking about: an order from the tables inside your from and join clauses, or even the order of the filter-criteria shouldn't matter.
The very first factor to complete, is consider the execution plan and evaluate it. Check wether you've to include indexes, or modify indexes for example.
Your real question is also a little vague. Are you able to provide a sample of the query which you want to refactor ?
MySQL comes with an
EXPLAIN statement, which informs you what execution path the engine follows. You should use that to determine which changes to create:
EXPLAIN SELECT foo,bar from glurch WHERE baz > 1 ORDER BY foo;
Pay special focus on the
rows column, which shows the number of table rows have to be examined to complete that area of the query, along with the
Extra column, which show more to the point, the way the query will get performed.
For instance, if you notice "Using temporary" within the
Extra column, that always implies that the DB will have to write the query results to a brief table (possibly on disk), sort them, after which re-read them (or at best some of these).
You are able to avoid temporary tables along with other nasty performance murders by supplying appropriate indexes. And not simply enough indexes, but instead the correct indexes. Simply because you've indexed a column does not imply that index may be used inside your query.