MySQL makes my site load very slow [closed]

Question

I can't seem to find what the problem is.

My homepage takes 5s to load and I suspected highly its because of MySQL. when I type my site url in the browser, I have to wait 5s only then it will load.

I have dedicated server running intel celeron, centos with 1GB RAM. My database is approx 650mb in size with 200k records and i have index created for date,id etc.

Here is my SQL query used in the homepage:

select `id`,`title`,`desc`,`contribution`,`date`,`sponsored`                
from tbl_releases where date <= CURRENT_DATE()  AND approved='1' 
order by `date` desc,`sponsored` desc,`con` desc,`id` desc
limit 100

I tried every everything like

1. optimizing using my.cnf with buffer etc but its not improving the site performance.
2. optimizing table.

P.S: does database size have impact on performance? I have over 50% junk in database and does deleting useless rows help improve the performance?

---

update: i did cleanup junk in the database and the performance improved dramatically and i noticed that server load and mysql cpu usage has gone significantly down from 36% to just 5% . But still what i dont understand, is why is mysql is slowing down on such large data, it should pretty much handle that much data. isnt it?

Btw, i am using smarty engine, despite this, site is slowing down.

Answer 1

Well there are many reasons why your site could be slow.

• Bad queries
• Improper indexes in database
• Lot of images
• Large images
• Lot of scripts and may others

MySQL may be one reason, so make sure to clean up your database, create proper indexes, use Memcache, etc. Also analyse your select queries using EXPLAIN

Here is an article written by me on optimizing websites.