I have googled a lot, but couldn't find it. But, I hope that there
will be a tool for query optimization (a tool that can automatically
detect the flaw in the query and suggest rewriting). If anyone of you
know any tools, kindly post here. TIA

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

Use the EXPLAIN keyword in front of your SELECT statements - that tell's you
exactly how MySQL executes the query.

Markus

Markus Popp wrote:
> Use the EXPLAIN keyword in front of your SELECT statements - that tell's you
> exactly how MySQL executes the query.

Thanks for your advice. I know, EXPLAIN.. but what I'm looking for
is a tool that can automatically optimize query statements.

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

"R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote:
> Markus Popp wrote:
>> Use the EXPLAIN keyword in front of your SELECT statements - that tell's you
>> exactly how MySQL executes the query.
>
> Thanks for your advice. I know, EXPLAIN.. but what I'm looking for
> is a tool that can automatically optimize query statements.

This "tool" is named BRAIN and it's installed on all of the HUMAN
hardware on delivery. However it needs training to master some of
the more complex tasks.

SCNR


XL

"R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote in message
news:1139018189.443513.81980@g44g2000cwa.googlegro ups.com...
> what I'm looking for is a tool that can automatically optimize query
> statements.

Optimizing queries is a combination of choosing the right SQL syntax,
clearly, but also depends heavily on choices in the database schema and
server configuration. You can often get some improvement from changing SQL
query syntax, but much more improvement from adding indexes, or redesigning
the relationships between tables. You can also improve performance
dramatically through choices in MySQL configuration, including resizing
server-side caches, choices between storage engines, etc. Even hardware
choices on the server host can be more important than optimizing SQL syntax.

The best choices for performance also depend on how you need to use the
database. For instance, you might gain a lot of benefit from using MySQL's
query cache (http://dev.mysql.com/doc/refman/5.0/...ry-cache.html), but
only if you use certain types of queries and if the database is unchanging
most of the time. This is something that a query analyzer tool would not be
able to know.

There's a whole chapter in the MySQL documentation about improving
performance. Only some of the suggestions have to do with query syntax.
You should read the whole chapter if you are tasked with designing
high-performance applications with MySQL.
http://dev.mysql.com/doc/refman/5.0/...imization.html

I also advise people on matters of performance improvement to be sure they
are addressing the right part of the problem. The database queries may not
be the bottleneck in your system at all.

For example, once I was asked to "speed up the database" in a web
application. After running the application in a profiler, I found that the
app was spending 80% of its processing time parsing its generated HTML to
auto-populate form fields. The processing time for database operations was
insignificant in comparison.

Regards,
Bill K.

Bill Karwin wrote:
> "R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote in message
> news:1139018189.443513.81980@g44g2000cwa.googlegro ups.com...
> > what I'm looking for is a tool that can automatically optimize query
> > statements.

<snip>
> You can often get some improvement from changing SQL
> query syntax, but much more improvement from adding indexes, or redesigning
> the relationships between tables. You can also improve performance
> dramatically through choices in MySQL configuration, including resizing
> server-side caches, choices between storage engines, etc.
<snip lot of great info>

Thanks for your generous lengthy response. I have some knowledge on
optimization as I have already gone through the manual, optimization
articles and "High performance MySQL" by Jeremy et al.

What I realised is that if there is a tool to suggest rewriting of
queries, it would help junior or even programmers. Say, an OR
condition in query might ignore any index and will result in complete
table scan--the workaround is to use UNION. So, if there is any such
tool to suggest such things or even automatically rewrite such queries
and could compare the difference, it will be of great help. I'd thought
there must be such tool exist; but unfortunately, it doesn't seem so.

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

Bill Karwin wrote:
> "R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote in message
> news:1139018189.443513.81980@g44g2000cwa.googlegro ups.com...
> > what I'm looking for is a tool that can automatically optimize query
> > statements.

<snip>
> You can often get some improvement from changing SQL
> query syntax, but much more improvement from adding indexes, or redesigning
> the relationships between tables. You can also improve performance
> dramatically through choices in MySQL configuration, including resizing
> server-side caches, choices between storage engines, etc.
<snip lot of great info>

Thanks for your generous lengthy response. I have some knowledge on
optimization as I have already gone through the manual, optimization
articles and "High performance MySQL" by Jeremy et al.

What I realised is that if there is a tool to suggest rewriting of
queries, it would help junior or even programmers. Say, an OR
condition in query might ignore any index and will result in complete
table scan--the workaround is to use UNION. So, if there is any such
tool to suggest such things or even automatically rewrite such queries
and could compare the difference, it will be of great help. I'd thought
there must be such tool exist; but unfortunately, it doesn't seem so.

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

Bill Karwin wrote:
> "R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote in message
> news:1139018189.443513.81980@g44g2000cwa.googlegro ups.com...
> > what I'm looking for is a tool that can automatically optimize query
> > statements.

<snip>
> You can often get some improvement from changing SQL
> query syntax, but much more improvement from adding indexes, or redesigning
> the relationships between tables. You can also improve performance
> dramatically through choices in MySQL configuration, including resizing
> server-side caches, choices between storage engines, etc.
<snip lot of great info>

Thanks for your generous lengthy response. I have some knowledge on
optimization as I have already gone through the manual, optimization
articles and "High performance MySQL" by Jeremy et al.

What I realised is that if there is a tool to suggest rewriting of
queries, it would help junior or even programmers. Say, an OR
condition in query might ignore any index and will result in complete
table scan--the workaround is to use UNION. So, if there is any such
tool to suggest such things or even automatically rewrite such queries
and could compare the difference, it will be of great help. I'd thought
there must be such tool exist; but unfortunately, it doesn't seem so.

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/

> Thanks for your generous lengthy response. I have some knowledge on
>optimization as I have already gone through the manual, optimization
>articles and "High performance MySQL" by Jeremy et al.
>
> What I realised is that if there is a tool to suggest rewriting of
>queries, it would help junior or even programmers. Say, an OR
>condition in query might ignore any index and will result in complete
>table scan--the workaround is to use UNION. So, if there is any such
>tool to suggest such things or even automatically rewrite such queries
>and could compare the difference, it will be of great help. I'd thought
>there must be such tool exist; but unfortunately, it doesn't seem so.

You really can't optimize one query. You need to collectively
optimize all of them together. You can make one query which you
use once a year at tax time faster by adding an index or two which
slows down every insert, which might be done at the rate of thousands
an hour. There is a good chance that adding the index is *NOT* a
good tradeoff for overall performance.

If it *is* possible to just re-write one query, as in the example
above with OR vs. UNION, wouldn't it be a good idea to put this
code in the server?

Gordon L. Burditt

Gordon Burditt wrote:
<snip>
> You really can't optimize one query. You need to collectively
> optimize all of them together. You can make one query which you
> use once a year at tax time faster by adding an index or two which
> slows down every insert, which might be done at the rate of thousands
> an hour. There is a good chance that adding the index is *NOT* a
> good tradeoff for overall performance.

Yes. But, what am I expecting in the case of optimization is
"something is better than nothing" logic.

> If it *is* possible to just re-write one query, as in the example
> above with OR vs. UNION, wouldn't it be a good idea to put this
> code in the server?

Yup.. :-)

--
<?php echo 'Just another PHP saint'; ?>
Email: rrjanbiah-at-Y!com Blog: http://rajeshanbiah.blogspot.com/