----- Forwarded by Shawn Green on 10/26/2004 01:52 PM -----
ok - I just forgot this idea with left comparision
thanks for explanation
about indexes i thought about another index on client_tbl containing
fields:
clientid and zoneid
both fields are used very often
I also thought about index containing two fields: clientamount desc and
clientname asc [but for first 32 signs..]
how do you think - would it be used within order by?
all queries must be very very fast - search time above 1 sec. is expected
this is a large internet search engine [somethink like address book with
such
conditions as: client keywords, branch keywords, city name, state,
province,
phone number and zone represented in listbox with zoneid|zonename pair
values]
with lots of data especially in client_tbl which should be displayed very
fast
like I wrote before
all displayed values are stored in client_tbl, but search must work with
at
least with all described conditions used in the same time
does anybody have a tips, good advices or something that can be used for
the
best solution which is possible?
regards in advande
DeRyl
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "DeRyl" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, October 26, 2004 7:26 PM
Subject: Re: question about indexes
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>
