Support ⇒ Dragonfly CMS v10 ⇒ Search not returning all results ⇒ Community Forums ⇒ CPG Dragonfly™ CMS
Forum IndexDragonfly CMS v10

Search not returning all results Reply to topic

Go to page Previous 1, 2

Also not sure I'm going to be able to run this within the CMS.. I think it's just going to take too long and will timeout.
I really wish PHP had an easy way to run an asynchronous query.

Test Signature

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):


Sorry this:

$result = $db->query("SELECT post_id, post_subject, post_text FROM {$db->TBL->bbposts_text}"); while ($row = $result->fetch_assoc()) { \Dragonfly\Forums\Search::addWords($row['post_id'], $row['post_text'], $row['post_subject']); }

Test Signature

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):


I know you use a progress indicator during the install progress.
How can I do that within the CMS?

Is there a way to use Server Side Events within the CMS?

Test Signature

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):


In case anyone else has this issue, here is what I did.

- Create a new file admin/modules/forums/admin_searchkeywords.php with the following contents:

<?php /*************************************************************************** * admin_searchkeywords.php * ------------------- * begin : Thursday, Jul 12, 2001 * copyright : (C) 2001 The phpBB Group * email : support @ phpbb.com * ***************************************************************************/ /*************************************************************************** * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation; either version 2 of the License, or * (at your option) any later version. * ***************************************************************************/ /* Modifications made by CPG Dev Team http://cpgnuke.com */ /************************************************************************/ if (!defined('ADMIN_PAGES')) { exit; } if (isset($_GET['mode']) || isset($_POST['mode'])) { $mode = $_POST->txt('mode') ?: $_GET->txt('mode'); } else { $mode = isset($_POST['reset']) ? 'reset' : ''; } if ($mode) { if ('reset' == $mode) { $old_postcount = $_POST['old_post_count']; $old_search_words = $_POST['old_search_words']; $old_search_wordmatch = $_POST['old_search_wordmatch']; $template->set_handle('body', 'admin/forums/keywords_edit'); // Clear the search word tables $db->query("DELETE FROM {$db->TBL->bbsearch_wordlist}"); $db->query("DELETE FROM {$db->TBL->bbsearch_wordmatch}"); //Go through each post and re-generate the search tables $result = $db->query("SELECT post_id, post_subject, post_text FROM {$db->TBL->bbposts_text}"); while ($row = $result->fetch_assoc()) { \Dragonfly\Forums\Search::addWords($row['post_id'], $row['post_text'], $row['post_subject']); } $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordlist}"); $template->search_words = $result->num_rows; $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordmatch}"); $template->search_wordmatch = $result->num_rows; $result = $db->query("SELECT post_id FROM {$db->TBL->bbposts}"); $template->post_count = $result->num_rows; $template->mode = $mode; } } else { $template->set_handle('body', 'admin/forums/keywords_list'); $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordlist}"); $template->search_words = $result->num_rows; $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordmatch}"); $template->search_wordmatch = $result->num_rows; $result = $db->query("SELECT post_id FROM {$db->TBL->bbposts}"); $template->post_count = $result->num_rows; $template->mode = $mode; }

- Create new file themes/admin/html/admin/forums/keywords_list.xml
<div class="border"> <nav tal:replace="structure php:here.toString('admin/forums/menu')"></nav> <h1 i18n:translate="Keywords_title">Search Keywords</h1> <p i18n:translate="Keywords_explain">From this control panel you can reset the Search Keywords in the database. Use this if you don't think that the search results are accurate - ie, you're sure some search results are missing.</p> <p i18n:translate="Keywords_warning"><strong>Warning:</strong> This query may be very intensive, depending on the number of forum posts.</p> <form method="post" action=""> <input type="hidden" name="old_post_count" tal:attributes="value post_count"></input> <input type="hidden" name="old_search_words" tal:attributes="value search_words"></input> <input type="hidden" name="old_search_wordmatch" tal:attributes="value search_wordmatch"></input> <input type="hidden" name="mode" value="reset"></input> <table><thead> <tr> <th i18n:translate="">Total Forum Posts</th> <th i18n:translate="">Search Keywords</th> <th i18n:translate="">Keyword Matches in Posts</th> </tr> </thead><tbody> <tr> <td tal:content="post_count">{postcount}</td> <td tal:content="search_words">{search_word}</td> <td tal:content="search_wordmatch">{search_wordmatch}</td> </tr> </tbody><tfoot> <tr> <td colspan="4"> <button name="form-reset" i18n:translate="reset_search_keywords">reset_search_keywords</button> </td> </tr> </tfoot></table> </form> </div>

- Create new file themes/admin/html/admin/forums/keywords_edit.xml
<div class="border"> <nav tal:replace="structure php:here.toString('admin/forums/menu')"></nav> <h1 i18n:translate="Keywords_title">Search Keywords</h1> <p i18n:translate="Keywords_explain">From this control panel you can reset the Search Keywords in the database. Use this if you don't think that the search results are accurate - ie, you're sure some search results are missing.</p> <p i18n:translate="Keywords_results"><br />Search Database Rebuild Results</p> <table><thead> <tr> <th i18n:translate="">Old Total Forum Posts</th> <th i18n:translate="">Old Search Keywords</th> <th i18n:translate="">Old Keyword Matches in Posts</th> </tr> </thead><tbody> <tr> <td tal:content="old_post_count">{old_postcount}</td> <td tal:content="old_search_words">{old_search_word}</td> <td tal:content="old_search_wordmatch">{old_search_wordmatch}</td> </tr> </tbody></table> <span id="percentage" style="text-align:right; display:block; margin-top:5px;">0</span> </div>

- In modules/Forums/l10n/en.php add
'Keywords_title' => 'Search Keywords', 'Keywords_explain' => 'From this control panel you can reset the Search Keywords in the database. Use this if you don\'t think that the search results are accurate - ie, you\'re sure some search results are missing.', 'Keywords_warning' => ' This query may be very intensive, depending on the number of forum posts. If you run this from behind a proxy server, it may time out.', 'Keywords_results' => ' Search Database Rebuild Results',

- In modules/Forums/admin/adlinks.inc after
$menuitems['_AMENU4'][$module]['SUB'][$lang['Word_Censor']] = URL::admin($module.'&do=words');

Add
$menuitems['_AMENU4'][$module]['SUB'][$lang['Search_Keywords']] = URL::admin($module.'&do=searchkeywords');

I'm not sure yet whether mine is completing or if it's timing out somewhere.
I might modify the code to do multiple queries in batches, possibly using mysql offset.

Test Signature

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):

Last edited by hybrid on Mon Dec 05, 2016 8:03 am; edited 1 time in total


Some further changes.
It will now process the posts table in a batch size that can be specified, and the page should reload to process another batch until it's done.
I'm not sure if I need an ORDER BY in the main SQL statement to ensure that the posts are returned in the same order each time so that OFFSET works properly.
Mine is still running now, so I won't know until the morning if it completed properly.

- in modules/Forums/l10n/en.php add
'zero_search_keywords' => 'Reset Rebuild Status',

- /admin/modules/forums/admin_searchkeywords.php

<?php /*************************************************************************** * admin_searchkeywords.php * ------------------- * begin : Thursday, Jul 12, 2001 * copyright : (C) 2001 The phpBB Group * email : support @ phpbb.com * ***************************************************************************/ /*************************************************************************** * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation; either version 2 of the License, or * (at your option) any later version. * ***************************************************************************/ /* Modifications made by CPG Dev Team http://cpgnuke.com */ /************************************************************************/ if (!defined('ADMIN_PAGES')) { exit; } if (isset($_GET['mode']) || isset($_POST['mode'])) { $mode = $_POST->txt('mode') ?: $_GET->txt('mode'); } else { $mode = isset($_POST['reset']) ? 'reset' : ''; } if ($mode) { if ('reset' == $mode) { $old_postcount = $_POST['old_post_count']; $old_search_words = $_POST['old_search_words']; $old_search_wordmatch = $_POST['old_search_wordmatch']; $batchsize = $_POST['batchsize']; $template->set_handle('body', 'admin/forums/keywords_edit'); // Check the query result database to find where the batch is up to. $result = $db->query("SELECT status from {$db->TBL->bbsearch_rebuild_status}"); $status = $result->fetch_assoc(); // If we have processed everything, just stop if ($status['status'] >= $old_postcount) { $template->old_search_words = $_POST['old_search_words']; $template->old_post_count = $_POST['old_post_count']; $template->old_search_wordmatch = $_POST['old_search_wordmatch']; $template->progress = $progress; $template->nextbatch = ""; $template->search_words = $result->num_rows; $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordmatch}"); $template->search_wordmatch = $result->num_rows; $template->processed = $newstatus; } else { // Clear the search word tables only if we are not in the middle of a rebuild already if ($status['status'] == 0) { $db->query("DELETE FROM {$db->TBL->bbsearch_wordlist}"); $db->query("DELETE FROM {$db->TBL->bbsearch_wordmatch}"); } //Go through each post and re-generate the search tables using the batch size specified $result = $db->query("SELECT post_id, post_subject, post_text FROM {$db->TBL->bbposts_text} LIMIT {$batchsize} OFFSET {$status['status']}"); while ($row = $result->fetch_assoc()) { \Dragonfly\Forums\Search::addWords($row['post_id'], $row['post_text'], $row['post_subject']); } // Update the status with the number of rows we just processed $newstatus = $status['status'] + $batchsize; $db->query("UPDATE {$db->TBL->bbsearch_rebuild_status} SET status={$newstatus}"); $progress = round($newstatus/$old_postcount*100); if ($progress > 100) { $progress = 100; //$db->query("UPDATE {$db->TBL->bbsearch_rebuild_status} SET status=0"); } $template->old_search_words = $_POST['old_search_words']; $template->old_post_count = $_POST['old_post_count']; $template->old_search_wordmatch = $_POST['old_search_wordmatch']; $template->progress = $progress; $template->nextbatch = "true"; $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordlist}"); $template->search_words = $result->num_rows; $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordmatch}"); $template->search_wordmatch = $result->num_rows; $template->processed = $newstatus; } } else if ('zero' == $mode) { $db->query("UPDATE {$db->TBL->bbsearch_rebuild_status} SET status=0"); $template->set_handle('body', 'admin/forums/keywords_list'); $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordlist}"); $template->search_words = $result->num_rows; $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordmatch}"); $template->search_wordmatch = $result->num_rows; $result = $db->query("SELECT post_id FROM {$db->TBL->bbposts}"); $template->post_count = $result->num_rows; $template->mode = $mode; $result = $db->query("SELECT status from {$db->TBL->bbsearch_rebuild_status}"); $status = $result->fetch_assoc(); if ($status['status'] != 0 && $status['status'] < $template->post_count) { $template->inprogress = "true"; } } } else { $template->set_handle('body', 'admin/forums/keywords_list'); $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordlist}"); $template->search_words = $result->num_rows; $result = $db->query("SELECT word_id FROM {$db->TBL->bbsearch_wordmatch}"); $template->search_wordmatch = $result->num_rows; $result = $db->query("SELECT post_id FROM {$db->TBL->bbposts}"); $template->post_count = $result->num_rows; $template->mode = $mode; $result = $db->query("SELECT status from {$db->TBL->bbsearch_rebuild_status}"); $status = $result->fetch_assoc(); if ($status['status'] != 0 && $status['status'] < $template->post_count) { $template->inprogress = "true"; } }

- /themes/admin/html/admin/forums/keywords_edit.xml
<div class="border"> <nav tal:replace="structure php:here.toString('admin/forums/menu')"></nav> <h1 i18n:translate="Keywords_title">Search Keywords</h1> <p i18n:translate="Keywords_explain">From this control panel you can reset the Search Keywords in the database. Use this if you don't think that the search results are accurate - ie, you're sure some search results are missing.</p> <p i18n:translate="Keywords_results"><br />Search Database Rebuild Results</p> <table><thead> <tr> <th i18n:translate="">Old Total Forum Posts</th> <th i18n:translate="">Old Search Keywords</th> <th i18n:translate="">Old Keyword Matches in Posts</th> </tr> </thead><tbody> <tr> <td tal:content="old_post_count">{old_postcount}</td> <td tal:content="old_search_words">{old_search_word}</td> <td tal:content="old_search_wordmatch">{old_search_wordmatch}</td> </tr> </tbody><tfoot> <tr> <th i18n:translate="">Progress</th> <th></th> <th i18n:translate="">Continuing?</th> </tr> <tr> <td tal:content="progress">{progress}</td> <td>%</td> <td tal:content="nextbatch">{nextbatch}</td> </tr> </tfoot></table> <br /><table><thead> <tr> <th i18n:translate="">Processed Posts</th> <th i18n:translate="">New Search Keywords</th> <th i18n:translate="">New Keyword Matches in Posts</th> </tr> </thead><tbody> <tr> <td tal:content="processed">{processed}</td> <td tal:content="search_words">{search_word}</td> <td tal:content="search_wordmatch">{search_wordmatch}</td> </tr> </tbody><tfoot></tfoot></table> </div> <script tal:condition="nextbatch" language="javascript">location.reload(true)</script>

- /themes/admin/html/admin/forums/keywords_list.xml

<div class="border"> <nav tal:replace="structure php:here.toString('admin/forums/menu')"></nav> <h1 i18n:translate="Keywords_title">Search Keywords</h1> <p i18n:translate="Keywords_explain">From this control panel you can reset the Search Keywords in the database. Use this if you don't think that the search results are accurate - ie, you're sure some search results are missing.</p> <p i18n:translate="Keywords_warning"><strong>Warning:</strong> This query may be very intensive, depending on the number of forum posts.</p> <form method="post" action=""> <input type="hidden" name="old_post_count" tal:attributes="value post_count"></input> <input type="hidden" name="old_search_words" tal:attributes="value search_words"></input> <input type="hidden" name="old_search_wordmatch" tal:attributes="value search_wordmatch"></input> <!-- <input type="hidden" name="mode" value="reset"></input> --> <table><thead> <tr> <th i18n:translate="">Total Forum Posts</th> <th i18n:translate="">Search Keywords</th> <th i18n:translate="">Keyword Matches in Posts</th> </tr> </thead><tbody> <tr> <td tal:content="post_count">{postcount}</td> <td tal:content="search_words">{search_word}</td> <td tal:content="search_wordmatch">{search_wordmatch}</td> </tr> </tbody><tfoot></tfoot></table> <br /> <table><thead> <tr> <th i18n:translate="">Reset Search Database</th> <th i18n:translate="">Number of posts to analyse per batch</th> <th tal:condition="inprogress">Rebuild In Progress or stale</th> </tr> </thead><tbody> <tr> <td> <button name="mode" value="reset" i18n:translate="reset_search_keywords">reset_search_keywords</button> </td> <td> <input type="text" name="batchsize" value="1000"></input> </td> <td tal:condition="inprogress"> <button name="mode" value="zero" i18n:translate="zero_search_keywords">zero_search_keywords</button> </td> </tr> </tbody></table> </form> </div>

Test Signature

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):


This is it working...

Test Signature

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):


Finished successfully, but I found some variables that were not right when it finished.
I've fixed those on mine, but won't spam any more here.

My original big bang script was not finishing.
That method was producing 43180 words and 993820 matches.

This new batch method is producing 74263 words and 2761222 matches.

Test Signature

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):


