data structure algorithms for database searching

Question

I was used to the traditional way of doing database searching with the following

1. using wildcards for term searches
2. using where clause for specific data like addresses and names

but at other times, I found these common methods to produce code that is so bloated, especially when it comes to complex searches.

Are there algorithms out there that you use for complex database searching? I tried to look for some but had a hard time doing so. I stumbled accross the binary search but I can't find a use for it :(

EDIT: Here's a pseudocode of a search I was working on. It uses jquery range sliders for maximum and minimum searching

query = 'select * from table'

if set minprice and not set maxprice
 if minprice = 'nomin'
  query += ' where price < maxprice'
 else
  query += ' where price < maxprice and price < minprice'
if not set minprice and set maxprice
 if maxprice = 'nomax'
  query += ' where price > minprice'
 else
  query += ' where price > minprice and price < maxprice'

if set maxprice and set minprice
 if maxprice = 'nomax'
  query += ' where price > minprice'
 else
  query += ' where price > minprice and price < maxprice'

this may not be the codebase by which you base your answers. I'm looking for more elegant ways of doing database searching.

EDIT by elegant I mean ways of rewriting the code to to achieve faster queries at less lines of code

Answer 1

Remeber speed of a query is not just the query itself. Also, greatly depends on how the db is structured. Is this a std relational layout, or a star, or? Are your keys indexed, and do you have secondary indexes? Are you expecting to bring back a lot of data, or just a couple of rows? Are you searching on columns where the db has to do a text search, or on numeric values. And of course, on top of that, how is the db physically layed out? index's and heavy hit tables on seperate drives? and so forth. Like the previous people mentioned, maybe a specific example would be more helpful in trying to solve

Answer 2

Alright, I'm still not very clear on what you want, but I'll give it a shot...

If you're trying to speed up the query, you don't need to worry about "improved algorithms". Just make sure that any columns that you're searching on (price in your example) have an index on them, and the database will take care of searching efficiently. It's very good at it, I promise.

As for reducing the amount of code, again, I can't speak for every case, but your above pseudocode is bloated because you're handling the exact same case multiple times. My code for something like that would be more like this (pseudocode, no particular language):

if (set(minprice) and minprice != 'nomin')
    conditions[] = 'price > minprice'

if (set(maxprice) and maxprice != 'nomax')
    conditions[] = 'price < maxprice'

query = 'select * from table'
if (notempty(conditions))
    query += ' where '+conditions.join(' and ')

Answer 3

When interfacing with a database, you're far better off with a complex and ugly query than with an 'elegant' query which has you duplicating database search functionality inside your application. Each call to the database has a cost associated with it. If you write code to search a database within your application, it's virtually guaranteed to be more expensive.

Unless you are actually writing a database (tall order), let the database do the searching.

Answer 4

try to focus on reorganizing your query building process.

query = select + ' where ' + filter1 + filter2

select = 'select * from table'

filter1 = '';
if set minprice 
   if minprice = 'nomin'
      filter1 = price > minprice'
   else
      filter1 = 'price < minprice'

and so on ... 'til the building the full query :

query = select;

if any filter on
   query += ' where '
   first = true

   if set filter 1
      if not first
         query += ' and '
      query += filter1

and so on...

you can put your filters in an array. it is more 'scalable' for your code.

Answer 5

The major problem with your code is that it unnecessarily mulls over every possible combination of set(minprice) and set(maxprice), while they can be treated independently:

query = 'select * from table'
conditions = [] #array of strings representing conditions 
if set(minprice):
    conditions.append("price < minprice")
if set(maxprice):
    conditions.append("price > maxprice")
if len(conditions)>0:
    query += ' WHERE ' + " and ".join(conditions)

In general it is beneficial to separate generation of conditions (the if set(...) lines above) from building the actual query. This way you don't need a separate if to generate (or skip) an "AND" or "WHERE" before each generated condition but instead you can just process it in one place (the last two lines above) adding the infixes as necessary.