Hello,
Am not sure if this question makes sense. But I know all the basic CRUD commands of mysql. Probably a bit more here and there (foreign keys etc). But there are so many books written on mysql/dbms. I can write decent queries and get all my results as I want them. Maybe they aren't the most efficient but it worksforme. Thats because my apps arent facebook as yet that I have to worry about optimization. Or do I?
Am I missing the point here? What else should I know?
Thank you very much.
If you know enough to to do what needs to be done, then you know enough for now. However, Introduction to Database Systems by C.J. Date is an outstanding discussion of relational databases written by a pioneer in the field (as well as other types of database systems).
You should worry about optimization anyway imho. Sure if right now you have only 10 people who are using your application it's not the issue, but in the future if the user base grows it can really be a "pain in the ass" to rewrite database structure, especialy if in your code you are using the raw queries without database abstraction.
Advanced MySQL is probably not just about writing queries for CRUD operations. Sometimes you need to do optimizations or various maintenance procedures that do require an intimate knowledge of the DBMS you are working with. You might not be worried about the performance of your queries and the robustness and efficiency of your database design if you deal with small to medium sized applications, but for a highly scalable application all these are factors that you have to take into account.
It's good to know CRUD syntax well. I would recommend that you go beyond that to understand relational design, primary and candidate keys, indexing, etc. These are topics that are meaningful for all relational databases, not just MySQL.
Generally, there are tons of features of mysql and other RDBMS systems that people aren't aware of. This is fine since you can get by well with a subset of features, but to deal with difficult problems or to be a good DBA there's a huge amount of stuff to learn. When people talk about Advanced X on databases, these are the types of things that are meant.
To answer your question, it's never a bad idea to find out what other features and tools are out there. You may find much better ways of solving your own problems and develop a better set of skills for solving other people's problems. I also fully agree with the other answers suggesting that you improve your knowledge on higher level topics, knowing how to make good db designs is extremely important.
Premature optimization is the root of all evil. Focus on designing proper and logical database structures and indexing them correctly, that will take you far. Modifying a badly written query is always easier than modifying a badly designed database structure.
In my opinion, use the queries you have and optimize them when there is a need for optimization. What comes to the queries, rather focus on making them secure (see sql injection).
Databases are much more than just "Places to put stuff". Once you realize that, you will start using them to their full potential.
Oh, now I get it (the question). I thought you meant "What is the point of combining 'Advanced' and 'MySQL'?". :-)
If you MUST use MySQL for your job, then yes, you had better gradually get a deeper understanding of it, especially what the shortcomings and gotchas are, and how other people at work may make assumptions about things that are not really going to work out.
Now, my "troll": if this is just you, use something a bit more robust. I'm no MicroSoft fan, but they do make the startup costs for using SQL Server pretty low, assuming you are working on Windows. Better yet, if you are working on a *nix server, you might try PostgreSQL. They have been pretty serious about correctly implementing quaint little things like ROLLBACK, transaction isolation, foreign key referential integrity, views, functions (aka stored procedures) for quite a few years. MySQL has improved over the years, but is still (IMHO) somewhat immature. My impression of it back around 2000 was "the reliability of xBASE with the ease of the SQL interface". (I'm not a big fan of the SQL language itself -- maybe I'm just too old to really warm up to it as the "the only possible way to do it")
What else should I know?
Greater understanding of relational theory, so you write better SQL. I'm currently enjoying "SQL and Relational Theory," a new book by C. J. Date, the world's leading expert on the relational model.
Implementing and monitoring security - SQL injection certainly, but other issues covered by OWASP, SANS.org, or books like "19 Deadly Sins of Software Security." This is a broad topic not specific to SQL, but I think it's every software developer's duty to learn this stuff.
Performance measurement and monitoring - how will you know when you reach the point where you do need to learn optimization techniques?
Database maintenance and recovery - backups, repair.
Replication, clustering, and proxying.
Deployment and upgrade techniques - how to apply changes to a running application or site without interrupting service.
Writing more-or-less portable SQL that works with multiple RDBMS brands. At least understand what needs to be rewritten if you need to support another brand.
How and when to employ Object-Relational Mapping frameworks.
How and when to employ non-relational databases. SQL is the best general-purpose data management paradigm, but there are other technologies more specialized to specific tasks.
I avoid MySQL like a bad flu bug, but I can add some insight here. One area that would be covered in the "advanced" portion of MySQL is customization.
There is alot involved with SQL in general that MySQL doesn't do out of the box, or can be customized. Our company uses full stored procedure implementation and geospatial queries as two examples.
Advanced would involved good customization skills, and experience adding and working with customizations or add-ins. Anything to make MySQL be more enterprise like.