Profile IN() statement in queries (General)
Hi,
Good to hear.
It's very helpful.
I'm not a database expert so I don't know.
I change the index.inc.php near line 74. The original code-line reads:
$result=profile_mysqli_query($connid, "SELECT id, tid FROM ".$db_settings['forum_table']." WHERE pid = 0".$display_spam_query_and." AND category IN (".$category_ids_query.") ORDER BY sticky DESC, ".$db_thread_order." ".$descasc." LIMIT ".$ul.", ".$settings['threads_per_page']) or raise_error('database_error',mysqli_error($connid));
I add in front of this code the following code (including the line above):
$sqlQuery = "SELECT `id`, `tid` FROM `".$db_settings['forum_table']."` INNER JOIN (SELECT ".$category_ids[0]." AS `cat_id`"; for ($c=1; $c<count($category_ids); $c++) { $sqlQuery .= " UNION ALL SELECT ".$category_ids[$c]; } $sqlQuery .= ") AS `tmp_table` ON `category` = `cat_id` WHERE `pid` = 0 AND `spam` = 0 ORDER BY sticky DESC, time DESC LIMIT 0, 40"; $result=profile_mysqli_query($connid, $sqlQuery); // Original-Code to compare both ones $result=profile_mysqli_query($connid, "SELECT id, tid FROM ".$db_settings['forum_table']." WHERE pid = 0".$display_spam_query_and." AND category IN (".$category_ids_query.") ORDER BY sticky DESC, ".$db_thread_order." ".$descasc." LIMIT ".$ul.", ".$settings['threads_per_page']) or raise_error('database_error',mysqli_error($connid));
In your profiling script/tool, both queries are now listed, and the first one seems to be a litte bit faster.
regards
Micha
--
applied-geodesy.org - OpenSource Least-Squares Adjustment Software for Geodetic Sciences