Maybe we're doing something really wrong in the way we do each look up.
We have one table with all the defintions on it's own row.
Then we have built off of that table another table that is only the distinct
words, no definitions.
This is because if a word has 10 definitions, it makes it hard to limit the
number of results returned from the first table to 5 words, because we don't
know how many definitions each word has.
We have two coloumns that we check the search on. keyWord and vKeyWord.
keyWord is basically the non-display keyword. without spaces and junk. We
could remove that from the searching, if it'd help. Would that make much of
a difference?
So first we do a:
"SELECT COUNT(*) AS totalWords FROM keywords WHERE keyword LIKE '$keyword%'
OR vkeyword LIKE '$keyword%'"
to get the number of entries they can page through.
Then we do a:
"SELECT vkeyWord FROM keywords WHERE keyword LIKE '$keyword%' OR vkeyword
LIKE '$keyword%' LIMIT $startWordCount, 5"
($startWordCount depends on which page they are on)
And build a list of the words we received.
Then we do a:
"SELECT * FROM Random WHERE vKeyWord IN ($word1, $word2, $word3, $word4,
$word5) ORDER BY ID"
And *poof* we have all the definitions for 5 words, and the maximum number
of words that there could be.
Are we doing anything obviouslly wrong in this?
Is there a way to log all the sql calls?
-----Original Message-----
From: Jason Terry [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 07, 2001 11:01 AM
To: Jim Beigel; [EMAIL PROTECTED]
Subject: Re: Performance issues.
Cool, I like this info. (especially key_buffer_size stuff)
However, I was running my numbers against what you said would be good. And
this is what I came up with...
The Key_reads/Key_read_request = 0.002 (much less than you suggest so that
is good... I think)
However this one worries me a bit...
Key_write/Key_write_requests = 0.087 (way lower than 1)
Does this mean that I am sending WAY to many un-needed UPDATE requests?
----- Original Message -----
From: "Jim Beigel" <[EMAIL PROTECTED]>
To: "Ryan Hadley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, February 07, 2001 6:32 AM
Subject: RE: Performance issues.
> 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
---------------------------------------------------------------------
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
