List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:August 4 1999 8:25pm
Subject:Re: Speed enchancements for MySQL
View as plain text  
Craig, you don't have any indexes on the table!  That's why the
queries are taking so long - MySQL has to read every row in the
table in order to find out which ones match.

For the examples you gave, you only need an index on the "NUMBER"
column.  You can add the index like this:

mysql> CREATE INDEX NUMBER_index ON email_list (NUMBER);

You only need to add the index once - MySQL handles updating the
index whenever you insert, delete or update rows in the table.

Your queries should be *very* fast after you add this index.  It
looks like this NUMBER column is actually a primary key for your
table (each row in the table has a unique value for NUMBER, and
that value is the main way of identifying a row).  If so, you
might want to create a UNIQUE index:

mysql> CREATE UNIQUE INDEX NUMBER_index ON email_list (NUMBER);

Tim


At 15:30, 19990804, Craig Vincent wrote:
>Tim,
>
>
>Most tables work fine, it is only for this one...here is the explain of it.
>
>| Field      | Type        | Null | Key | Default | Extra |
>
>| EMAIL   | char(120)                                       | YES  |     |
>NULL
>| STATUS  | enum('CONFIRM','UNCONFIRM','BLOCKED','REMOVED') | YES  |     |
>NULL
>| NUMBER  | int(9)                                          | YES  |     |
>NULL
>| referid | char(20)                                        | YES  |     |
>NULL
>| date    | datetime                                        | YES  |     |
>NULL
>| URL     | char(120)                                       | YES  |     |
>NULL
>| TITLE   | char(60)                                        | YES  |     |
>NULL
>| TEXT    | char(255)                                       | YES  |     |
>NULL
>
>
>There are 24,643 rows in the table currently.
>Most queries should only produce one row...
>
>SELECT status FROM email_list where number = $number;
>is the main command, the other is to update status set status = $newstatus
>where number = $number;
>
>$number is always unique.  These queries generally take 3-5 seconds each.
>
>I did five queries a simple SELECT status FROM email_list where number = 1
>...here are the response times.
>
>1 - 3.67 sec
>2 - 8.80 sec
>3 - 3.55 sec
>4 - 3.73 sec
>5 - 4.25 sec
>
>--------------
>mysql  Ver 9.16 Distrib 3.21.33b, for pc-bsdi3.1 (i386)
>
>Connection id:          1079
>Current database:       richard
>Server version          3.21.33b-log
>Protocol version        10
>Connection              Localhost via UNIX socket
>UNIX socket             /tmp/mysql.sock
>Uptime:                 2 hours 55 min 45 sec
>
>Running threads: 2  Questions: 5631  Opened_tables: 76  Flush tables: 3
>Open tables: 58
>--------------
>
>
>Any suggestions would be very appreciated.
>
>Sincerely,
>
>Craig Vincent
>
>
>----- Original Message -----
>From: Thimble Smith <tim@stripped>
>To: Craig Vincent <webmaster@stripped>
>Cc: <mysql@stripped>
>Sent: Wednesday, August 04, 1999 3:11 PM
>Subject: Re: Speed enchancements for MySQL
>
>
>> MySQL can handle tables with millions of rows, and 20,000 should pose
>> no problem.
>>
>> If you send us the output of EXPLAIN on the slow queries, and also
>> how many rows are returned and how many rows are in each table, we
>> should be able to help out.
>>
>> Tim
>>
>>
>> At 15:03, 19990804, Craig Vincent wrote:
>> >I'm running a mailing list organizer using mysql.
>> >
>> >One table now has grown to over 20,000 records and since then queries and
>updates are taking 5-6 seconds if not more before completion.
>> >
>> >Are there anyways to optimize the database or perhaps use a ramdrive to
>help improve the speed of the queries?
>> >
>> >Sincerely,
>> >
>> >Craig Vincent
>> >
>>
>
Thread
Speed enchancements for MySQLCraig Vincent4 Aug
  • Re: Speed enchancements for MySQLThimble Smith4 Aug
  • Re: Speed enchancements for MySQLChristian Mack5 Aug
Re: Speed enchancements for MySQLThimble Smith5 Aug