On Thu, Sep 06, 2001 at 05:26:53PM +0200, Henning Schroeder wrote:
> >Find all the queries that interact with this table. EXPLAIN them. Time them.
>
> *all* of them? there are lots.
Well, perhaps not initially but you may want to have EXPLAINed a majority
of the queries that are issued against your database and probably all the
ones that occur often.
> as said before, it is the main user table. a
> user account is updated every time somebody request a page. and i need to
> join to that table very often to find out, when the corresponding user
> logged in last and what his name is. bad design? bad idea? what do you think?
It does seem to be a point of contention.
> i could split these queries below into multiple queries, first getting the
> userid and then firing off another query to get the name (without a join).
> but i thought letting the database handle this should be faster.
I guess only benchmarking will show if it's faster. Generally though I've
found that many very quick queries is preferable to MySQL than one large,
slow query, even if the many queries take longer (all added together) than
the one large query. This assumes a web-backend type of application.
> > > 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. :-)
> >
> >Post the output of EXPLAIN for these queries. Along with the create defns
> >for these other tables.
<Explanations snipped>
> doesnīt look too bad for me. except that timing information at the end :-(.
> but thatīs because the database is almost completely locked up again
> (processlist full of queries with Locked status).
>
> the table definition is quite long, iīll append it to the end of the mail.
Yes, I see what you mean. :-)
Since uid is the primary key for cookies, users, sessions and extended, I
wonder if the database could be made happier overall by breaking the queries
down...
select uid from cookies where cookie = "5226220e3b62cef71a13524ec7a413ac"
select * from users where uid=$uid
select * from sessions where uid=$uid
select * from extended where uid=$uid
These might ease the bottlenecks. An update to sessions (for example)
will only delay for the (relatively quick) third query instead of on the
first bigger query. Conversely, only the third query will delay updates
to sessions.
I assume (and I'm guessing here) that the bottlenecks are being caused by
regular updates to sessions and, to a lesser extent, extended. Perhaps some
normalisation of sessions might help?
Chances are I'm missing an important subtlety in your use of left join
though, so feel free to flame (offlist)... :-)
> well, yes. i noticed that :-(. thank you for your good explanation though.
> but i think itīs probably not very fruitful to look for queries in the
> processlist that have "locked" status, because they are probably not the
> slow ones that caused the block. am i correct?
Pretty much. While queries that spend too much time in a locked state
aren't directly to blame (I blame the queries that lock the tables :-),
blame isn't really at issue: either make the tables not locked so
much or make the queries less dependent on the locked tables. :-)
> i also tried logging the queries that appear often with "copying to temp
> table" status and now have a nice set of them, though i donīt quite
> understand *why* the are copying. below are two:
> (the rows count is *way* to high, probably because the timestamps are
> ancient by the time i ran explain select)
>
> select count(*) from adviews where click="f" and uid=7618 and cid=11 and
> datestamp>=999782664
>
> mysql> explain select count(*) from adviews where click="f" and uid=7618
> and cid=11 and datestamp>=999782664 ;
>
>+---------+------+-------------------------+------+---------+-------+------+------------+
> | table | type | possible_keys | key | key_len | ref | rows
> | Extra |
>
>+---------+------+-------------------------+------+---------+-------+------+------------+
> | adviews | ref | uid,click,cid,datestamp | uid | 4 | const | 2365
> | where used |
>
>+---------+------+-------------------------+------+---------+-------+------+------------+
> 1 row in set (0.00 sec)
>
> index on all fields, optimized nightly. actually i just want to know
> whether there is a corresponding entry or not. count(*) is supposed to be
> fast, i thought??
AIUI, count(*) is only fast on whole tables because the number of rows in
a table is stored separately - the rows don't have to be counted. With
WHERE clauses, count(*) is only faster than returning all the rows because
the rows don't all have to be sent to the client.
If you only want to know if there is an entry or not, it /might/ be faster
to do a...
select uid # or whatever
from adviews
where click="f"
and uid=7618
and cid=11
and datestamp>=999782664
limit 1
...and check if you get any rows back.
Perhaps an index on (uid,datestamp) or even (uid,cid,click,datestamp)
instead of on (uid) *might* help your query. (At the expense of hindering
inserts.)
Also, imposing an upper limit on the datestamp might stop queries from
taking too long before concluding there are no rows.
> select count(*) from chat where type="msg" and timestamp>999783881
>
> mysql> explain select count(*) from chat where type="msg" and
> timestamp>999783881 ;
> +-------+-------+---------------+-----------+---------+------+------+------------+
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra |
> +-------+-------+---------------+-----------+---------+------+------+------------+
> | chat | range | timestamp | timestamp | 4 | NULL | 753 | where
> used |
> +-------+-------+---------------+-----------+---------+------+------+------------+
> 1 row in set (0.00 sec)
>
> hello please? wtf is mysql coping to temp table?
I imagine the 753-ish rows with timestamp>999783881. Perhaps an index
(timestamp,msg) on chat *might* help *this* query.
ATB,
Wesley.
---------------------------------------------------------------------
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
