List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:March 23 2005 11:15am
Subject:Re: search through one/several tables
View as plain text  
I am not quite sure I understand your question, then: you would have to 
make your table structure a little clearer. Generally, however, puristic 
database design would say that you do not have the same "class" of data in 
different tables. Instead, you have one "master" table with all the 
"similar" data in it, tagged by a unique ID, and all the other tables 
refer to the master copy by that ID. You then "reconstruct" the original 
table at query time using a JOIN. Then, of course, it is trivial to search 
the master table.

I will say that I think fulltext will not help you, if I understand your 
problem. Fulltext divides a column into separate words, based on the 
spaces (and non alphanumerics) in the string. Since, as I understand it, 
your DNA sequences have no natural breaks and the "words", such as they 
are, can start at any base, fulltext will not help you.

"mel list_php" <list_php@stripped> wrote on 23/03/2005 11:00:08:

> Unfortunatly they are not, I have something like 30 tables, with I would 
say 
> 10 to 15 fields per table.
> The number of row per table is quite low, i think it won't exceed 
> 500-1000/table. But I may sometimes have to search into dna sequences 
> (around 5000 atcg characters in any order), so that is quite heavy. 
Maybe 
> for that field a fulltext index would be helpful?
> 
> Thanks for your help,
> Melanie
> 
> 
> >From: Alec.Cawley@stripped
> >To: list_php@stripped
> >CC: mysql@stripped
> >Subject: Re: search through one/several tables
> >Date: Wed, 23 Mar 2005 10:44:53 +0000
> >
> >"mel list_php" <list_php@stripped> wrote on 23/03/2005 10:14:07:
> >
> > > Hi list,
> > >
> > > I would like to search for something into one or several tables.
> > > My first idea was to retrieve the tables' names, then for each of 
them
> > > retrieve the columns' name and have a look in each of this column.
> > > Is there a more elegant (fast) way to do that with mysql?
> > >
> > > Somebody has some tips/doc where I could look for search engines?My
> >problem
> > > is that I don't have one big table with all the data but several 
little
> >ones
> > > with few fields, so I don't think solutions like Lucene could work.
> >
> >If your tables are all identical, which it sounds like, you want to 
create
> >a Merge Table: see
> >http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html
> >
> >         Alec
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: 
> >http://lists.mysql.com/mysql?unsub=1
> >
> 
> _________________________________________________________________
> Express yourself with cool new emoticons 
http://www.msn.co.uk/specials/myemo
> 

Thread
search through one/several tablesmel list_php23 Mar
  • Re: search through one/several tablesAlec.Cawley23 Mar
    • Re: search through one/several tablesmel list_php23 Mar
      • Re: search through one/several tablesAlec.Cawley23 Mar
      • Re: search through one/several tablesSGreen23 Mar
        • Re: search through one/several tablesmel list_php23 Mar
          • Re: search through one/several tablesSGreen23 Mar
            • Re: search through one/several tablesPeter Brawley23 Mar
              • Re: search through one/several tablesmel list_php23 Mar
                • Re: search through one/several tablesSGreen23 Mar
                • Re: search through one/several tablesPeter Brawley23 Mar
RE: search through one/several tablesPat 006 Adams23 Mar