I was reading the mysql docs and noticed a section on searching on
multiple keys (stupid question - keys=columns?). It says doing an OR on
multiple keys is inefficient, and you should use a temp table. Here's
their example:
CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;
My question is, is there a point at which this temp table is less
efficient? What I have is a page where people can choose various things
that they might search on. I somewhat generically create the necessary
sql based on what criteria they've chosen. Unfortunately, one of the
queries includes searching for something that might show up in a number
of different columns. What's worse is that depending on what the user
might be searching on, I might need nearly all of the columns in the
later search. As a result, my tmp table would need to include all
columns that the original table has.
So here's effectively what I've done (note that db is a PEAR::DB):
$table = 'diverSearch';
$tableIsTemp = true;
$keysToSearch = array (
'userid', 'team_bottom1', 'team_bottom2', 'team_bottom3',
'team_support1', 'team_support2', 'team_additional'
);
$columnsToStore = "*";
$this->createTmpTable( $this->selectedView['divername'],
$columnsToStore,
$keysToSearch,
$this->reportsTable, $table );
And createTmpTable is:
function createTmpTable( $searchFor, &$columnsToStore, &$keysToSearch,
&$origTable, &$table ) {
$searchFor = $this->db->quote( $searchFor );
$columns = null;
if ( is_array( $columnsToStore ) ) {
$this->arrayToString( $columns, $columnsToStore );
} else {
$columns = $columnsToStore;
}
$sql = "CREATE TEMPORARY TABLE $table " .
"SELECT $columns FROM $origTable WHERE " .
"{$keysToSearch[0]} = {$searchFor} ";
// XXX why do I have to do these as separate queries. Shouldn't
// I be able to assemble one big query, where each line ends
// with semi-colon?
$result = $this->db->query( $sql );
$this->checkError( $result );
$count = count( $keysToSearch );
for( $i=1; $i<$count; $i++ ) {
$sql = "INSERT INTO $table " .
"SELECT $columns FROM $origTable WHERE " .
"{$keysToSearch[$i]} = {$searchFor} ";
$result = $this->db->query( $sql );
$this->checkError( $result );
}
}
And then my later searches are performed based on this temp table,
specifiying DISTINCT.
I'm curious if at this point, the creation of the temporary table with
all of this data is going to outweigh the inefficiencies of the "OR".
Also, as a minor side-note - every time I tried to do the createTmpTable
function by creating one big sql string, separating commands with a ';',
I got a syntax error. Is it not legal to do this? Is it
more/less/equal efficient to assemble one big query than to perform them
individually?
Thanks for any help.
-Dave
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
