List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 23 2005 2:55pm
Subject:Re: search through one/several tables
View as plain text  
"mel list_php" <list_php@stripped> wrote on 03/23/2005 06:00:08 AM:

> 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
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

I agree in priciple with Alec. Good database design can and should promote 
good data retreival. However, it sounds from your original question that 
you need to find all records on any table with some bit of information in 
any column.  That problem is just too generic to provide specific help 
for. 
If you can't find certain pieces of data, it _may_ be that you haven't 
organized your data to the point that there is a place for everything and 
everything is in its place. There is always (except for the most trivial 
cases) more than one way to organize any set of data, however different 
schemas offer different advantages (size, speed, ease of use, etc). Which 
one will work best for you depends on the nature of your data, your 
database server's limitations (hardware, software, and operating system 
limits), and what you need to get from the data once it's organized.  Some 
schemas make it harder to add or update data but make finding it a breeze; 
others are just the opposite. This sounds like a good time to honestly 
review your current schema to make sure it's going to meet your usage 
needs not just your data storage requirements.  Ask the people who need to 
use the data you are storing what they need to find and adjust your 
schemas to fit. Sometimes it means asking them to look by using a 
different method as a compromise (you give a little, they give a little). 
Eventually you can work it out.

If you have a schema design issue or if you have a more specific set of 
tables (please post their SHOW CREATE TABLE... results) and a sample query 
that you cannot solve (even if it's only a description of what you need to 
find), I am sure the list will be happy to help. Many of us really enjoy 
those kinds of puzzles.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


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