Getting a list of recently added images more quickly

[BillH]

I'm using the following code to get an array of recently added images with various bits of information about them, sorted in reverse order of when they were added.

$startDate = strtotime("-2 months");
$pagesWithRecentImages = $pages->find("article_images.created>{$startDate},include=unpublished");
$recentImages = array();
foreach($pagesWithRecentImages as $pwri) {
    foreach($pwri->article_images as $image) {
        if ($image->created >= $startDate) {
            $sortKey = $image->created;
            $recentImages[$sortKey] = array($image->page->title, $image->page->url, $image->basename, $image->created);
        }
    }
}
krsort($recentImages);

It works fine, and I'm displaying the contents of the array on a dashboard page on the admin side.

However, it runs rather slowly, delaying the loading of the dashboard page by a few seconds (around six or seven seconds when getting images from about 5,000 pages). The page already takes 3 or 4 seconds to load (it shows other stuff too), and a total of ten seconds or so is a bit frustrating for users.

I've been trying to work out a faster way of getting an array of images (perhaps using different selector), but so far with no success.

Can anyone think of a way to get it to run more quickly?

Thanks!

 

[Zeka]

@BillH Have you tried to use 'findMany' method? Does it make different? 

[Autofahrn]

@BillH, I'd probably go with a pretty different approach and manage a separate "RecentList" when updating entries instead for each view.

If images are stored on individual pages, you simply maintain a repeater (on some hidden page) holding a page reference to each image. Whenever a new image is uploaded (i.e. use hook on page save), a link is added to that repeater. If it has too many items, the oldest are removed in that step.

Since you are using multiple images per article, your repeater has to hold a reference to the article and the basename of the newly added image.

On page view you simply load the repeater content (no search at all).

To speed up things, you may add more fields to that repeater, like the title of the article, so there is no need for additional database queries. To ensure titles stay in sync, your page save hook needs to update the cached article titles as well.

[Zeka]

@BillH Also if you are familiar with SQL you can get recent images directly from DB with one query and then find corresponding pages if you need some information from them. 

There is RockFinder module that can perform faster searches in some scenarios (complicated dashboards etc.) 

[BillH]

Thanks @Zeka, that makes a big difference, reducing the time taken to less than half of what is was previously.

And thanks also @Autofahrn, that sounds a really interesting approach to improving performance further. The system is already quite well established, so I probably can't switch to having each image on a page (or at least not easily), but some variation on your idea might work well. For example, hooking on image load and storing relevant data (such as the time of the most recent image load) in a field on the page or some similar approach might work.

And @Zeka, the SQL and RockFinder ideas seem interesting too.

I now need to close down for the day, but I'll look into all these further over the next day or so.

[BillH]

After some experimentation, here's what I'm now using to get the relevant pages:

$startDate = strtotime("-2 months");

// Use RockFinder to get recently saved pages with images
$recentPagesWithImagesSelector = "has_parent=/articles/,article_images.count>0,modified>{$startDate},include=unpublished";
$finderRPWI = new RockFinder($recentPagesWithImagesSelector,['title']);
$recentPagesWithImages = $finderRPWI->getPages();

// Use standard PW find to reduce to pages with at least one recent image
$pagesWithRecentImages = $recentPagesWithImages->find("article_images.created>{$startDate}");

No doubt this could be improved (e.g. by maintaining an index of recent images), but it reduces the page-loading time to something acceptable.

Note that I tried a one-step process using RockFinder only with "article_images.created>{$startDate}" in the selector, but this produced a few unexpected results, and anyway was slower than the above. I didn't investigate what was happening in any depth.

I have also used RockFinder in another place on the page where a search was running slowly, and the page is now loading more quickly than before I added the list of images!

[BillH]

I've come up with some seriously fast ways of doing this – following the suggestion from @Zeka and taking inspiration from the code in RockFinder. I'm posting them here in case anyone finds them useful.

The RockFinder approach in my previous post takes about 0.95 seconds (across about 5,000 pages with about 1,500 associated images, resulting in about 30 pages in the final array), which is fine.

But the following, using direct SQL access, produces exactly the same result in around 0.0055 seconds (much of which is making the final page array, so if there were more pages it would be somewhat slower).

startDate = strtotime("-2 months");

// Run query on database
$startDateString = date('Y-m-d H:i:s', $startDate);
$queryString = "SELECT pages_id FROM field_article_images WHERE created >= '{$startDateString}'";
$queryResult = $this->db->query($queryString);

// Get array of unique page IDs
$pageIdArray = $queryResult->fetch_all(MYSQLI_NUM);
$pageIdArray = array_unique($pageIdArray, SORT_REGULAR);

// Make PW page array
$pagesWithRecentImages = new PageArray();
foreach($pageIdArray as $pageIdSubArray) {
	$pwPage = $pages->get($pageIdSubArray[0]);
	$pagesWithRecentImages->add($pwPage);
}

And if an array of image data from the database table would be sufficient, the following is even faster, at around 0.0014 seconds.

$startDate = strtotime("-2 months");

// Run query on database
$startDateString = date('Y-m-d H:i:s', $startDate);
$queryString = "SELECT * FROM field_article_images WHERE created >= '{$startDateString}'";
$queryResult = $this->db->query($queryString);

// Make array of image data
$imageData = $queryResult->fetch_all(MYSQLI_ASSOC);

(Adding "ORDER BY created DESC" to the SQL query above could be a useful addition.)

It's worth noting that the SQL queries above are quite straightforward because they are on a single table, and the image field in question appears only on relevant pages. For more complex situations, using RockFinder would be a great deal easier, and I'd guess the speed difference would be less.