Tools for optimizing mysql query

[R. Rajesh Jeba Anbiah]

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: [url]http://rajeshanbiah.blogspot.com/[/url]

[Markus Popp]

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

Markus

[R. Rajesh Jeba Anbiah]

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: [url]http://rajeshanbiah.blogspot.com/[/url]

[Axel Schwenke]

"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

[Bill Karwin]

"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 ([url]http://dev.mysql.com/doc/refman/5.0/en/query-cache.html[/url]), 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.
[url]http://dev.mysql.com/doc/refman/5.0/en/optimization.html[/url]

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.

[R. Rajesh Jeba Anbiah]

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: [url]http://rajeshanbiah.blogspot.com/[/url]

[R. Rajesh Jeba Anbiah]

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: [url]http://rajeshanbiah.blogspot.com/[/url]

[R. Rajesh Jeba Anbiah]

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: [url]http://rajeshanbiah.blogspot.com/[/url]

[Gordon Burditt]

> 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

[R. Rajesh Jeba Anbiah]

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: [url]http://rajeshanbiah.blogspot.com/[/url]

[Bill Karwin]

"R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote in message
news:1139424717.214298.225240@g44g2000cwa.googlegr oups.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.

You should also read "SQL Performance Tuning" by Puter Gulutzan & Trudy
Pelzer. Good book.

> What I realised is that if there is a tool to suggest rewriting of
> queries, it would help junior or even [senior] programmers.

I think what you're looking for is a magic bullet, to make programmers not
have to design their systems well.

Programmers would get more benefit from identifying the bottlenecks, and
focusing their performance improvement efforts there. It does no good to
make a query run faster, if that's not the "long pole", hindering
performance in a given application.

