CODEWITHSUNDEEP
mysqlsql
Mr Br
Mr Br

Reputation: 3901

SQL - searching with one against many conditions

I am trying to find out how to get better performance when filtering Table. In my specific situation, I am filtering location table which have columns for Google geolocation (route, street_number, city, country and so on).

Now, all this values are in separate columns, and I can simply filter with multiple conditions, but I am interested how this multiple conditions effect search ?

Would it be better to search for full address with all params in 1 field or to search for each field separately ?

For example, is it better to filter with Q1:

SELECT * FROM locations WHERE route='some route' AND street_number='x' AND country='xy'

or Q2:

SELECT * FROM location WHERE full_route='some route x, xy'

Of course in this other situation I need to have standard for connecting those strings into one.

Generally speaking, is it better to search with more conditions or only with one ?

Upvotes: 0

Views: 52

Answers (1)

jgabriel
jgabriel

Reputation: 555

I believe that multiple columns is best. You can index less data ( like indexing country ) and SQL has better performance with searching the number as an INT instead of searching it as a part of an varchar
And BTW, if you make it a single varchar collumn you are also prone to have problems with whitespaces

EDIT:
With multiple collumns you can also turn common places as foreign keys ( in this case, the country collumn ). That can improve speed ( because it will be indexed ) and save disk space (as it will be only an INT instead of a varchar for each address row)

AND if you use some class to manage your DataBase connection ( such as Doctrine in PHP ), it can cache the country table on memory, to get the country as an int and then do the SQL search. That would improve performance a lot ;)

Upvotes: 2

Related Questions