The queries were...
> > select * from cookies left join users on cookies.uid=users.uid left join
> > sessions on users.uid=sessions.uid where
> > cookies.cookie="e3bd03382561eb3619b66fbea2af217d";
> >
> > select * from cookies left join users on cookies.uid=users.uid left join
> > extended on users.uid=extended.uid where
> > cookies.cookie="5226220e3b62cef71a13524ec7a413ac";
> >
> > (above queries have to be performed at the beginning of every webpage to
> > find the current user. i donīt really think they are slow; they just lock
> > because of something else.)
> >
> > i just tried the above query ten times. result: 7x 0.00sec, 1x0.01,
> > 1x0.02, 1x0.04. does that spell "slow"?
>
> Yes. I wouldn't let queries with times like these near a busy production
> server I was responsible for unless I was positive they wouldn't and
> couldn't happen more than a handful of times a day. :-)
Uh, we've have a lot of luck when we don't use left joins at all.
Left joins seem to often make things slower. Occasionally, much slower.
Other points to consider are these:
1) if you don't have an index, inserts will fly. We do some logging in MySQL
and we don't have many indexes on the tables. Other places, we need to do a
lot of queries on the log, so we have indexes. We will often batch copy data
from one table to another where the original table has few or no indexes and
the target table has several indexes.
2) as many other people have pointed out, use explain. It will show you an
estimated number of rows per table that it will work through. I generally
multiply those number by each other. You can quickly determine if you are
going to have a really long query this way.
> When these are locked, not only are they waiting for the selects that
> came before them to finish, they are blocking selects behind them in
> the queue from executing simultaneously with selects ahead of them.
>
> Selects in the queue for the users table that join to other tables
> (and you have plenty of them) will block updates/inserts to those
> other tables.
It has taken us a while to tune our database to do what the other post above
suggests. This is good advice. It has really made a difference for us. We
regularly saw an order of magnitude performance increase for several of these
steps.
Best,
Kyle
--
Quicknet has just released the following new products:
Internet SwitchBoard v5.5 and MicroTelco Gateway 2.0. We
have also added a new low cost carrier, Blue Star Telecom
to our award winning MicroTelco services. For more
information visit: www.quicknet.net or www.microtelco.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
