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:

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from 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'],
$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.


PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to