> It sounds like poorly designed database queries with unindexed joins

Neither of those assumptions are correct. As an example, one page needs to look through 2.5 million records to find where the world record holder changed because it provides stats on who held the most records, held them for the greatest cumulative time, etc. The only thing to do would be introducing caching layers for parts of the computation, but for the number of users this system has, it's just not worth spending more development time than I already have. Also keep in mind it's a free web service and I don't run ads or anything, it's just a fan project for a game

> Google will ... demote rankings for that page (or the entire site!)

Google employs anticompetitive practices to maintain the search monopoly. We need more diversity in search engines, I don't know how else to encourage people to use something instead of, or at least in addition to, Google, besides by making Google Search just not competitive anymore. Google's crawler cannot access my site in the first place (but their other crawlers can; I'm pretty selective about this). My sites never show up in Google searches, on purpose

It's also not the whole site that's slow, it's when you click on a handful of specific pages. If that makes those pages not appear in search results, that's fine. Besides that it's not my loss, it's not like any other site has the info so people will find their way to the main page and click on what they want to see

Like I said then, you need indexes on those columns which you filter on in this table. Search a table of 2.5 million records for a value is still blazing fast if you use indexes correctly. I’m talking about 0.01 seconds or less. Even with tables much larger.

I agree about Google being shit. However, my website makes my living, and feeds and clothes my children, so I have to play along to their rules, or suffer.

Please take your slowest performing query and run it with EXPLAIN in front. And share that (or dump it into an LLM and it will tell you have to fix it)

You have very strong opinions about a site you effectively know nothing about.

Instead of immediately concluding that the person actually building the system is an incompetent fool who doesn't know any better, maybe work on the assumption that they know what they're doing, and have already considered the various trade-offs.

If nothing else, that would be considerably less obnoxious.

You need to drive and fine tune a Ferrari because it feeds your family. The OP just drive a nice little car, because it is fun to drive and he enjoys it. He could extract another +5% of torque by fine tuning, but he does not care, this is not where his joy is and where he wants to spend his time.

To be completely fair to the person you're responding to, they're talking about pages that take 20 seconds to load. On a regular website that hopes to get visitors from search engines, say, that is indeed insane and the fix is not to squeeze out 5% by fine tuning, the fix is to re-architect the thing

I don't mind people asking why it is this slow (whether I can't or why I don't re-architect it) or suggest fixes, but as the sibling comment to yours (from u/monooso) put it well, it would be nice if one does not assume that I'm an incompetent fool. The person also doesn't seem to read what I explained before suggesting more of the same in another reply. Thanks for adding your comment as well, I appreciate the sentiment. Even if I'm not sure if it applies in this case, it definitely applies to other things I do (I may have too many hobby projects running on that server.. ^^)

OP is driving with handbrake engaged.

How so?

Right...

In case it helps to understand: compare it to something like weather models. You can't "just add indexes" to make it fast, but you can store the result of an hourly run and serve that to people in milliseconds. In my case, nobody's paying me to serve them that 'weather report' so it is what it is

> Like I said then, you need indexes on those columns

Dude, like I said ("that's not a correct assumption" in response to "It sounds like ... unindexed joins"), I have indexes on the relevant columns in the correct order

Believe me I've benchmarked and SQL-EXPLAINed everything. All substantial queries have a code comment saying what index it uses as a way to make sure that changes to one query (and its corresponding index) don't affect another. I've learned a lot in this project about how everything from the different Where parts to the Order By clause to cardinality estimates to explicit index hints affect which indexes it can use as well as chooses to use. I enjoy learning about it, but now that I know the things relevant for this project, I'm also just done with the project and would rather spend my spare time on something new rather than adding code and introducing code and/or infrastructure complexity for storing parts of the computation that don't frequently change for example. Or if it was a popular site with frequent new visitors, that could be worth it as well. It's not. That doesn't make it necessarily a poor design if it's simply a lot of data

> Search a table of 2.5 million records for a value is still blazing fast

If you read what I write then you'd know it's not about looking up a single record

> It sounds like poorly designed database queries with unindexed joins.

I find it amusing that you think every database operation imaginable can be performed in less than 20 seconds if we throw in a few indexes. Some things are slow no matter how much you optimise them.

The GP could have implemented them as async endpoints, or callbacks, but obviously they've already considered those options.

It's the kind of prescriptive cargo culting that is responsible for a significant fraction of pain involved in software engineering, right up there with DRY and KISS and shitty management.

I bet the GP abstracts out a function the second there's a third callsite too, regardless of where it's used or how it will evolved - only to add an options argument and blow up the cyclomatic complexity three days later.

So what about flight searches where we have to query several 3rd party providers, and can take 45 seconds to get results from all of them (out of my control). I can dynamically update the page (and do) but a scraper would have to wait 20-45 seconds to get the 'cheapest' flight from my site. I can add async the queries and have the fastest pipes, but if the upstream providers take their time (they need to query their GDSs as well), there's not much you can do.