sreeb

Faster Database search

Recommended Posts

Hi Everyone,

Since a couple of months, we have our new website live with processwire as CMS system.

In this website there is a huge database (1900+ trees), wich can be found with different filters. Take a look at www.vdberk.com/trees.
The website does not to be seen very fast when you select a couple of filters. Every time there is a delay between 3-5 seconds. 

Now we are migrate the website to another sever, with more preformance. So we hope this give us some timeprofits.

Does anyone have any kind of suggest, to make this filter faster. Now the website is on PHP5.6, is php7 better? Can this make a different? We used ProCache already.

Thanks in advance

 

  • Like 1

Share this post


Link to post
Share on other sites

Cool thanks @abdus,

Is processwire and the modules like procache and formbuilder etc ready for PHP 7. Or is this a heel of a job to make it work on php7?

We have processwire 3.0.

Are there any other sollution to make this huge database faster?

Share this post


Link to post
Share on other sites

It's usually plug and play, I dont remeber having any problems with the upgrade.

Unless you're using deprecated features, you should be ok

http://php.net/manual/en/migration70.php
http://php.net/manual/en/migration70.deprecated.php

Here are some guides on Tuts+ and DigitalOcean

https://code.tutsplus.com/tutorials/upgrading-your-linux-server-to-php-7--cms-27583
https://www.digitalocean.com/community/tutorials/how-to-upgrade-to-php-7-on-ubuntu-14-04

  • Like 1

Share this post


Link to post
Share on other sites

Definitely with @abdus on this one. PHP7 is noticeably faster than 5.6, and PW has no problems (that I'm aware of?) running under 7. If it is available, do it (taking the usual precautions, obvs).

Also, there might be optimisations you could make to your selectors and there are probably mysterious optimisations you may be able to make to your mySQL setup, depending on how much control you have within your hosting. First of those you will probably get help with here, if you are able to share some code, second is down to black magic, voodoo and selling the soul of your firstborn. Or hiring an expert.

All of that having been said, it's a very good looking website, and it works well - initial loading doesn't feel slow, just the filtering isn't instant.

<edit>Man, 3 more replies while I write this one.</edit>

  • Like 3
  • Haha 1

Share this post


Link to post
Share on other sites

Hi @sreeb

Do you cache results of searches? 

I have a similar project with a lot of filtering options. I use WireCache for caching search results ( PageArrays) and MarkupCache for result cards. It works quite fast on shared hosting. 

  • Like 1

Share this post


Link to post
Share on other sites

Superb website. It should be included in the featured sites if it isn't already.

1 hour ago, sreeb said:

The website does not to be seen very fast when you select a couple of filters.

Perhaps your filter queries are not optimal. Feel free to post the filter code if you would like any feedback on this.

  • Like 1

Share this post


Link to post
Share on other sites

A generous use of WireCache can help with generating overviews for a large number trees.

<?php namespace ProcessWire;
/** @var $cache WireCache */

foreach ($trees as $tree) {
    $overview = $cache->getFor($tree, "tree-overview");
    if (!$overview) {
        $overview = wireRenderFile('parts/tree-overview');
        $cache->saveFor($tree, 'tree-overview', WireCache::expireWeekly);
    }
    echo $overview;
}

 

Share this post


Link to post
Share on other sites

Wow, thanks a lot for al the suggets.

I will send this to our developers, so they can expertmate with it. 

@Zeka, do you have an example website so i can see the search?

Share this post


Link to post
Share on other sites

I am actually wondering if you might be better off not relying on ajax calls for each filter step. Have you considered storing all the details of all trees in a wireCache'd JSON object that you load up when the page originally loads and then filter through that in memory? I have done this before - I have 3MB of data in JSON which is cached, I make sure that data is transferred compressed (so it's only a couple of hundred KBs). The initially page load takes a couple of seconds, but after that, the filtering is instant. I think so long as you know that the data will not expand to an outrageous size, this might be a good approach.

