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]
>
