At 12:35 PM 6/27/2002, you wrote:
> I am currently using mysql 4.01 with caching for web page serving. I
>pull several records out with a like statement on a text field. The db
>consists of about 200,000 records right now and it is using 100% of the cpu
>when the search is ran.
> Since this is a critical function, it is ran on almost every web
>request. Without being cached, the search can take up to 2000-3000
>milliseconds. With cache it is around 40-50 ms, but there are so many
>different queries, cache is almost useless. It is killing the machine though
>as nothing else can be ran on it.
>
>The machine specs:
>Windows 2000
>Pentium III 733 MHZ
>756 SDRAM
>SCSI Drives.
>MYSQL++ API
>
>I am looking into having the records approach 1 million records soon and
>would really like to minimize cpu and search time. Any suggestions on a new
>machine or does this search time and cpu usage not sound right? Thanks in
>advance.
>
>Jeremy Beha
>
Jeremy,
If you posted the sql you are executing and perhaps the table
structure, some of us could optimize the sql so it won't take so long.
If you are using LIKE "%string%" then it is not using the index
and has to search every single record. To use the index with Like you must
remove the first "%" , as in LIKE "string%" so it finds values starting
with "string". If you absoultely must search for a word within a string
then take a look at FullText index. It may solve your problem (although it
is not perfect). It works on MyISAM tables and not InnoDb tables.
Mike
---------------------------------------------------------------------
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