PS - Great looking site!

  • Like 7

Share this post


Link to post
Share on other sites

Unfortunately, php7 does not give the desired speed gain. Do you have experience with Varnish Cache and PW?
Also extra server capacity did not do the job. It looks like its a code technical problem.

As i can see, all search query's are set as an URL, like:

https://www.vdberk.com/trees/?soil-type=1147&sort=0
https://www.vdberk.com/trees/?soil-type=1147&soil-moisture=1157&sort=0
https://www.vdberk.com/trees/?soil-type=1147&soil-moisture=1157&resistance=1189&sort=0

Etc.etc.

The page will not be change very often. they are quite static. It looks like below. But i'am not sure.
Unfortainly our developers cant find a sollution, so this is my last hope;)

 

 public function getTreesSelector($filterParams){
        
        $selector = array('template=tree-detail');
        
        foreach ($filterParams as $key => $value) {
            if(trim($value) != ""){
                $key = $this->getFilterKey($key);
                
                switch ($key) {
                    case "key":
                        $selector[] = "title|tree_specific_name|tree_family|tree_subfamily%=".urldecode($value);
                        break;

 

 

Share this post


Link to post
Share on other sites

i think you need to do some more debugging! i just played around with a local test-setup and got this results with 10.000 pages:

creation of pages:

//foreach($pages->find('parent=8181') as $p) $pages->delete($p);

$i=0;
$tmp = range('A', 'Z');
while($i<10000) {
    $p = new Page();
    $p->template = 'filtertest';
    $p->parent = 8181;
    $p->title = "test$i";
    $p->a = $tmp[array_rand($tmp)];
    $p->b = $tmp[array_rand($tmp)];
    $p->c = $tmp[array_rand($tmp)];
    $p->save();
    $i++;
}

dump (results):

d($pages->find('template=filtertest, a=a, b=b')->each('title'));
array (13)
14.48ms, 0.09MB

d($pages->find('template=filtertest, a|b|c=a|b|c')->each('title'));
array (3084)
1110.54ms, 6.54MB

d($pages->find('template=filtertest, a|b|c%=a|b|c')->each('title'));
array (3084)
1339.68ms, 6.54MB

your filter function looks totally weird to me. you have ID values as filter but then you use the slow %= selector. why? see my first example using " = " as selector should give you an instant result! also i don't understand why you are using the OR operator ( | ) for searching different fields. is the information spread over multiple fields?? shouldn't every filter-value be stored in a separate field?

 

PS: my template "filtertest" has fields A, B and C holding letters from A-Z

PPS: are you sure the selector is slowing the site down? maybe it is the way you count your number of results in the filter sidebar?

59e5bdbd413fb_2017-10-1710_21_03-Findtreesonline_over1600treesincl.photos.png.b8c5e5a0536365b848ebd064560462b9.png

  • Like 3

Share this post


Link to post
Share on other sites

you have 175 labels showing the tree count:

59e5f242727d4_2017-10-1714_05_09-ProgramManager.png.7267e1f4281f9a7582fa134d6b7dc622.png

i tried a loop with 200 iterations on my test-install with the SLOW selector from my above example and got all the counts within 500ms:

59e5f2435adcb_2017-10-1714_04_39-EditPage_Homehandsontable_dev.png.d6f258c818e7fee310afcc487e05ae62.png

so i think it should be no problem to get the site to returning results plus all the filter count-labels under 500ms.

but as i said in my pm: that can only be a wild guess as we don't know any details about your setup.

Share this post


Link to post
Share on other sites
11 minutes ago, bernhard said:

i tried a loop with 200 iterations on my test-install with the SLOW selector from my above example and got all the counts within 500ms:

Once PW fetches pages from DB, it saves them to memory, meaning subsequent find() operations with the same selector don't really have an effect, so it's essentially one DB operation.

// PagesLoaderCache.php

 * Cache the given selector string and options with the given PageArray
 *
 * @param string $selector
 * @param array $options
 * @param PageArray $pages
 * @return bool True if pages were cached, false if not
 *
 */
public function selectorCache($selector, array $options, PageArray $pages) {

    // get the string that will be used for caching
    $selector = $this->getSelectorCache($selector, $options, true);

    // optimization: don't cache single pages that have an unpublished status or higher
    if(count($pages) && !empty($options['findOne']) && $pages->first()->status >= Page::statusUnpublished) return false;

    $this->pageSelectorCache[$selector] = clone $pages;

    return true;
}

 

  • Like 1
  • Thanks 1

Share this post


Link to post
Share on other sites

ah, thanks, you are right :)

ok, so now i get 15seconds. if you really need the count-labels it seems you would need to implement some caching...

59e5f556a43a2_2017-10-1714_18_49-EditPage_Homehandsontable_dev.png.8a26d0eec99a1fbaacdb7dfd90888246.png

  • Like 2

Share this post


Link to post
Share on other sites

I haven't been following this closely for a while, but perhaps an SQL query would be helpful?

SELECT COUNT(id) 
FROM `pages` 
WHERE (pages.templates_id=29) 
AND (pages.title='My Page') 
AND (pages.status<1024) 
GROUP BY pages.id

You might find this snippet helpful. It will generate an SQL query from a find selector. I just modified the returned result to be a COUNT instead.

59e5ff7bae8cd_ScreenShot2017-10-17at6_02_18AM.thumb.png.fc0a6aba609e8be85a713efeb323d26b.png

Share this post


Link to post
Share on other sites

No if it makes the speed  much faster, the countlabels can go away.

I will ask to our developers to look at this. Maby this give them some information.

Is Varnish caching a good sollution?

Share this post


Link to post
Share on other sites
23 minutes ago, adrian said:

It will generate an SQL query from a find selector. I just modified the returned result to be a COUNT instead.

A simpler way could be passing a second parameter to $pages->count() method like this

$count = $pages->count('id>0', ['getTotalType' => 'calc']); // default, slower, fetches everything then counts
$count = $pages->count('id>0', ['getTotalType' => 'count']); // uses SQL query, faster

From the core:

Spoiler

<?php
/**
 * Return all pages matching the given selector.
 *
 * @param Selectors|string|array $selectors Selectors object or selector string
 * @param array $options
 * ....
 *  - `getTotalType` (string): Method to use to get total, specify 'count' or 'calc' (default='calc').
 * ....
 */
public function ___find($selectors, array $options = [])
{
    // ...
    if ($this->getTotal) {
        if ($this->getTotalType === 'count') {
            $query->set('select', ['COUNT(*)']);
            $query->set('orderby', []);
            $query->set('groupby', []);
            $query->set('limit', []);
            $stmt = $query->execute();
            $errorInfo = $stmt->errorInfo();
            if ($stmt->errorCode() > 0) throw new PageFinderException($errorInfo[2]);
            list($this->total) = $stmt->fetch(\PDO::FETCH_NUM);
            $stmt->closeCursor();
        } else {
            $this->total = (int)$database->query("SELECT FOUND_ROWS()")->fetchColumn();
        }

    } else {
        $this->total = count($matches);
    }

    // ...
}

Results:

image.png.b05a23ddb6658958e44bb0b8874b9b0b.png

 

  • Like 2

Share this post


Link to post
Share on other sites
2 minutes ago, sreeb said:

hi @adrian, how can i run that snippet?

Which one - the one to generate an SQL query from a PW selector, or the actual SQL query itself?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By sam-vital
      Hi,
      I'm trying to setup up ProceesWire for a site I am working on, and I'm having troubles with the Database section.
      I get these errors :
      Database connection information did not work.
       SQLSTATE[HY000] [1045] Access denied for user
      I also cannot connect to phpMyAdmin, Xampp is what I am using as my stack.
      None of these errors have occurred before which is annoying me to say the least.
      Help is appreciated 🙂
    • By Noel Boss
      Page Query Boss
      Build complex nested queries containing multiple fields and pages and return an array or JSON. This is useful to fetch data for SPA and PWA.
      You can use the Module to transform a ProcessWire Page or PageArray – even RepeaterMatrixPageArrays – into an array or JSON. Queries can be nested and contain closures as callback functions. Some field-types are transformed automatically, like Pageimages or MapMarker.
      Installation
      Via ProcessWire Backend
      It is recommended to install the Module via the ProcessWire admin "Modules" > "Site" > "Add New" > "Add Module from Directory" using the PageQueryBoss class name.
      Manually
      Download the files from Github or the ProcessWire repository: https://modules.processwire.com/modules/page-query-builder/
      Copy all of the files for this module into /site/modules/PageQueryBoss/ Go to “Modules > Refresh” in your admin, and then click “install” for the this module. Module Methods
      There are two main methods:
      Return query as JSON
      $page->pageQueryJson($query); Return query as Array
      $page->pageQueryArray($query); Building the query
      The query can contain key and value pairs, or only keys. It can be nested and 
      contain closures for dynamic values. To illustrate a short example:
      // simple query: $query = [ 'height', 'floors', ]; $pages->find('template=skyscraper')->pageQueryJson($query); Queries can be nested, contain page names, template names or contain functions and ProcessWire selectors:
      // simple query: $query = [ 'height', 'floors', 'images', // < some fileds contain default sub-queries to return data 'files' => [ // but you can also overrdide these defaults: 'filename' 'ext', 'url', ], // Assuming there are child pages with the architec template, or a // field name with a page relation to architects 'architect' => [ // sub-query 'name', 'email' ], // queries can contain closure functions that return dynamic content 'querytime' => function($parent){ return "Query for $parent->title was built ".time(); } ]; $pages->find('template=skyscraper')->pageQueryJson($query); Keys:
      A single fieldname; height or floors or architects 
      The Module can handle the following fields:
      Strings, Dates, Integer… any default one-dimensional value Page references Pageimages Pagefiles PageArray MapMarker FieldtypeFunctional A template name; skyscraper or city
      Name of a child page (page.child.name=pagename); my-page-name A ProcessWire selector; template=building, floors>=25
      A new name for the returned index passed by a # delimiter:
      // the field skyscraper will be renamed to "building": $query = ["skyscraper`#building`"]  
      Key value pars:
      Any of the keys above (1-5) with an new nested sub-query array:
      $query = [ 'skyscraper' => [ 'height', 'floors' ], 'architect' => [ 'title', 'email' ], ]  
      A named key and a closure function to process and return a query. The closure gets the parent object as argument:
      $query = [ 'architecs' => function($parent) { $architects = $parent->find('template=architect'); return $architects->arrayQuery(['name', 'email']); // or return $architects->explode('name, email'); } ] Real life example:
      $query = [ 'title', 'subtitle', // naming the key invitation 'template=Invitation, limit=1#invitation' => [ 'title', 'subtitle', 'body', ], // returns global speakers and local ones... 'speakers' => function($page){ $speakers = $page->speaker_relation; $speakers = $speakers->prepend(wire('pages')->find('template=Speaker, global=1, sort=-id')); // build a query of the speakers with return $speakers->arrayQuery([ 'title#name', // rename title field to name 'subtitle#ministry', // rename subtitle field to ministry 'links' => [ 'linklabel#label', // rename linklabel field to minlabelistry 'link' ], ]); }, 'Program' => [ // Child Pages with template=Program 'title', 'summary', 'start' => function($parent){ // calculate the startdate from timetables return $parent->children->first->date; }, 'end' => function($parent){ // calculate the endate from timetables return $parent->children->last->date; }, 'Timetable' => [ 'date', // date 'timetable#entry'=> [ 'time#start', // time 'time_until#end', // time 'subtitle#description', // entry title ], ], ], // ProcessWire selector, selecting children > name result "location" 'template=Location, limit=1#location' => [ 'title#city', // summary title field to city 'body', 'country', 'venue', 'summary#address', // rename summary field to address 'link#tickets', // rename ticket link 'map', // Mapmarker field, automatically transformed 'images', 'infos#categories' => [ // repeater matrix! > rename to categories 'title#name', // rename title field to name 'entries' => [ // nested repeater matrix! 'title', 'body' ] ], ], ]; if ($input->urlSegment1 === 'json') { header('Content-type: application/json'); echo $page->pageQueryJson($query); exit(); } Module default settings
      The modules settings are public. They can be directly modified, for example:
      $modules->get('PageQueryBoss')->debug = true; $modules->get('PageQueryBoss')->defaults = []; // reset all defaults Default queries for fields:
      Some field-types or templates come with default selectors, like Pageimages etc. These are the default queries:
      // Access and modify default queries: $modules->get('PageQueryBoss')->defaults['queries'] … public $defaults = [ 'queries' => [ 'Pageimages' => [ 'basename', 'url', 'httpUrl', 'description', 'ext', 'focus', ], 'Pagefiles' => [ 'basename', 'url', 'httpUrl', 'description', 'ext', 'filesize', 'filesizeStr', 'hash', ], 'MapMarker' => [ 'lat', 'lng', 'zoom', 'address', ], 'User' => [ 'name', 'email', ], ], ]; These defaults will only be used if there is no nested sub-query for the respective type. If you query a field with complex data and do not provide a sub-query, it will be transformed accordingly:
      $page->pageQueryArry(['images']); // returns something like this 'images' => [ 'basename', 'url', 'httpUrl', 'description', 'ext', 'focus'=> [ 'top', 'left', 'zoom', 'default', 'str', ] ]; You can always provide your own sub-query, so the defaults will not be used:
      $page->pageQueryArry([ 'images' => [ 'filename', 'description' ], ]); Overriding default queries:
      You can also override the defaults, for example
      $modules->get('PageQueryBoss')->defaults['queries']['Pageimages'] = [ 'basename', 'url', 'description', ]; Index of nested elements
      The index for nested elements can be adjusted. This is also done with defaults. There are 3 possibilities:
      Nested by name (default) Nested by ID Nested by numerical index Named index (default):
      This is the default setting. If you have a field that contains sub-items, the name will be the key in the results:
      // example $pagesByName = [ 'page-1-name' => [ 'title' => "Page one title", 'name' => 'page-1-name', ], 'page-2-name' => [ 'title' => "Page two title", 'name' => 'page-2-name', ] ] ID based index:
      If an object is listed in $defaults['index-id'] the id will be the key in the results. Currently, no items are listed as defaults for id-based index:
      // Set pages to get ID based index: $modules->get('PageQueryBoss')->defaults['index-id']['Page']; // Example return array: $pagesById = [ 123 => [ 'title' => "Page one title", 'name' => 123, ], 124 => [ 'title' => "Page two title", 'name' => 124, ] ] Number based index
      By default, a couple of fields are transformed automatically to contain numbered indexes:
      // objects or template names that should use numerical indexes for children instead of names $defaults['index-n'] => [ 'Pageimage', 'Pagefile', 'RepeaterMatrixPage', ]; // example $images = [ 0 => [ 'filename' => "image1.jpg", ], 1 => [ 'filename' => "image2.jpg", ] ] Tipp: When you remove the key 'Pageimage' from $defaults['index-n'], the index will again be name-based.
       
      Help-fill closures & tipps:
      These are few helpfill closure functions you might want to use or could help as a
      starting point for your own (let me know if you have your own):

      Get an overview of languages:
          $query = ['languages' => function($page){         $ar = [];         $l=0;         foreach (wire('languages') as $language) {             // build the json url with segment 1             $ar[$l]['url']= $page->localHttpUrl($language).wire('input')->urlSegment1;             $ar[$l]['name'] = $language->name == 'default' ? 'en' : $language->name;             $ar[$l]['title'] = $language->getLanguageValue($language, 'title');             $ar[$l]['active'] = $language->id == wire('user')->language->id;             $l++;         }         return $ar;     }]; Get county info from ContinentsAndCountries Module
      Using the [ContinentsAndCountries Module](https://modules.processwire.com/modules/continents-and-countries/) you can extract iso
      code and names for countries:
          $query = ['country' => function($page){         $c = wire('modules')->get('ContinentsAndCountries')->findBy('countries', array('name', 'iso', 'code'),['code' =>$page->country]);         return count($c) ? (array) $c[count($c)-1] : null;     }]; Custom strings from a RepeaterTable for interface
      Using a RepeaterMatrix you can create template string for your frontend. This is
      usefull for buttons, labels etc. The following code uses a repeater with the
      name `strings` has a `key` and a `body` field, the returned array contains the `key` field as,
      you guess, keys and the `body` field as values:
          // build custom translations     $query = ['strings' => function($page){         return array_column($page->get('strings')->each(['key', 'body']), 'body', 'key');     }]; Multilanguage with default language fallback
      Using the following setup you can handle multilanguage and return your default
      language if the requested language does not exist. The url is composed like so:
      `page/path/{language}/{content-type}` for example: `api/icf/zurich/conference/2019/de/json`
       
          // get contenttype and language (or default language if not exists)     $lang = wire('languages')->get($input->urlSegment1);     if(!$lang instanceof Nullpage){         $user->language = $lang;     } else {         $lang = $user->language;     }     // contenttype segment 2 or 1 if language not present     $contenttype = $input->urlSegment2 ? $input->urlSegment2 : $input->urlSegment1;     if ($contenttype === 'json') {         header('Content-type: application/json');         echo $page->pageQueryJson($query);         exit();     } Debug
      The module respects wire('config')->debug. It integrates with TracyDebug. You can override it like so:
      // turns on debug output no mather what: $modules->get('PageQueryBoss')->debug = true; Todos
      Make defaults configurable via Backend. How could that be done in style with the default queries?
      Module in alpha Stage: Subject to change
      This module is in alpha stage … Query behaviour (especially selecting child-templates, renaming, naming etc)  could change
    • By rareyush
      i am receiving and error whenever I try to run my processwire on localhost,

       
       
      sql code
       
      -- -- Table structure for table `field_fieldset_meta_end` -- CREATE TABLE `field_fieldset_meta_end` ( `pages_id` int(10) UNSIGNED NOT NULL, `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `field_fieldset_meta_END` -- CREATE TABLE `field_fieldset_meta_END` ( `pages_id` int(10) UNSIGNED NOT NULL, `data` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; anyone ?
      whenever I make a new database and upload it there, database get imported without errors.
    • By modifiedcontent
      I have one central website, with membership registration and content etc., and then several related websites with their own URL/domains, each on Processwire, all on the same server.
      I would like to access the database of the central website from the sister websites. How would I do that?
      You can't bootstrap one PW installation into another. You can include template parts from one in the other by just using the server path, but whatever you try to get/post just comes/goes to the database of the site you are on.
      Could you switch databases by including the config.php from another PW installation somewhere?
      What is the correct, secure way to do this?
    • By answersmode
      Please help me........
      DATABASE CONNECTION PROBLEM  - CREATE DATABASE IF NOT EXISTS `lwteswm664_ltingesjfo_itbd` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
      Error
      SQL query:
       
      -- -- Database: `lwteswm664_ltingesjfo_itbd` -- CREATE DATABASE IF NOT EXISTS `lwteswm664_ltingesjfo_itbd` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;  
      MySQL said:
      #1044 - Access denied for user 'newhost'@'localhost' to database 'lwteswm664_ltingesjfo_itbd' 
       
      I'm facing this peoblem in my website xxxxx and xxxxx When I wanted to upload my databaz backup file.
      Now what can I do?
      Please help me........