List:General Discussion« Previous MessageNext Message »
From:Spiderman Date:August 11 1999 12:01am
Subject:RE: question about update
View as plain text  
user = varchar(32)   PRI
email = varchar(64)  MUL
last_activity = date MUL
last_login = date    MUL
subscribed = text 

The update statement
update client set subscribed = 'xxxx:# yyyy:# zzzz:#' where user = 'me'

mysql> explain select * from client where user = 'user';
+--------+-------+---------------+---------+---------+---------+------+-------+
| table  | type  | possible_keys | key     | key_len | ref     | rows | Extra |
+--------+-------+---------------+---------+---------+---------+------+-------+
| client | const | PRIMARY       | PRIMARY |      32 | user    |    1 |       |
+--------+-------+---------------+---------+---------+---------+------+-------+
1 row in set (0.00 sec)

-billy

From: Quentin Bennett <quentin.bennett@stripped>
Subject: RE: question about update
Date: Wed, 11 Aug 1999 08:03:22 +1200
Message-ID:
<B0018544@stripped>,<81420E6207D4D111A3BC00805FC7FA42215DC5@pegasus>

quentin.bennett> I'm no expert on databases, but I'll bet that someone who is will want
to
quentin.bennett> see your update statement, table keys and the output of an EXPLAIN, to
see
quentin.bennett> what keys are being used.
quentin.bennett> 
quentin.bennett> Regards
quentin.bennett> 
quentin.bennett> Quentin Bennett
quentin.bennett> Senior Analyst
quentin.bennett> Transport Systems Division
quentin.bennett> Trilogy Computer Systems
quentin.bennett> Phone : +64 9 358 9720
quentin.bennett> Fax : +64 9 309 4142
quentin.bennett> mailto: quentin.bennett@stripped
quentin.bennett> web: http://www.trilogy.co.nz
quentin.bennett> 
quentin.bennett> > -----Original Message-----
quentin.bennett> > From:	Spiderman [SMTP:billy@stripped]
quentin.bennett> > Sent:	Wednesday, August 11, 1999 7:28 AM
quentin.bennett> > To:	mysql@stripped
quentin.bennett> > Subject:	question about update
quentin.bennett> > 
quentin.bennett> > Situation:
quentin.bennett> > 
quentin.bennett> > Table: client is 31 columns by ~60k rows
quentin.bennett> > One of these columns is the subscribed-to field which is of type
text.
quentin.bennett> > 
quentin.bennett> > The client table is updated every so often by a client changing
their
quentin.bennett> > subscriptions or information. This update seems to take a long
time.
quentin.bennett> > I want to determine which factors impact the speed of the update
the
quentin.bennett> > most.
quentin.bennett> > 
quentin.bennett> > Is it the: number of columns  = 31
quentin.bennett> >            size of the table ~= 60k
quentin.bennett> >            updating a text type field
quentin.bennett> > 
quentin.bennett> > The problem is whenever a client logs on and read an article, he
is
quentin.bennett> > checked against the table to see if he is subscribed to that
quentin.bennett> > list. When the client is updateing their info, the table is write
quentin.bennett> > locked and the selects to access the articles are queued behind
it.
quentin.bennett> > I need to increase the speed of the update.
quentin.bennett> > 
quentin.bennett> > If there are too many columns I can split up the table, but I
want to
quentin.bennett> > make sure that that will give me significant gains since it is
not a
quentin.bennett> > trivial matter to update all the scripts which touch this table.
quentin.bennett> > 
quentin.bennett> > -billy
quentin.bennett> > 
quentin.bennett> > 
quentin.bennett> > 
quentin.bennett> >
---------------------------------------------------------------------
quentin.bennett> > Please check
"http://www.mysql.com/Manual_chapter/manual_toc.html" before
quentin.bennett> > posting. To request this thread, e-mail
mysql-thread9859@stripped
quentin.bennett> > 
quentin.bennett> > To unsubscribe, send a message to the address shown in the
quentin.bennett> > List-Unsubscribe header of this message. If you cannot see it,
quentin.bennett> > e-mail mysql-unsubscribe@stripped instead.
quentin.bennett> 
quentin.bennett> 
quentin.bennett> 
quentin.bennett> 
quentin.bennett>
==========================================================================
quentin.bennett> This e-mail message has been scanned and cleared by MailMarshal
quentin.bennett> MailMarshal Web Site: http://www.mailmarshal.com/
Thread
question about updateSpiderman10 Aug
RE: question about updateQuentin Bennett10 Aug
RE: question about updateSpiderman11 Aug
  • Re: question about updateMartin Ramsch11 Aug
Re: question about updateChristian Mack11 Aug