mysql search question ??

ChicoMendez

Hi

i have a big table with a load of columns and what i want to do is search the table without having to specify all the columns - is this possible ?

instead of

select * from table where col1 like 'word' or col2 like 'word' or col3 like 'word' or col4 like 'word' or col5 like 'word' or col6 like 'word' .....

i wanna do something like


select * from table where cols like 'word'


is this possible ??

tnx

Webmonkey

I don't think there is such method but I could be wrong.

It smells of bad design though if you have to do that.

amen

afaik no.

btw don't use select *
1: you normally get better database performance specifing the columns you are interested in
2: if someone adds a column in the middle of table then your result set will change and you will get unexpected results. Select column name will prevent this

seamus

Possible option here, though I'm not sure how well it will perform.

Use a view which consists of the primary key from your table and a single other column which is the concatenation of the other columns.

So

CREATE VIEW 'merged_data' AS 
	SELECT 
		pri_key,
		CONCAT(col1, col2, col3, col4, col5, ....) as 'merged_texts'
	FROM
		main_table;

So then if you want to get the columns which match your word;

SELECT * from merged_data WHERE merged_texts like 'word';

To pull directly from the main table:

SELECT * FROM main_table WHERE pri_key IN (SELECT pri_key from merged_data WHERE merged_texts LIKE 'word');

Performance on this could be atrocious. But on the other hand, if the columns are char or varchar and they're indexed, there shouldn't be much of a difference. I don't know if the indexes will work if these are text columns with full-text indexes on them.

If this is something for you to query every so often directly, then it could be a timesaver. But on the other hand if you're simply trying to clean up a query in some code, I would stick with the "WHERE col1 LIKE 'word' OR col2 LIKE 'word'"...etc.

Liam Byrne

amen wrote: »

afaik no.

btw don't use select *
1: you normally get better database performance specifing the columns you are interested in
2: if someone adds a column in the middle of table then your result set will change and you will get unexpected results. Select column name will prevent this

Not if you retrieve your columns using mysql_fetch_assoc and reference the fields using the name index.

amen

if someone adds a column in the middle of table then your result set will change and you will get unexpected results. Select column name will prevent this

not a mysql person but something new learnt today.

"WHERE col1 LIKE 'word' OR col2 LIKE 'word'"

you may get better performance writing multiple select statements for each or clause and unioning the results.

damoth

If your table is MyISAM then you could use FULLTEXT search which can search across multiple columns in one go.

See the 2nd answer on this page (not the accepted one) for a basic example: http://stackoverflow.com/questions/2514548/how-to-search-multiple-columns-in-mysql

There are a lot of other options available for FULLTEXT search too such as giving some words higher priority than others and you can also order the results by relevance.