hi again,
I always must search within keywords that are in dictionary_tbl and always the
searched word must be beginning-of-string
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?
and index ont dict_client_tbl - you suggested an index on both fields...
is it important in which order the fields should be?
and what about indexes on client_tbl?
I think at least clientid must be a primary key
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?
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?
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
"DeRyl" <[EMAIL PROTECTED]> wrote on 10/26/2004 10:19:21 AM:
> hello,
>
> I have a main table called client_tbl which contains:
>
> clientid bigint(14) [primary key]
> zoneid smallint(2) [secondary key]
> clientame varchar(255)
> clientaddress
> clientemail
> clientwww
> clientinfo
> clientamount decimal(6,1)
>
> and a lot of dictionaries that are int he same schema - so I
> describe them just
> like that:
>
> dictionary_tbl:
> dictionaryid int
> dictionaryword varchar(32)
>
> and
>
> dict_client_tbl:
> clientid
> dictionaryid
>
> all questions I use are just like that
>
> select c.*
> from dictionary_tbl d, dict_client_tbl k, client_tbl c
> where d.dictionary like 'some_eord_part%'
> and d.dictionaryid=k.dictionarytbl
> and k.clientid=c.clientid
> order by c.clientamount desc, c.clientname asc [! it is required sort
order]
>
> there can be a few combinantions with more than one dictionaries
>
> my questions are:
> 1. how should be a good set of indexess on these tables [search speed is
a
> primary requirement]?
> 2. how to optimize search speed?
>
> client table contains about 1200000 records
> disctionaries from 300 to 250000 records
>
> what can I write more to better problem describe?
>
> regards
> DeRyl
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