Another reason got rid of the index tables is that they Bloat the DB bigtime.
Was creating huge backups as well as excessive times to backup the database.
The direct search has been very efficient.

Dragonfly 9.4 Running on PHP 7.x + MariaDB

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
PHP Version 7.0.6 + MariaDB + Dragonfly 9.4 Modified


bulldog500 wrote
We just got rid of the wordlist search technique used by dragonfly.
We have more than 50,000 posts and directly
searching the table was just as fast and returns all records.

Perhaps using a Indexed wordlist table technique was needed years back
But directly searching the post works just fine in todays mysql or mira DB's

Actually i'm in the process of changing that. However, it is MySQL specific what you did there.

When we want to implement this in PostgreSQL it needs a tsvector column.
Also, HTML and BBCode shouldn't be indexed/searched on.

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
Fedora 25 / Apache 2.4.27 / MariaDB 10.1.26 / PHP 7.1.10 / Mercurial


PostgreSQL Full text search is great.
But doesn't do anything for mysql.
Besides the majority of sites with DF won't benefit for the waiting.
Although PostgreSQL is slightly slower (without the tsvector ) most DF PostgreSQL sites
don't suffer any problems getting rid of the search index tables. They just aren't that large.

Dragonfly 9.4 Running on PHP 7.x + MariaDB

Server specs (Server OS / Apache / MySQL / PHP / DragonflyCMS):
PHP Version 7.0.6 + MariaDB + Dragonfly 9.4 Modified

Last edited by bulldog500 on Tue Dec 06, 2016 2:15 pm; edited 3 times in total

All times are UTC
Go to page Previous 1, 2


Jump to: