Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Optimizations for gallery page view #1297

Open
jywarren opened this issue May 12, 2020 · 6 comments
Open

Optimizations for gallery page view #1297

jywarren opened this issue May 12, 2020 · 6 comments
Labels
hacktoberfest For hackoberfest participants

Comments

@jywarren
Copy link
Member

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

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
Copy link
Member Author

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
Copy link
Member Author

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

Indeed we only index maps on slug it seems?

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
Copy link
Member Author

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

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

@jywarren
Copy link
Member Author

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
Copy link
Member Author

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).

@cesswairimu cesswairimu added the hacktoberfest For hackoberfest participants label Sep 21, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
hacktoberfest For hackoberfest participants
Projects
None yet
Development

No branches or pull requests

2 participants