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

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

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

hi lists,

I want to count the number of rows containing "'" aphostrophe in a particular field. I tried with

select count(*) from table where field like "%'%"

i get only 0 counts. but I am sure that exists in many rows.

how to query them

Saravanan


__________________________________________________ __________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i...Dypao8Wcj9tAcJ

You need to escape the apostrophe first

so

select count(*) from table where field like '%\'%'

On Tue, Mar 25, 2008 at 2:37 PM, Saravanan <suzuki_babu@yahoo.com> wrote:

> hi lists,
>
> I want to count the number of rows containing "'" aphostrophe in a
> particular field. I tried with
>
> select count(*) from table where field like "%'%"
>
> i get only 0 counts. but I am sure that exists in many rows.
>
> how to query them
>
> Saravanan
>
>
>
> __________________________________________________ __________________________________
> Be a better friend, newshound, and
> know-it-all with Yahoo! Mobile. Try it now.
> http://mobile.yahoo.com/;_ylt=Ahu06i...Dypao8Wcj9tAcJ
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=pchapman@nc.rr.com
>



--
build our city at http://free-dc.myminicity.com !

Hi Lists,

I am using the version 5.0.51a. After I compiled that It shows version as "5.0.51alog".

Here is the problem I am facing. I run a procedure to update the table. Which updates a innodb table. But when It updates all other sql statements are locked and I can see hundreds of sql statements in the processlist.

Any Ideas why this happens to me?

Saravanan


__________________________________________________ __________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs