Optimize Database Search Query

Question

I have table that has following columns
-> col1, col2, col3

I am trying to search using these columns. So I am taking the 3 inputs from user.

Simple rules for search:

1) If any-of col is NOT entered by user then it should search using only other 2 columns.

select * from myTable where col1="abc" and col2="def"; // something like this. Any combination like col1-col2, col1-col3 or col2-col3

2) If all cols are entered then:

select * from myTable where col1="abc" and col2="def" and col3="ghi"; // something like this

3) If any-of one col is entered by user then:

select * from myTable where col1="abc"; // something like this. It can be col1, col2 or col3.

I know this can be DONE by using different select statements for database and using if-else in Java code.

I want a MOST OPTIMIZED solution (with little code/explanation) for this situation.

EDIT

NOTE: All 3 columns are NULL-able !! I am using Microsoft-SQL Server (MSSQL) but I want solution for both, MySQL and MSSQL

Answer 1

I will assume that the names of your columns are not exactly col1, col2, col3, and that the number of columns may expand in the future, so you want something that will not need to be completely reworked when that happens. You will therefore need to have an array that holds the column names. The user input should likewise be from a list of Strings with the same size as the array of column names.

I will also assume that you are using a prepared statement of some sort, but if not, just follow the basic outline.

Additionally, my assumption is that if all inputs are null we will return the entire table.

private final String[] COLUMNS = new String[]{"col1", "col2", "col3"};

public static PreparedStatement getStatement(String queryString){
   //you do this
}

public static PreparedStatement generateOptimizedStatement(List<String> input) {
  String whereOrAnd = " where ";
  StringBuilder sb = new StringBuilder("select * from myTable ");
  int i = 0;
  for(int i = 0; i < COLUMNS.length; i++){
    if(input.get(i) != null){
      sb.append(whereOrAnd).append(COLUMNS[i]).append(" = ? ");
      whereOrAnd = " and ";  
    }
  }
  PreparedStatement ps = getStatement(sb);
  for(int j = 0; j < COLUMNS.length; j++){
    String s = input.get(j);
    if(s != null){
      ps.setString(j+ 1, s); //prepared statement starts with index 1
    }
  }
  return ps;
}

Answer 2

Surely this is all you need?

Select * 
    from myTable 
where (col1 like @col1 +'%' or @col1 is null) 
  and (col2 like @col2 +'%' or @col2 is null) 
  and (col3 like @col3 +'%' or @col3 is null)

Answer 3

You can also use nested CASE in your query. Then query conditions would be simpler when some of variables are null.

First proposition:

SELECT *
FROM myTable 
WHERE
  CASE
    WHEN @col1 is NULL OR @col1 = '' THEN 
      CASE 
        WHEN @col2 is NULL OR @col2 = '' THEN
          CASE 
            WHEN @col3 is NULL OR @col3 = '' THEN 1=1
            ELSE @col3 = col3
          END
        ELSE
          CASE 
            WHEN @col3 is NULL OR @col3 = '' THEN @col2 = col2
            ELSE @col2 = col2 AND @col3 = col3
          END
      END
    ELSE
      CASE 
        WHEN @col2 is NULL OR @col2 = '' THEN
          CASE 
            WHEN @col3 is NULL OR @col3 = '' THEN @col1 = col1
            ELSE @col1 = col1 AND @col3 = col3
          END
        ELSE
          CASE 
            WHEN @col3 is NULL OR @col3 = '' THEN @col1 = col1 AND @col2 = col2
            ELSE @col1 = col1 AND @col2 = col2 AND @col3 = col3
          END
      END
  END;

And second proposition:

SELECT *
FROM myTable 
WHERE
  col1 = 
  CASE
    WHEN @col1 IS NULL OR @col1 = '' THEN col1
    ELSE @col1
  END
AND
  col2 = 
  CASE
    WHEN @col2 IS NULL OR @col2= '' THEN col2
    ELSE @col2
  END
AND
  col3 = 
  CASE
    WHEN @col3 IS NULL OR @col3= '' THEN col3
    ELSE @col3
  END;

You can see the result in SQLFiddle

EDIT:

So there are three different queries. One is proposed by Mureinik and two above proposed by me. To decide which one of them is optimal we have to be avare of how MySQL (and other DBMS's) optimizes query before execution. We can see details here.

The most important phrase for us is

Constant condition removal

It means that condition (1=1) from one of my queries will be removed. It also means than when :col1 and :col2 are both nulls and :col3 = 'aaa' then Mureinik's query:

WHERE  (NULL  IS NULL OR NULL  = '' OR NULL  = col1) AND
   (NULL  IS NULL OR NULL  = '' OR NULL  = col2) AND
   ('aaa' IS NULL OR 'aaa' = '' OR 'aaa' = col3)

will be simplified to:

WHERE 'aaa' = col3

If we analyze all 3 proposed queries this way we will see that for every set of variables col1, col2 and col3 all these queries will be optimized by DBMS to exactly the same query. So all three of them are equally performant. So you can choose whichever you want (Mureinik's one seems the clearest one)

Answer 4

This can be very easily done using a case in the where clause:

SET @col1='someterm1';
SET @col2='someterm2';
SET @col3=NULL;

SELECT  *
FROM table tbl1
WHERE 
    CASE WHEN @col1 IS NULL THEN 1=1 ELSE tbl1.col1=@col1 END
    AND CASE WHEN @col2 IS NULL THEN 1=1 ELSE tbl1.col2=@col2 END
    AND CASE WHEN @col3 IS NULL THEN 1=1 ELSE tbl1.col3=@col3 END;

The where clause will only search for a value if you pass a NON-NULL value. So the above statement will look like follows when the variables/paramaters are substituted:

SELECT  *
FROM table tbl1
WHERE 
    CASE WHEN @col1 IS NULL THEN 1=1 ELSE tbl1.col1='someterm1' END
    AND CASE WHEN @col2 IS NULL THEN 1=1 ELSE tbl1.col2='someterm2' END
    /* THIS LINE AND CASE WHEN @col3 IS NULL THEN 1=1 ELSE tbl1.col3=@col3 END; changes because of the NULL*/
   AND 1=1;

Thus you can pass any combination of fields that you hav and only those fields will be searched.For the fields you dont have send a NULL value and the CASE statement turns that into a 1=1 and the criteria is not applied.

The technique should work on any database engine.

Answer 5

One point you should note is that in a WHERE clause, adding an "OR" usually adds a lot of overhead to a query. An "AND" is usually much faster and requires less computing by the compiler. So I would try something that would not use that as best as possible.

Here is my idea of how to best optimize this:

1) put indexes on all 3 columns (col1, col2, col3). 2) Determining which columns to be used should, ideally, be computed in Java and based on this the query would be fired. Here is my idea (in PHP, but can be extended to Java... Sorry, not familiar enough!):

<?php

if (isset($_GET['options'])) {
$options = explode(",",$_GET['options']); // assuming you feed the columns separated with columns
}

if (isset($_GET['col1Value'])) {
$col1Value = $_GET['col1Value']; 
}

if (isset($_GET['col2Value'])) {
$col2Value = $_GET['col2Value']; 
}

if (isset($_GET['col3Value'])) {
$col3Value = $_GET['col3Value']; 
}

if (in_array("col1",$options)) { // check to see if 'col1' exists in array
$clause = ' and coalesce(col1,'') = $col1Value';
}

if (in_array("col2",$options)) { // check to see if 'col2' exists in array
$clause = $clause.' and coalesce(col2,'') = $col2Value';
}

if (in_array("col3",$options)) { // check to see if 'col3' exists in array
$clause = $clause.' and coalesce(col3,'') = $col3Value';
}


$sql = "

select *
from table
where 1=1
     $clause
";

pg_execute($databaseConnection,$sql);

?>

This is probably not the best example as it is in PHP, but hopefully that gives you some ideas....

Cheers!

Answer 6

You can do that as below using PHP, do the same way with Java:

$mapColVal = array( 1 => $first_post_value, 2 => $second_post_value, 3 => $third_post_value);
$whereCond = '';
for($i = 1; $i <= 3; $i++){
   $whereCond .= "col".$i. "=". $mapColValue[$i]." AND ";
}
$whereCond = subStr($whereCond,0,-5);

Then do as bellow:

SELECT * FROM my_table WHERE $whereCond;

Answer 7

Assuming you're binding variables named :col1, :col2 and :col3, respectively, this can be done in a single statement by using a couple of or conditions. The idea here is to have the database perform a short circuit logic for eahc column - if the user passes null that part of the condition is just evaluated to true, without accessing the table. If a real value if passed, it's compared to the column in the table.

SELECT *
FROM   myTable 
WHERE  (:col1 IS NULL OR :col1 = '' OR :col1 = col1) AND
       (:col2 IS NULL OR :col2 = '' OR :col2 = col2) AND
       (:col3 IS NULL OR :col3 = '' OR :col3 = col3)