(My)SQL Basics and much more

I've been using MySQL for quite some time now. Most of that time I used it with PHP, for Joo开发者_如何学Gomla development. Up until now, I didn't pay very much attention to optimization, since I was usually asked to finish stuff ASAP.

Now, while I know that ASAP factor is a reality, I would like to improve my knowledge of relational DBs, together with good introspection to query and db optimization. I'm planning to start working with some rather large dbs, for which my usual approach will not be possible.

Any recommendations for some good books from the area?

Thx in advance.

Joe Celko's SQL for smarties, 4th ed.

The Art Of SQL

Refactoring SQL Applications

I would not recommend you to devote yourself to MySQL only. Instead, if you can, try to gain some experience with other DBMSs, where the advanced optimizers make your job easier.

If using linux shell I recommend mtop application to watch what is happening.

In mysql configuration you can specify logging slow queries: http://www.webdevelopmentstuff.com/112/optimizing-mysql-log-slow-queries.html

There also is a parameter that defines what is a long query. Set it to 0 when desperate :) I once had when debugging a CMS that kept sending thousands of requests that took 0.00001s each.

I also found this: http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

And I recommend a bit of reading on indexes.

For php+mysql with slow-queries log it's also useful to know Apache Bench command: ab -c10 -n50 http://... calls the adress 50 times with up to 10 concurrent request.

That's just a list of tips. It's not complete in any way.