I apologize for not being more specific. The query that is taking too long is : SELECT * FROM Stories WHERE UCASE(Path) LIKE 'toUpper(path)%'; Path is a 255 char field with an index on it. path is variable passed in. Thanks again.
Jeremy Beha ----- Original Message ----- From: "Benjamin Pflugmann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, June 27, 2002 3:08 PM Subject: Re: Hardware Planning > Hi. > > On Thu 2002-06-27 at 13:35:28 -0400, [EMAIL PROTECTED] 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? > > This depends on your table design and queries. If you are only pulling > out one row with a const reference (e.g. "user='foo'"), the query time > seems far too slow. If you are doing a complex join over multiple > tables which has to scan at least one table in whole, the query time > could be considered reasonable or even fast. It depends. > > In short: Provide more information. > > Regards, > > Benjamin. > > -- > [EMAIL PROTECTED] > --------------------------------------------------------------------- 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
