MySQL Skills

[Joseph Witchard]

What exactly is there to know/you should know about MySQL, other than storing data and using a server-side language to interact with it?

[stephan.gerlach]

That's all MySQL is doing. storing and retrieving data. But knowing how to store and retrieve data the most efficient way is a skill that needs to be learned.

A bad designed MySQL database can slow the website down as badly written queries.

[chazzy]

That's all MySQL is doing. storing and retrieving data.

That's only part of what mysql is capable of.

[Joseph Witchard]

That's only part of what mysql is capable of.

Is there anything else that's crucial to know about MySQL, or will storing and retrieving data be what you need 99% of the time?

[Four Staples]

MySQL is a database, plain and simple. It holds data, thus you inevitably will be doing a lot of storing data and retrieving data. Most everything it does can be boiled down to the storing, organizing, and retrieving of data.

As Stephan mentioned, figuring out how best to store data is a trick you learn with time. You don't want to store too much redundant information, in case it changes in once instance, but you don't want too little redundancy or your site could slow down due to the extra queries.

[b4web]

Joseph - If someone like a DataBase Administrator is taking care of the database, then you probably don't need to worry about the backup, recovery and some of the database functions. You should probably have some understanding of relational databases so that you will understand how to set up your tables. Referential integrity and how to use foreign keys are important. It would be nice to be familiar with major functions of your application and how they may be helped by perhaps adding an index to your table. There are capabilities within the database, such as, procedures and triggers that are stored on the database that you may find more efficient to use than other methods. I can't think of anything else right now.

[talldean]

What exactly is there to know/you should know about MySQL, other than storing data and using a server-side language to interact with it?

Well, I guess it falls into two categories:
1. SQL - how to design the data model, and how to write the most efficient queries to return information stored in that model by performance tuning of queries.
2. Administration - how to setup the server, allocate disk storage, create databases, create user accounts, and perhaps performance tuning of the server.

[Inferno_str1ke]

I guess a related field is in caching the data - its not directly using MySQL but its important that you can cache commonly used data that doesn't change often to remove unnecessary database calls and speed up your site.

[svidgen]

Learn about a few different storage engines: InnoDB, MyISAM, and Memory are good to know about. Learn how indexing works on these engines. Learn about the different types of indexes and get creative with what you can do with those. Learn about Views and Stored Procedures. Take a look at the list of String/Numeric/Date/Time functions that are available, as well as the general logic and math functions and operators available.

Then ... read up on efficiency. Read up on benchmarks to see which schemas work best for what and which things are better to do in the DBMS and which are best left to the application. I would recommend getting a book on the matter, like High Performance MySQL, 2nd ed. (http://oreilly.com/catalog/9780596101718/?CMP=AFC-ak_book&ATT=High+Performance+MySQL,+Second+Edition,).

DBMS's in general can be used to create some neat effects, limited primarily by our creativity. For instance, you can use UNIQUE constraints across particular fields to "filter" data in clever ways. Performing this in the DBMS rather than the application allows these filters to operate much more efficiently, since they can take advantage of indexes and avoid the overhead of being passed between processes first--not to mention MySQL/your DBMS is already optimized for this type of thing.

Additionally, since you can create your own procedures, you can dump off a great deal (if not all) of your application logic into the database. With the clever use of triggers, procedures, and constraints, multiple SQL clients can interact with the database as if it were an application server.

If we were to consider a web environment specifically, a DBMS can stand in as a sort of shared memory, rather than as a long-term data store. Through the use of temporary tables, multiple scripts and apps written in a variety of languages can share common variables while maintaining data integrity and security at a low cost. So, in this respect, you can think of your DBMS as a password protected, lock-controlled, and network-shared extension of the RAM available to your applications and scripts.

And that "RAM" can be indexed ...

Thinking of the database in that sense allows you to use the DBMS for "messaging" between instances of an application, or even entirely different applications when real-time messaging is unnecessary or impossible.


Sorry for the long-windedness ... MySQL is about managing data (not just storing). But even the storage aspects of MySQL (or any DBMS) are very intricate, and knowing those intricacies can be very helpful.