Avatar

Profile IN() statement in queries (General)

by Micha ⌂, Sunday, March 26, 2017, 09:54 (2559 days ago) @ danielb987

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


Complete thread:

 RSS Feed of thread