PHP: Multiple SQL queries vs one monster

Question

I have some really funky code. As you can see from the code below I have a series of filters that I add to query. Now would it be easier to just have multiple queries, with it's own set of filters, then store the results in an array, or have this mess?

Does anyone have a better solution to this mess? I need to be able to filter by keyword and item number, and it needs to be able to filter using multiple values, not know which is which.

//Prepare filters and values
$values = array();
$filters = array();
foreach($item_list as $item){
 $filters[] = "ItemNmbr = ?";
 $filters[] = "ItemDesc LIKE ?";
 $filters[] = "NoteText LIKE ?";
 $values[] = $item;
 $values[] = '%' . $item . '%';
 $values[] = '%' . $item . '%';
}
//Prepare the query
$sql = sprintf(
 "SELECT ItemNmbr, ItemDesc, NoteText, Iden, BaseUOM FROM ItemMaster WHERE %s LIMIT 21",
 implode(" OR ", $filters)
);
//Set up the types
$types = str_repeat("s", count($filters));
array_unshift($values, $types);

//Execute it
$state = $mysqli->stmt_init();
$state->prepare($sql) or die ("Could not prepare statement:" . $mysqli->error);
call_user_func_array(array($state, "bind_param"), $values);
$state->bind_result($ItemNmbr, $ItemDesc, $NoteText, $Iden, $BaseUOM);
$state->execute() or die ("Could not execute statement");
$state->store_result();

Answer 1

You're answer depends on what exactly you need. The advantage of using only 1 query is that of resource use. One query takes only 1 connection and 1 communication with the sql server. And depending what what exactly you are attempting to do, it might take less SQL power to do it in 1 statement than multiple.

However, it might be more practical from a programmers point of view to use a few less complex sql statements that require less to create than 1 large one. Remember, ultimitaly you are programming this and you need to make it work. It might not really make a difference, script processing vs. sql processing. Only you can make ultimate call, which is more important? I would generally recommend SQL processing above script process when dealing with large databases.

Answer 2

I don't see anything particularly monstrous about your query. The only thing I would do different is separate the search terms.

Ie $item_list could be split in numeric items and text items.

then you could make the search something like:

...WHERE ItemNmbr IN ( number1, number2, number3) OR LIKE .... $text_items go here....

IN is a lot more efficient and if your $item_list doesn't contain any text part... then you are just searching a bunch of numbers which is really fast.

Now the next part if you are using a lot of LIKEs in your query maybe you should consider using MySQL Full-text Searching.

Answer 3

A single table query can be cached by the SQL engine. MySQL and its ilk do not cache joined tables. The general rule for performance is to use joins only when necessary. This encourages the DB engine to cache table indexes aggressively and also makes your code easier to adapt to (faster) object databases--like Amazon/Google cloud services force you to.