Hi.
On Wed, Feb 07, 2001 at 03:01:28PM -0500, [EMAIL PROTECTED] wrote:
[...]
> 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?
In your case, yes (see below).
> 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.
The problem is that MySQL cannot (yet) use indexes well for OR
clauses. You can see this with
EXPLAIN SELECT COUNT(*) AS totalWords FROM keywords WHERE keyword LIKE
'$keyword%' OR vkeyword LIKE '$keyword%';
If you can afford it (as you said above), just let keyword away and
test for vkeyword:
SELECT COUNT(*) AS totalWords FROM keywords WHERE vkeyword LIKE '$keyword%';
This should be quite fast (provided that there exists an index on
vkeyword).
> 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.
You would have to rewrite this, too.
> 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?
Not really. It's just that MySQL cannot handle the OR well.
If you really would need the two conditions, there are work-arounds
for that (e.g. creating a temporary table, ...), which I won't
elaborate on now.
> Is there a way to log all the sql calls?
Yes, there are two logs: an update log (contains only queries which
change the database content) and a general log (all queries). You
probably have to enable logging first. Have a look at the section
about server options in the manual.
Bye,
Benjamin.
---------------------------------------------------------------------
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