For example, I used to support customers who used data-aware controls to
link drop-down lists in their GUI application to database tables. They were
confused when it became very slow and costly to populate the drop-down list
after the table grew to 100,000 records. This was not a problem in the
query; it was a perfectly simple and straightforward query (e.g. "select
one_column from one_table"), and there is little opportunity for rewriting
it to gain any magical improvement. The problem was the programming
decision to link a data-aware control to a table that was likely to grow
that much.

Application design decisions can have a lot more effect on performance than
an individual query, and query analyzer tools will never be able to suggest
alternative application designs.

Regards,
Bill K.

[Axel Schwenke]

[email]gordonb.qvhhc@burditt.org[/email] (Gordon Burditt) wrote:

>
> 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?

In fact this has been done in the 5.0 version of MySQL.
The optimizer can even use multiple indexes now:

root@localhost/test>select version();
+-----------+
| 5.0.18 |
+-----------+

root@localhost/test>root@localhost/test>show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(11) default NULL,
`c2` int(11) default NULL,
KEY `c1` (`c1`),
KEY `c2` (`c2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

root@localhost/test>explain select * from t1 where c1=500 or c2=600 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index_merge
possible_keys: c1,c2
key: c1,c2
key_len: 5,5
ref: NULL
rows: 2
Extra: Using union(c1,c2); Using where


XL

[Bill Karwin]

"Bill Karwin" <bill@karwin.com> wrote in message
news:dsdo5c092g@enews3.newsguy.com...

> You should also read "SQL Performance Tuning" by Puter Gulutzan & Trudy
> Pelzer. Good book.

Ack!! That's _Peter_ Gulutzan. Sorry, Peter -- I don't mean to imply that
you're a 'puter. <g>

Bill K.

[Axel Schwenke]

"Bill Karwin" <bill@karwin.com> wrote:

[snip]

What a nice long version of my message in
<6b22sd.dkn.ln@idefix.xl.local>


XL ;-)

[R. Rajesh Jeba Anbiah]

Axel Schwenke wrote:

> [email]gordonb.qvhhc@burditt.org[/email] (Gordon Burditt) wrote:

> >
> > 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?

>
> In fact this has been done in the 5.0 version of MySQL.
> The optimizer can even use multiple indexes now:

<snip>

> root@localhost/test>explain select * from t1 where c1=500 or c2=600 \G

<snip>

> Extra: Using union(c1,c2); Using where

Oh, great:-) Looks like that I have to unlearn those dirty old
tricks:-(

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

[R. Rajesh Jeba Anbiah]

Bill Karwin wrote:

> "R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote in message
> news:1139424717.214298.225240@g44g2000cwa.googlegr oups.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.

>
> You should also read "SQL Performance Tuning" by Puter Gulutzan & Trudy
> Pelzer. Good book.

Thanks a lot for the suggestion. Will definitely check this.

> > What I realised is that if there is a tool to suggest rewriting of
> > queries, it would help junior or even [senior] programmers.

>
> I think what you're looking for is a magic bullet, to make programmers not
> have to design their systems well.

YMMV. But, if we have bunch of navigator tools, why not a single
tool for query suggestion or at least having set of optimization tricks
so that it will help a lot.

<snip>

> For example, I used to support customers who used data-aware controls to
> link drop-down lists in their GUI application to database tables. They were
> confused when it became very slow and costly to populate the drop-down list
> after the table grew to 100,000 records. This was not a problem in the
> query; it was a perfectly simple and straightforward query (e.g. "select
> one_column from one_table"), and there is little opportunity for rewriting
> it to gain any magical improvement. The problem was the programming
> decision to link a data-aware control to a table that was likely to grow
> that much.

<snip>

IIRC, I read such performance centric systems wouldn't even use DB.
See my post in c.l.php
<news:1139430596.238800.276020@g14g2000cwa.googleg roups.com> (
[url]http://groups.google.com/group/comp.lang.php/msg/79f09c5d2e027ffd[/url] ) So,
drifting to OT, do you have any idea, how such system is implemented
'coz I'm extremely curious about the architecture.

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

[Bill Karwin]

"R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote in message
news:1139460512.347594.191790@f14g2000cwb.googlegr oups.com...

> IIRC, I read such performance centric systems wouldn't even use DB.
> See my post in c.l.php
> <news:1139430596.238800.276020@g14g2000cwa.googleg roups.com> (
> [url]http://groups.google.com/group/comp.lang.php/msg/79f09c5d2e027ffd[/url] ) So,
> drifting to OT, do you have any idea, how such system is implemented
> 'coz I'm extremely curious about the architecture.

[The thread referenced discusses sites that may use a database to log hits
on a given web site, e.g. a product catalog, so later reports can calcuate
the most popular web pages. Contrast with non-database solutions such as
proprietary hashed data files.]

Again, the best architecture depends on how you need to use it.

One might use a flat text file for logs (like the default Apache log file).
But in a configurable log format like Apache's, which fields should you
write out to the file? It depends on what kinds of reports you need to
produce.

One might use a data structure in memory, such as a self-balancing tree, and
serialize the tree to disk at intervals. Or you could alternatively
serialize the data structure to a blob field in a database. What to use as
the key for the tree depends on what sort of lookups you're going to do with
it. Are you interested in getting a sorted list of the most popular pages?
Or are you interested in the popularity of a specific page?

Another option is writing the web server logs directly to a database.
Here's an article about this:
[url]http://www.onlamp.com/pub/a/apache/2005/02/10/database_logs.html[/url]
There are pros and cons to this solution, spelled out in the article.
It might turn out to be faster to do an INSERT statement than output a
line directly to a flat log file, because DBMS operations can be configured
to use a write-back cache. This is another example of database
configuration making more of a difference than the syntax you use for the
SQL statement.

Yet another approach is to log in the traditional manner to text files, and
then parse the logs and import them into a relational database as an offline
task (probably at the time you rotate the log files). Then you can
structure the database in a variety of ways, to support the types of queries
you intend to do. But to do that, you do need to identify the types of
queries you intend to do.

Regards,
Bill K.

[R. Rajesh Jeba Anbiah]

Bill Karwin wrote:

> "R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote in message
> news:1139460512.347594.191790@f14g2000cwb.googlegr oups.com...

> > IIRC, I read such performance centric systems wouldn't even use DB.
> > See my post in c.l.php
> > <news:1139430596.238800.276020@g14g2000cwa.googleg roups.com> (
> > [url]http://groups.google.com/group/comp.lang.php/msg/79f09c5d2e027ffd[/url] ) So,
> > drifting to OT, do you have any idea, how such system is implemented
> > 'coz I'm extremely curious about the architecture.

>
> [The thread referenced discusses sites that may use a database to log hits
> on a given web site, e.g. a product catalog, so later reports can calcuate
> the most popular web pages. Contrast with non-database solutions such as
> proprietary hashed data files.]

<snip>

> One might use a data structure in memory, such as a self-balancing tree, and
> serialize the tree to disk at intervals. Or you could alternatively
> serialize the data structure to a blob field in a database. What to use as
> the key for the tree depends on what sort of lookups you're going to do with
> it. Are you interested in getting a sorted list of the most popular pages?
> Or are you interested in the popularity of a specific page?

<snip>

[Many thanks for everyone who answered in this thread. I was stuck
by connectivity and extremely sorry for my late respone]


Thanks for your thoughts. Think about the site that sells thousands
of products and they had to list the products according to their
popularity/number of hits on that product. If you take any product
sites, they list the products page by page but having the option to
sort by popularity/hits.

Do they update the hits lively on that products DB table? (Merchants
might add thousands of products at the same time) If not, how do they
provide that feature of sorting by popularity?

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

[Jerry Stuckle]

R. Rajesh Jeba Anbiah wrote:

> Bill Karwin wrote:
>

>>"R. Rajesh Jeba Anbiah" <ng4rrjanbiah@rediffmail.com> wrote in message
>>news:1139460512.347594.191790@f14g2000cwb.google groups.com...
>>

>>> IIRC, I read such performance centric systems wouldn't even use DB.
>>>See my post in c.l.php
>>><news:1139430596.238800.276020@g14g2000cwa.goog legroups.com> (
>>>[url]http://groups.google.com/group/comp.lang.php/msg/79f09c5d2e027ffd[/url] ) So,
>>>drifting to OT, do you have any idea, how such system is implemented
>>>'coz I'm extremely curious about the architecture.

>>
>>[The thread referenced discusses sites that may use a database to log hits
>>on a given web site, e.g. a product catalog, so later reports can calcuate
>>the most popular web pages. Contrast with non-database solutions such as
>>proprietary hashed data files.]

>
> <snip>
>

>>One might use a data structure in memory, such as a self-balancing tree, and
>>serialize the tree to disk at intervals. Or you could alternatively
>>serialize the data structure to a blob field in a database. What to use as
>>the key for the tree depends on what sort of lookups you're going to do with
>>it. Are you interested in getting a sorted list of the most popular pages?
>>Or are you interested in the popularity of a specific page?

>
> <snip>
>
> [Many thanks for everyone who answered in this thread. I was stuck
> by connectivity and extremely sorry for my late respone]
>
>
> Thanks for your thoughts. Think about the site that sells thousands
> of products and they had to list the products according to their
> popularity/number of hits on that product. If you take any product
> sites, they list the products page by page but having the option to
> sort by popularity/hits.
>
> Do they update the hits lively on that products DB table? (Merchants
> might add thousands of products at the same time) If not, how do they
> provide that feature of sorting by popularity?
>
> --
> <?php echo 'Just another PHP saint'; ?>
> Email: rrjanbiah-at-Y!com Blog: [url]http://rajeshanbiah.blogspot.com/[/url]
>

Hits don't count. Sales do. Whatever sells the most has the greatest
popularity.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
[email]jstucklex@attglobal.net[/email]
==================

[Bill Karwin]

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:BOKdnTZ-G6D0iW7eRVn-sw@comcast.com...

>> Do they update the hits lively on that products DB table? (Merchants
>> might add thousands of products at the same time) If not, how do they
>> provide that feature of sorting by popularity?

>
> Hits don't count. Sales do. Whatever sells the most has the greatest
> popularity.

I agree. Also, generating popularity from recorded sales is easy to do as
an offline task; it doesn't need to be done at the moment of the sale.

However, there's also another Amazon.com style of report which is "customers
who viewed this item also viewed the following items." This is different
than sorting a given list of products by popularity, and I suspect it has a
much different database structure to track it. It could also be generated
as an offline task, from sophisticated web server log analysis tools. Since
Amazon's servers are certainly numerous, the logs are probably merged and
analyzed offline anyway.

Regards,
Bill K.