php and mysql optimization

site slow because of the mysql processes, how to speed it up

[fkroerank]

Ok, heres what I'm currently running into and hopefully someone could help out with it.

I'm on a cluster server package with an opteron 244 mysql server and then an Athlon64 x2 3800+ server supporting the site. The site runs off of mysql and php, and recently the site has slowed down a great deal and its been getting to me as to how I could speed it up. The site is a layout making community with 13K+ members and over 5K layouts that the members are able to submit. I get roughly about 120 layouts submited a day, and within the past week the site has been slowing down greatly.

I tried optimizing the code more and more as I could, and I believe everything is optimized to the best of my knowledge. I dont use any kinds of SELECT * FROM blah blah, I have SELECT id, name FROM table LIMIT 0, 25 and sometimes I have a WHERE category = category in the statement. I also run some queries to select how many layouts are in the category, and how many are there total, and to do that I would run something like SELECT COUNT(id) form table.

Is there anyone that could give me some kind of pointers with dealing with mysql to make the load lower? I tried using some JOIN table ON some.id = this.uid but that didnt seem to speed it up any to my knowledge at least.

[eelixduppy]

Welcome to Webmasterworld!

You can find more information about Optimization [dev.mysql.com] at [mysql.com...]

Good luck

[fkroerank]

thanks for the welcome.

I've been looking over the optimization at mysql.com but couldnt come up with a good solution to try to use. If someone has learned anything from past expirences with large websites growing at a decent pase, I would like to know how or what they did to get it all working faster. =/

[coopster]

It's all there in the link. I would start by EXPLAINing some of the SELECT statements to see how you could create INDEXes on your tables. I once took a query from 51 seconds down to milliseconds (on a different database) by optimizing a complex SELECT query joining tables with well over 3 million rows in one of the tables. Get your indexes in place for optimal performance is one of the very first tasks at hand.

[whoisgregg]

I have a table that I need to ANALYZE [dev.mysql.com] about once a week or it gets unbearably slow. ::looks at feet, kicks ground:: It fixes it, so I haven't taken the time to determine the problem yet.

[fkroerank]

sorry for the late reply, I just got back from vacation and figured I would note here what I did to actually speed up the site a bit.

I went through some of my database tables and it turned out that I didnt index some columns that I really should have. One of the main columns I should have indexed before was one for my comments. I let some of my members comment each other on their profiles and within the table for that, I had something like toUser where would be the users id number. Once I undexed that column it started speeding up a lot more.

In other words, if you're using some kind of WHERE thisID = 2 in the mysql query, try to have the 'thisID' column indexed.

[Sekka]

9/10 it is always down to poor database structure, or that is the case when I have been brought in to optimise a website.

Also, I sometimes find that it can be quicker to run 2 seperate queries than 1 query with a join.

Just experiment.