optimized data base search algorithm

Question

I'm developing a website with api that can be accessed from mobile clients, the app is something similar to http://zomato.com/

lets say we have table car(carID, model, maker, transmission, fuleType, otherOptions)

the user can query any of the fields, the query also might include fields in other tables for example if i create w separate table for makers and models.

of course I can query using like clause on all fields, is that the right way ? when there might be thousands of records ?

I need fast db response real time querying when using either the mobile app or the website.

I'm familiar with .Net the most but i would be glad if you give me any other recommendations and guide me through the starting point(libraries, existing open source projects).

note: the user will only have a search field and optional filters like model, category etc...

Answer 1

Here is what i would do.

Build up your query using an IQueryable. You can use if statements to check if any of your parameters have a value and if so append the where statement to your query. Once your query is all set send it to memory with a .ToList or an .AsEnumerable.

public List<Data> Search (string param1, string param2)
{
    IQueryable<Data> query = db.SomeTable;

    if (!String.IsNullOrEmpty(param1))
        query = query.Where(m => m.Field1 == param1); // might use contain depending on your use case

    if (!String.IsNullOrEmpty(param2))
        query = query.Where(m => m.Field2 == param2);

    return query.ToList();
}

I would also use a Skip and Take for pagination.

Edit: I wanted to note this is about as fast as you will get with a database that I know of, if anyone else knows a faster function please let me know. You can achieve a quicker response by using a NoSQL database and static documents, but for several thousands of records a database should work just fine.