java - SQL Scaling: should I try to minimize queries when having multiple OR column conditions? -

my db schema is

integer id time_stamp last_updated   // indexed varchar url               // indexed  // place attributes varchar name              // indexed varchar address           // indexed varchar phone             // indexed  integer score 

out of name, address, , phone fields, 1 of them must non-empty, , other 2 empty.

valid examples:

{name="pizza hut", address=null, phone=null, score=40}  

invalid examples:

{name="pizza hut", address="some address", phone=null, score=40}  

i'm trying write function.

// return map/dictionary of string keys , integer scores of latest data in db matches given url, , not older last_updated. // example: {name=40, address=50, phone=66} public map getplaceanalysis(string name_, string address_, string phone_, string url, datetime last_updated_); 

scaling assumptions:

• this function getting called frequently.
• db has millions of rows
• name, phone, address may not fields. may have 20+ attributes place.

there 2 approaches:

1. 3 (or number of attributes place stored in db) queries:

   select score db_name name=name_ , url = url_ , last_updated > last_updated_ select score db_name phone=phone_ , url = url_ , last_updated > last_updated_ select score db_name address=address_ , url = url_ , last_updated > last_updated_ 

   after each query, can select latest row , write score map/dictionary returned appropriate key.

2. 1 query:

   select name, phone, address, score db_name (name=name_ or phone=phone_ or address=address_ ) , url = url_ , last_updated > last_updated_ 

   i loop through query results , figure out updated row name, phone, , address, , return appropriate map/dictionary.

does matter approach take? i'm guessing second 1 better, have little experience databases. sorry if explanation unclear. i'd imagine pretty common problem couldn't figure out how search problem.

when running query remote database there considerable amount of time spent in establishing connection , stuff that, more related communication database , not query itself. point of view want minimize number of queries issue database.

as explained in mysql reference manual multiple-column indexes, seems benefit create composite index on 3 fields using in query. index used when search name only, name , address , 3 fields (see says leftmost prefixes) pay attention order going declare index.

the idea of hash of 3 fields seems interesting, if can used in case.

finally should read understanding query execution plan and/or have database administrator that.

the key thing understand when talking query performance design repeatable test case (i.e. same set of data, repeatable database load etc) , try different approaches. , watch out differences between environment test , production environment.

i hope helps.