mysql queries optimization

Question

I'm having problem with my classifieds site even though using dedicated server but still slow and many times my site is out of access.

I'm using php and mysql, is there anybody willing to assist me to streamline mysql queries?

Answer 1

How do you know that the MySQL queries are the dominant factor in your site's performance issues? How have you measured that?

It may be that you need a different design.

For example if you're doing a query on every page refresh maybe you could use memcache and detect cases where the refresh is triggering the same query to be repeated. Then you could return the cached results (perhaps while asynchronously checking to see if any intervening updates to the backend databases necessitate cache invalidation and/or refresh).

Consider how frequently the data is changing vs. how often it's being read. In some cases you may be able to supplement your design with an extra table into which update timestamps or generation/serial numbers are posted. From there your querying engine maybe able to replace a complex query, possibly with joins and necessitating full table scans, with a cache consistency check involving a simple query of one column in one table. If a previous query returned all the automobiles you had in the database 10 minutes ago and no rows have been updated, inserted or deleted from that underlying tables or views, then you can skip the query and refer to the cached results.

Perhaps you could have your middle layer(s) perform fuzzier queries, and then winnow the data down to the more precise subset before presentation.

For example a web front-end query for automobiles under a given price in a given region might be translated into a standardized DB query for a broader range of prices in a larger region, with the application server caching the results and then filter it down to the more specific subset matching that web request. Another, similar, query might then be translated into the same standard abstract query ... and the application can thus use the cached results to avoid a call to the back-end DB.

In other words when you're asking about optimizing your queries you should also be asking if their are ways for you to generate valid, correct, results while performing fewer queries. It's better to reduce the contention on the bottleneck then it is to expend considerable effort on tuning the flow through that bottleneck.

In fact you should even question whether this data all belongs in the MySQL database or whether some of it might better be distributed across some NoSQL (couchDB or Hadoop HBase, etc). SQL databases are best used for transactional operations which impose referential integrity requirements on work. Wherever possible you should look for ways to decouple those operations from those which are more frequent and which involve large bulks of data.

Answer 2

I don't think anyone is going to give you free support in optimizing your queries, and it's not what this site is for.

However, there's extensive information about this on the internets. You should check out the mysql slow query log to find out which are your slowest queries. You can then use EXPLAIN to find out how they are performing and if they use correct indexes etc.

I can recommend the Optimizing SELECT Statements article from MySQL and the book High performance MySQL.

Good luck.