Optimizations for gallery page view

[jywarren]

...which is experiencing slow load times -- possibly connected to #1167...?

mapknitter/app/controllers/front_ui_controller.rb

Lines 64 to 73 in 5022129

	def gallery
	@maps = Map.page(params[:page])
	.per_page(20)
	.where(archived: false, password: '')
	.order('updated_at DESC')
	.group('maps.id')
	@authors = User.where(login: Map.featured.collect(&:author))
	.paginate(page: params[:mappers], per_page: 20)
	end

Looking in skylight here: https://oss.skylight.io/app/applications/ArYnJAb3VUC9/1589300520/6h/endpoints

[jywarren]

https://oss.skylight.io/app/applications/ArYnJAb3VUC9/1589236560/1d/endpoints/FrontUiController%23gallery?responseType=html

[jywarren]

Nice, a lot is selection from maps -- which we do call twice. I wonder if we simply need some more indexing on the maps table?

[jywarren]

I mean, pagination via the will_paginate gem should cover limiting the query.

Indeed we only index maps on slug it seems?

mapknitter/db/schema.rb

Line 78 in 2c5704c

t.index ["slug"], name: "index_maps_on_slug", unique: true

The queries we are seeing take 1.2 seconds each are:

SELECT maps.*, count(maps.id) as image_count FROM `maps` INNER JOIN `warpables` ON `warpables`.`map_id` = `maps`.`id` GROUP BY warpables.map_id ORDER BY image_count DESC

and

SELECT  maps.*, count(maps.id) as image_count FROM `maps` INNER JOIN `warpables` ON `warpables`.`map_id` = `maps`.`id` WHERE `maps`.`archived` = ? AND `maps`.`password` = ? GROUP BY maps.id, warpables.map_id ORDER BY updated_at DESC, image_count DESC LIMIT ? OFFSET ?

[jywarren]

Ah, if we are joining warpables and we are saving a TON of versions of those, maybe that is causing the slowness. Yes:

mapknitter/app/models/map.rb

Lines 92 to 97 in 2c5704c

	def self.featured
	Map.joins(:warpables)
	.select('maps.*, count(maps.id) as image_count')
	.group('warpables.map_id')
	.order('image_count DESC')
	end

[jywarren]

So, we may be able to filter out any warpables which aren't the latest version, here: https://github.com/paper-trail-gem/paper_trail#3-working-with-versions

This uses a versions table, but I'm not sure if it creates many copies of the warpables records themselves?

[jywarren]

Updating that we actually aren't saving all that many versions of images, we've found. But we could try for a narrower query on line 93, anyways? I mean, we only use the count and the first one (for the lead image).