How to balance quick fixes with schema changes for a slow five-table join?

[Olivia.P]

I'm a database administrator for an e-commerce platform, and we're experiencing severe performance degradation on our product search page, where a critical SQL query joining five large tables is timing out during peak traffic, despite what I thought were appropriate indexes. I've run the query execution plan and see a costly table scan on one of the joined tables, but even after adding a suggested index, the optimizer sometimes ignores it and reverts to the scan, causing inconsistent performance. For other DBAs who have tackled similar SQL performance tuning challenges, what systematic approach do you take to diagnose and resolve these issues? Beyond indexing, what are the most effective strategies for rewriting a complex query or restructuring the schema to avoid these bottlenecks, and how do you balance the need for immediate fixes with long-term architectural changes?