Fastest way to become a MySQL expert?

I have been using MySQL for years, mainly on smaller projects until the last year or so. I'm not sure if it's the nature of the language or my lack of real tutorials that gives me the feeling of being unsure if what I'm writing is the proper way for optimization purposes and scaling purposes.

While self-taught in PHP I'm very sure of myself and the co开发者_运维百科de I write, easily can compare it to others and so on.

With MySQL, I'm not sure whether (and in what cases) an INNER JOIN or LEFT JOIN should be used, nor am I aware of the large amount of functionality that it has. While I've written code for databases that handled tens of millions of records, I don't know if it's optimum. I often find that a small tweak will make a query take less than 1/10 of the original time... but how do I know that my current query isn't also slow?

I would like to become completely confident in this field in the ability to optimize databases and be scalable. Use is not a problem -- I use it on a daily basis in a number of different ways.

So, the question is, what's the path? Reading a book? Website/tutorials? Recommendations?

EXPLAIN is your friend for one. If you learn to use this tool, you should be able to optimize your queries very effectively.

• Scan the the MySQL manual and read Paul DuBois' MySQL book.
• Use EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS and SHOW PROCESSLIST.
• Learn how the query optimizer works.
• Optimize your table formats.
• Maintain your tables (myisamchk, CHECK TABLE, OPTIMIZE TABLE).
• Use MySQL extensions to get things done faster.
• Write a MySQL UDF function if you notice that you would need some function in many places.
• Don't use GRANT on table level or column level if you don't really need it.

http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html

The only way to become an expert in something is experience and that usually takes time. And a good mentor(s) that are better than you to teach you what you are missing. The problem is you don't know what you don't know.

Research and experience - if you don't have the projects to warrant the research, make them. Make three tables with related data and make up scenarios.

E.g.

Make a table of movies their data

make a table of user

make a table of ratings for users

spend time learning how joins work, how to get movies of a particular rating range in one query, how to search the movies table ( like, regex) - as mentioned, use explain to see how different things affect speed. Make a day of it; I guarantee your handle on it will be greatly increased.

If you're still struggling for case-scenarios, start looking here on SO for questions and try out those scenarios yourself.

I don't know if MIT open courseware has anything about databases... Well whaddya know? They do: http://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/6-830Fall-2005/CourseHome/

I would recommend that as one source based only on MITs reputation. If you can take a formal course from a university you may find that helpful. Also a good understanding of the fundamental discrete mathematics/logic certainly would do no harm.

As others have said, time and practice is the only real approach.

More practically, I found that EXPLAIN worked wonders for me personally. Learning to read the output of that was probably the biggest single leap I made in being able to write efficient queries.

The second thing I found really helpful was SQL Tuning by Dan Tow, which describes a fairly formal methodology for extracting performance. It's a bit involved, but works well in lots of situations. And if nothing else, it will give you a much better understanding of the way joins are processed.

Start with a class like this one: https://www.udemy.com/sql-mysql-databases/

Then use what you've learned to create and manage a number of SQL databases and run queries. Getting to the expert level is really about practice. But of course you need to learn the pieces before you can practice.