How to search by varchar fields?

Question

MySQL

Good evening.

There is a table of about 1.5 million lines with addresses, consisting of a dozen varchar fields:

ID - md5-хеш от адреса
FullAddress - адрес строкой
CityType - тип населенного пункта
City - название
StreetType - тип улицы
Street - название
...

There was a need to make selections from this table by several fields, for example:

• exact address match - currently searched by ID = md5( search )
  
• all addresses within a city (where CityType=... and City=...)
  
• all addresses within a street (where CityType=... and City=... and StreetType=... and Street=...)
  
• at least something similar to the desired address (where City=... and Street=... and Building=... - names only, no types)
  
• all settlements that have this address - (where CityType=... and Street=... and Building=...)
  

Accordingly, the question arises - how to organize it effectively?

Is it enough to simply throw an index on each field? As far as I know, MySQL can't work well with multiple indexes in a single query - MySQL could sometimes make use of the multiple indexes.

Making a million indices for all possible combinations is clearly even less correct.

What other options might there be?

Answer 1

For such tasks, search engines are more suitable, for example, Sphinx or Elasticsearch.

Answer 2

If all records are unique, then why not make one unique index for all fields?