Re: search through one/several tables
mel list_php [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
Re: search through one/several tables
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: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] 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/[EMAIL PROTECTED] _ 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/[EMAIL PROTECTED]
Re: search through one/several tables
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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] 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/[EMAIL PROTECTED] _ 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/[EMAIL PROTECTED]
Re: search through one/several tables
mel list_php [EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] 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/[EMAIL PROTECTED] _ 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/[EMAIL PROTECTED] 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
Re: search through one/several tables
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 Smithbasically 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: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] 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/[EMAIL PROTECTED] _ 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/[EMAIL PROTECTED] 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
Re: search through one/several tables
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 [EMAIL PROTECTED] 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 Smithbasically 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: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] 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/[EMAIL PROTECTED] _ 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/[EMAIL PROTECTED] 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
RE: search through one/several tables
The best I've been able to come up with involves some shell scripting. If you're running Linux, using unpacked MyISAM tables, and have some scripting ability from whatever language you're writing your application in, you can run this in your MySQL directory: strings -f *.MYD | grep search string | cut -f1 -d'.' | sort -u It will give you back the table names that have that string somewhere inside them. Then in your scripting language you can check the columns on just the tables that the shell script returned. Note that it is NOT fast at all. My 1.7GB of database takes about 10 minutes to crunch through the data. If you're going to do something like this make sure you understand the security ramifications of running shell scripts with user input and how to secure it in your language of choice. -- Pat Adams Applications Programmer SYSCO Food Services of Dallas, L.P. (469) 384-6009 -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 23, 2005 10:09 AM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: search through one/several tables 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: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: search through one/several tables
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 - [EMAIL PROTECTED] 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" [EMAIL PROTECTED] 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 Smithbasically 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: [EMAIL PROTECTED] To: "mel list_php" [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 "mel list_php" [EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + "mel list_php" [EMAIL PROTECTED] 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
Re: search through one/several tables
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 [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com 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 - [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 Smithbasically 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: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] 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/[EMAIL PROTECTED] _ 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
Re: search through one/several tables
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com 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 - [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 Smithbasically 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: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] 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
Re: search through one/several tables
Right, my mistake, you would have to use HAVING. PB mel list_php wrote: 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 [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com 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 - [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 Smithbasically 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: [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 09:55:23 -0500 mel list_php [EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: search through one/several tables Date: Wed, 23 Mar 2005 10:44:53 + mel list_php [EMAIL PROTECTED] 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/[EMAIL PROTECTED] _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List
