MySQL - meilleure façon de rechercher dans plusieurs colonnes - Stack Overflow

I was wondering what is the best way to search on multiple columns at the same time, this is on big databases.

For example I have a table with 400k records and I want to perform an intersection with another table with 1000k records.

Currently i am doing something like this:

alter table t1 add column (hash varbinary(32));
update t1 set hash = md5(concat(col1, col2, col3));
alter table t1 add index (hash);

and then i do my queries, joins, whatever sing the hashed columns...

select * from t1 where t1.hash not in (select t2.hash from t2); 

Does any1 have similar experiences, or uses other tricks or something else which might be interesting to share?

Have you looked at the Apache Solr project? I have not used it with MySQL but have worked on a project in the past that used it with Microsoft SQL Server. It is very useful for complex queries.

IF col1, col2, col3 are of same data type in both tables and there is an index (better clustered), then this solution will not bring much performance improvement. It wioll be cheaper to to store an int column, with all combinations of your columns numbered.

I think there is more space for improvement in your tables structure and queries you're using to get "intersection".

I was wondering what is the best way to search on multiple columns at the same time, this is on big databases.

For example I have a table with 400k records and I want to perform an intersection with another table with 1000k records.

Currently i am doing something like this:

alter table t1 add column (hash varbinary(32));
update t1 set hash = md5(concat(col1, col2, col3));
alter table t1 add index (hash);

and then i do my queries, joins, whatever sing the hashed columns...

select * from t1 where t1.hash not in (select t2.hash from t2); 

Does any1 have similar experiences, or uses other tricks or something else which might be interesting to share?

Have you looked at the Apache Solr project? I have not used it with MySQL but have worked on a project in the past that used it with Microsoft SQL Server. It is very useful for complex queries.

IF col1, col2, col3 are of same data type in both tables and there is an index (better clustered), then this solution will not bring much performance improvement. It wioll be cheaper to to store an int column, with all combinations of your columns numbered.

I think there is more space for improvement in your tables structure and queries you're using to get "intersection".

Source