There are 3 important things affected to MySQL performance

BUFFERS

Buffers is temporary where data lives in memory. Because memory speed > disk speed ⇒ keep data fit in memory is best way to keep good performance.

So, before thinking about scale horizontally by clustering or sharding, just throw your money to upgrade your server specs. Keep it simple and live in only server with large memory.

INDEXES

Index could make your query faster, and ... slower. Really, no silver bullets in tech, monitoring your slow query and find out why it was slowww.

Even turn off query optimizer in specific query which full scan table is faster than range index scan.

JOINs

JOIN makes SQL databases good, popular, easier for users (developer, data analysis, BI, ...). But, things alway have downside, "easier" makes users free from making it right and making it fast (they just focus to make it work).

Btw, if your query join tables within <100,000 rows, then don't worry, be happy 😎

Good luck! ;)

Ref :