Ryan:
1.) ...WHERE word LIKE '%word%' will never be fast.
2.) Since you said you have indexed the field, if you can limit your
searches to ...WHERE word LIKE 'word%', then you might want to look at
indexing on a subset of the field if it's a big one.
3.) You will get better performance if you perform routine maintenance on
your tables. Try running OPTIMIZE TABLE .... Or, if you can bring down
mysqld, try the myisamchk (-a -S) equivalents.
4.) Lastly, in addition to the other comments, I'd take a look at changing
some of the parameters in my.cnf. Here's a pertinent snip from the manual:
join_buffer_size
----------------
The size of the buffer that is used for full joins (joins that do not use
indexes). The buffer is allocated one time for each full join between two
tables. Increase this value to get a faster full join when adding indexes is
not possible. (Normally the best way to get fast joins is to add indexes.)
key_buffer_size
---------------
Index blocks are buffered and are shared by all threads. key_buffer_size is
the size of the buffer used for index blocks. Increase this to get better
index handling (for all reads and multiple writes) to as much as you can
afford; 64M on a 256M machine that mainly runs MySQL is quite common. If
you, however, make this too big (more than 50% of your total memory?) your
system may start to page and become REALLY slow. Remember that because MySQL
does not cache data read, that you will have to leave some room for the OS
filesystem cache. You can check the performance of the key buffer by doing
show status and examine the variables Key_read_requests, Key_reads,
Key_write_requests, and Key_writes. The Key_reads/Key_read_request ratio
should normally be < 0.01. The Key_write/Key_write_requests is usually near
1 if you are using mostly updates/deletes but may be much smaller if you
tend to do updates that affect many at the same time or if you are using
delay_key_write. See section 7.28 SHOW Syntax (Get Information About Tables,
Columns,...). To get even more speed when writing many rows at the same
time, use LOCK TABLES. See section 7.32 LOCK TABLES/UNLOCK TABLES Syntax.
Jim Beigel
Director of Software Development
Alabanza Corporation
[EMAIL PROTECTED]
740-282-2971 x.5205
> -----Original Message-----
> From: Ryan Hadley [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 06, 2001 6:09 PM
> To: [EMAIL PROTECTED]
> Subject: RE: Performance issues.
>
>
> Thanks for the quick response.
>
> The response time is slow... and the mysqld processes are what is
> hogging up
> the system.
>
> We do have indexes on the fields, but from what I understand,
> when you use a
> "LIKE" statement, it rarely uses an index.
>
> -Ryan
>
> -----Original Message-----
> From: Kent Hoover [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 06, 2001 5:42 PM
> To: [EMAIL PROTECTED]
> Subject: Performance issues.
>
>
> Ryan:
>
> If your response time for this query is slow, it is likely that an INDEX
> will help
> you. (Read about CREATE INDEX in the MySQL manual.
>
> If you don't already have an INDEX on the keyWord column, create one.
>
> If you can induce your customer/users to type more characters, that
> would help.
> WHERE keyWord LIKE 'salomi%' is much better for you than
> WHERE keyWord LIKE 's%' .
>
> NOTE, that if your response time to this query is good, there could be
> something
> other than MySQL running on your machine that is sucking your CPU dry.
> You might be able to spot it by running 'top' 'ps -ef' or whatever
> command is
> available for your machine.
>
> Cheers,
>
> Kent Hoover
>
>
>
>
> ---------------------------------------------------------------------
> 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
>
>
---------------------------------------------------------------------
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
