PHWinfo - Afficher un message

25/03/2008, 16h17

The field has up to 1000 characters, usually well over 255. It is
searched constantly (2-3 times every second). The smallint values are
scanned up to 40 times a second. So, are you saying that the entire
MYI file is dumped into the keycache? If so, your suggestion to create
a separate table with PK and text field seems to be the only sensible
decision. Thanks in advance,
Christian

On Tue, Mar 25, 2008 at 10:11 AM, Arthur Fuller <fuller.artful@gmail.com> wrote:
> What is the size of the text field you're fulltext indexing? How often is
> that index used? You might be best off to create a table containing only
> that column and a PK that is equal to the PK in the original table. You
> might also keep a portion of the text field (say 50 characters) in the
> original table, for head-first indexing.
>
> Arthur
>
>
>
> On Tue, Mar 25, 2008 at 9:56 AM, J. Christian Hesketh
> <ionchannels@gmail.com> wrote:
>
> >
> >
> >
> > Hi,
> > I have created a rather large table containing about 16M records. Most
> > of the indexed fields are smallint, but there is one field that is a
> > text field that I am using fulltext indexing on. The total size of the
> > smallint indexes is only about 30 MB, but the fulltext index brings
> > the total index size to about 2 GB. My question is: When mysql adds
> > indexes to the keycache, does it add each index individually or does
> > it dump the entire .MYI file to the keycache. If it is the latter, I
> > should move the fulltext index to another table.
> > Thanks in advance,
> > Christian
> >
> > --
> > J. Christian Hesketh M.Sc.
> > CEO - Ion Channel Media Group
> > 2028 Harvard Avenue, Suite 103
> > Montreal, QC
> > CANADA H4A 2V9
> >
> > Tel: +1(514)245-8107
> >
> > http://www.IonChannelMedia.com
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=f...tful@gmail.com
> >
> >
>
>

--
J. Christian Hesketh M.Sc.
CEO - Ion Channel Media Group
2028 Harvard Avenue, Suite 103
Montreal, QC
CANADA H4A 2V9

Tel: +1(514)245-8107

http://www.IonChannelMedia.com

25/03/2008, 16h17	#3
J. Christian Hesketh Messages: n/a Hébergeur:	Re: Indexing question The field has up to 1000 characters, usually well over 255. It is searched constantly (2-3 times every second). The smallint values are scanned up to 40 times a second. So, are you saying that the entire MYI file is dumped into the keycache? If so, your suggestion to create a separate table with PK and text field seems to be the only sensible decision. Thanks in advance, Christian On Tue, Mar 25, 2008 at 10:11 AM, Arthur Fuller <fuller.artful@gmail.com> wrote: > What is the size of the text field you're fulltext indexing? How often is > that index used? You might be best off to create a table containing only > that column and a PK that is equal to the PK in the original table. You > might also keep a portion of the text field (say 50 characters) in the > original table, for head-first indexing. > > Arthur > > > > On Tue, Mar 25, 2008 at 9:56 AM, J. Christian Hesketh > <ionchannels@gmail.com> wrote: > > > > > > > > > Hi, > > I have created a rather large table containing about 16M records. Most > > of the indexed fields are smallint, but there is one field that is a > > text field that I am using fulltext indexing on. The total size of the > > smallint indexes is only about 30 MB, but the fulltext index brings > > the total index size to about 2 GB. My question is: When mysql adds > > indexes to the keycache, does it add each index individually or does > > it dump the entire .MYI file to the keycache. If it is the latter, I > > should move the fulltext index to another table. > > Thanks in advance, > > Christian > > > > -- > > J. Christian Hesketh M.Sc. > > CEO - Ion Channel Media Group > > 2028 Harvard Avenue, Suite 103 > > Montreal, QC > > CANADA H4A 2V9 > > > > Tel: +1(514)245-8107 > > > > http://www.IonChannelMedia.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/mysql?unsub=f...tful@gmail.com > > > > > > -- J. Christian Hesketh M.Sc. CEO - Ion Channel Media Group 2028 Harvard Avenue, Suite 103 Montreal, QC CANADA H4A 2V9 Tel: +1(514)245-8107 http://www.IonChannelMedia.com