It's more understandable if the table names are in front of the column
names :
SELECT relationship.topic_id1, relationship.topic_id2, topic.topic_name,
topic.categ_id, topic.list_name, topic.title,
topic.url, topic.page_type, relationship.rel_type, entry_type.inverse_id,
relationship.description AS rel_descrip,
relationship.created, relationship.updated
FROM relationship, topic, entry_type
WHERE
((relationship.topic_id1 = topic.topic_id AND relationship.topic_id2 =
1252)
OR (relationship.topic_id2 = topic.topic_id and relationship.topic_id1 =
1252))
AND relationship.rel_type = entry_type.type_id
AND entry_type.class_id = 2
ORDER BY rel_type, list_name;
I see a few problems in your schema.
- topic_id1 and topic_id2 play the same role, there is no constraint to
determine which is which, hence it is possible to define the same relation
twice.
- as you search on two columns with OR, you need UNION to use indexes.
- lack of indexes
- I don't understand why the planner doesn't pick up hash joins...
- if you use a version before 8, type mismatch will prevent use of the
indexes.
I'd suggest rewriting the query like this :
SELECT topic.*, foo.* FROM
topic,
(SELECT topic_id2 as fetch_id, topic_id1, topic_id2, rel_type, description
as rel_descrip, created, updated
FROM relationship
WHERE
rel_type IN (SELECT type_id FROM entry_type WHERE class_id = 2)
AND topic_id1 = 1252
UNION
SELECT topic_id1 as fetch_id, topic_id1, topic_id2, rel_type, description
as rel_descrip, created, updated
FROM relationship
WHERE
rel_type IN (SELECT type_id FROM entry_type WHERE class_id = 2)
AND topic_id2 = 1252)
AS foo
WHERE topic.topic_id = foo.fetch_id
CREATE INDEX'es ON
entry_type( class_id )
relationship( topic_id1, rel_type, topic_id2 ) which becomes your new
PRIMARY KEY
relationship( topic_id2, rel_type, topic_id1 )
Of course, this doesn't explain how MySQL manages to execute the query
in about 9 msec. The only minor differences in the schema are:
entry_type.title and rel_title are char(32) in MySQL,
entry_type.class_id is a tinyint, and topic.categ_id, page_type and
dark_ind are also tinyints. MySQL also doesn't have the REFERENCES.
Can you post the result from MySQL EXPLAIN ?
You might be interested in the following code. Just replace mysql_ by pg_,
it's quite useful.
$global_queries_log = array();
function _getmicrotime() { list($u,$s) = explode(' ',microtime()); return
$u+$s; }
/* Formats query, with given arguments, escaping all strings as needed.
db_quote_query( 'UPDATE junk SET a=%s WHERE b=%s', array( 1,"po'po" ) )
=> UPDATE junk SET a='1 WHERE b='po\'po'
*/
function db_quote_query( $sql, $params=false )
{
// if no params, send query raw
if( !$params )
return $sql;
// quote params
foreach( $params as $key => $val )
{
if( is_array( $val ))
$val = implode( ',', $val );
$params[$key] = "'".mysql_real_escape_string( $val )."'";
}
return vsprintf( $sql, $params );
}
/* Formats query, with given arguments, escaping all strings as needed.
Runs query, logging its execution time.
Returns the query, or dies with error.
*/
function db_query( $sql, $params=false )
{
// it's already a query
if( is_resource( $sql ))
return $sql;
$sql = db_quote_query( $sql, $params );
$t = _getmicrotime();
$r = mysql_query( $sql );
if( !$r )
{
echo "<div class=bigerror><b>Erreur MySQL
:</b><br>".mysql_error()."<br><br><b>Requte</b>
:<br>".$sql."<br><br><b>Traceback </b>:<pre>";
foreach( debug_backtrace() as $t ) xdump( $t );
echo "</pre></div>";
die();
}
global $global_queries_log;
$global_queries_log[] = array( _getmicrotime()-$t, $sql );
return $r;
}
At the end of your page, display the contents of $global_queries_log.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
