List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 23 2005 5:32pm
Subject:Re: search through one/several tables
View as plain text  
But you can use column aliases in a HAVING clause (a simple mistake when 
you are typing as fast as you are thinking). The other option is to put 
the CONCAT(...) into the WHERE clause in place of the alias. Either way, 
you will absolutely NOT be using an index to search that table. You will 
need to scan the results of each CONCAT() for each row (at least as long 
as a full table scan would take).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"mel list_php" <list_php@stripped> wrote on 03/23/2005 12:24:42 PM:

> Unfortunatly I can't use the alias txt in the where clause:
> Standard SQL doesn't allow you to refer to a column alias in a WHERE 
clause. 
> This is because when the WHERE code is executed, the column value may 
not 
> yet be determined. For example, the following query is illegal:
> 
> SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;
> 
> http://dev.mysql.com/doc/mysql/en/problems-with-alias.html
> 
> 
> 
> >From: Peter Brawley <peter.brawley@stripped>
> >Reply-To: peter.brawley@stripped
> >To: SGreen@stripped
> >CC: mel list_php <list_php@stripped>,  mysql@stripped
> >Subject: Re: search through one/several tables
> >Date: Wed, 23 Mar 2005 10:46:10 -0600
> >
> >Shawn, Mel
> >
> > >I am not aware of any SQL dialect that supports a query of the kind 
you
> > >are asking about. If there were such a query it _might_ look 
something
> > >like this
> >
> > >SELECT <column list>
> > >FROM <table reference>
> > >WHERE ANY_COLUMN(<column name list>) LIKE <search parameter>
> >
> >Right, but Mel can emulate your ANY_COLUMN with something like
> >
> >  SELECT CONCAT( col1, ..., colN ) AS txt
> >  FROM tbl
> >  WHERE txt LIKE '%foobar%';
> >
> >PB
> >
> >-----
> >
> >SGreen@stripped wrote:
> >
> >>I am not aware of any SQL dialect that supports a query of the kind 
you 
> >>are asking about. If there were such a query it _might_ look something 

> >>like this
> >>
> >>SELECT <column list>
> >>FROM <table reference>
> >>WHERE ANY_COLUMN(<column name list>) LIKE <search parameter>
> >>
> >>but like I said, I can't think of any SQL server that has a function 
like 
> >>ANY_COLUMN() or its equivalent. Sorry! I think you will need to do a 
> >>column-by-column comparison (especially if you want to use LIKE or 
RLIKE).
> >>
> >>Shawn Green
> >>Database Administrator
> >>Unimin Corporation - Spruce Pine
> >>
> >>"mel list_php" <list_php@stripped> wrote on 03/23/2005 11:08:56 
AM:
> >>
> >>
> >>
> >>>I can find the data, I was just wondering if mysql provides a kind of 

> >>>generic scan of a whole table.
> >>>
> >>>I could provide an advanced search, and ask the user what kind of 
info
> >>>
> >>he
> >>
> >>>wants to retrieve but I first would like a quickSearch that may 
retrieve
> >>>
> >>too
> >>
> >>>much info but is more intuitive.
> >>>
> >>>As I said, my first idea was to create a script to go through all my
> >>>
> >>tables
> >>
> >>>and scan the relevant columns (I don't want to scan the id keys for 
> >>>example), but I was just wondering if given a table it is possible to
> >>>
> >>use
> >>
> >>>mysql to scan all its columns:
> >>>*************************** 1. row ***************************
> >>>       Table: dbLab
> >>>Create Table: CREATE TABLE `dbLab` (
> >>>  `labId` tinyint(5) NOT NULL auto_increment,
> >>>  `name` varchar(50) NOT NULL default '',
> >>>  `fullName` varchar(250) default NULL,
> >>>  `adress` varchar(150) NOT NULL default '',
> >>>  `city` varchar(50) NOT NULL default '',
> >>>  `country` varchar(50) NOT NULL default '',
> >>>  `url` varchar(100) NOT NULL default '',
> >>>  PRIMARY KEY  (`labId`)
> >>>) TYPE=MyISAM COMMENT='list of partners'
> >>>1 row in set (0.00 sec)
> >>>
> >>>If I search for "Smith" I want to retrieve the name Smith, the 
fullname 
> >>>Smith, the address or city containing Smith....basically look for the
> >>>
> >>word
> >>
> >>>in all the columns, a shortener for select * from dbLab where name 
> >>>like'%Smith%' or fullName like '%Smith%' or adress like 
'%Smith%'.....
> >>>
> >>>But maybe this is not a good way to do things?
> >>>
> >>>Melanie
> >>>
> >>>
> >>>
> >>>
> >>>>From: SGreen@stripped
> >>>>To: "mel list_php" <list_php@stripped>
> >>>>CC: Alec.Cawley@stripped,mysql@stripped
> >>>>Subject: Re: search through one/several tables
> >>>>Date: Wed, 23 Mar 2005 09:55:23 -0500
> >>>>
> >>>>"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
> >>>>
> >>>>
> >>>>
> >>>_________________________________________________________________
> >>>Stay in touch with absent friends - get MSN Messenger 
> >>>http://www.msn.co.uk/messenger
> >>>
> >>>
> >>>
> >>
> >>
> >>
> 
>>------------------------------------------------------------------------
> >>
> >>No virus found in this incoming message.
> >>Checked by AVG Anti-Virus.
> >>Version: 7.0.308 / Virus Database: 266.8.1 - Release Date: 3/23/2005
> >>
> >>
> >No virus found in this outgoing message.
> >Checked by AVG Anti-Virus.
> >Version: 7.0.308 / Virus Database: 266.8.1 - Release Date: 3/23/2005
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: 
> >http://lists.mysql.com/mysql?unsub=1
> 
> _________________________________________________________________
> Want to block unwanted pop-ups? Download the free MSN Toolbar now! 
> http://toolbar.msn.co.uk/
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

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