I have an application that has thousand of records. the problem that i am having right now is that my website is performing slow.when i am trying to swtich to other link on the site which have a query running on the background it takes ages to load. i already tried using cloudflare to optimize the system but the slow querying still persist. can anybody have an idea that can help me optmize the poor performace of my database?
By the way the system is hosted in a dedicated server but the resources consumption is very high.
What is the query trying to do?
If it is returning a large record set, written bad or uses a large number of joins it could be hogging the resources.
Find a tool for analysing the query (MySQL Workbench or MS SQL Server Management Studio for example, depending on your database type) and check how the query performs.
Then either re-write if possible or make it return a smaller subset of rows (by using pagination)
The query is fetching all the record frm 3 tables using inner join and I am using pagination displaying it.
I tried adding Indexes in all the column that im searching or connecting with and it improves that over all performance of the system. I am on the observation part now. hope that it fix the problem permanently.
I'll close this thread later on when i validated that the solution is effective.