See responses embedded....
"DeRyl" <[EMAIL PROTECTED]> wrote on 10/26/2004 12:58:38 PM:
> hi again,
>
> I always must search within keywords that are in dictionary_tbl and
always the
> searched word must be beginning-of-string
That's good. MySQL will use an index for that type of search
>
> maybe better solution is to use question like that:
>
> select c.*
> from dictionary_tbl d, dict_client_tbl k, client_tbl c
> where left(d.dictionary,length('some_word_part'))= 'some_word_part'
> and d.dictionaryid=k.dictionarytbl
> and k.clientid=c.clientid
> order by c.clientamount desc, c.clientname asc [! it is required sort
order]
>
> ??
>
> what do you think?
Respectfully, no. That would ruin your query's performance. Because you
are always trying to match the beginning of a word, MySQL can use an index
to find those words (as I tried to explain last message). In the query you
wrote, a column (dictionary) from each row will need to be transformed
(LEFT(...) )and the results of that transformation would need to be
compared to your target value. This is very slow. If you can possibly
avoid it, do not use functions on your columns in either WHERE or ON
clauses, especially on the LEFT side of a comparison.
>
> and index ont dict_client_tbl - you suggested an index on both fields...
> is it important in which order the fields should be?
That specifically depends on your query patterns but, in general, I do not
think that the order matters.
>
> and what about indexes on client_tbl?
> I think at least clientid must be a primary key
According to you showed us, it already is!
> but I also often use zoneid in where clause
>
> and fields clientamount and clientname are user in sort [order by
clause]
> what should I do with these fields and possible indexes?
That depends on how often you need to run those queries and how fast those
queries need to be. If you run queries that use those fields often or of
your performance is too slow to be acceptable then consider combining
those columns into an index or two.
>
> maybe is better way to sort searched data [sort wastes always a lot
> of time, but
> in this case it must be used]?
> does anybody have a better idea?
Yes, please. Any other ideas out there?
>
> regards
> DeRyl
>
>
> ----- Original Message -----
> From: <[EMAIL PROTECTED]>
> To: "DeRyl" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Tuesday, October 26, 2004 6:17 PM
> Subject: Re: question about indexes
>
>
> You asked about indexes for these tables
>
> I would make "dictionaryid" the PK of each dictionary_tbl.
> I would add an index to each dictionary_tbl for dictionaryword.
> I would make the PK for dict_client_tbl (clientid, dictionaryid)
>
> You also asked about fastest possible searches.
>
> Whenever you have to do a substring search (with LIKE or RLIKE) you
> almost always eliminate any use of indexes. The index I suggested above
is
> great for beginning-of-string or whole-string matches ("dictionaryword
> LIKE 'wordstart%' " or "dictionaryword ='thisword' ") but it fails to
> help for any other sub-string searches ("dictionaryword LIKE
'%endswith'"
> or "dictionaryword LIKE %wordpart%")
>
> Good Luck!
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
<previous responses clipped for space>
