[VladVladikoff]

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)

[monooso]

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.

[Loic]

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.

[lucb1e]

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

[Breza]

Well said! At work, I deal with these kinds of issues, and they get messy. I've spent hours this week discussing and debating table indexes and caching parameters for a system that's been running for fifteen years but we think we can improve. There's a diminishing return to putting tons of your time into every little thing, especially when a project is not your livelihood. It sounds like you've taken a thoughtful approach to your system architecture. If it works for you, it works!

[a_ko]

OP is driving with handbrake engaged.

[lucb1e]

How so?

[lucb1e]

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

[Breza]

But but my GCP-PDE exam said every project needs massive and expensive infrastructure to optimize every tiny detail, minimize latency, and deliver five nines of availability! If your cloud bill isn't five figures every month, are you even a dev? /s