Simple MySQL cookbook
Posted by Steve on Tue 28 Dec 2004 at 18:16
In the course of running this site, and working with MySQL generally I've had to lookup miscellaneous documentation. Hopefully these tips will prove useful to others too.
MySQL is described by its authors as "the worlds most popular open source database", despite missing several features offered by its leading competitor PostgreSQL it is very popular and often included in cheap webhosting packages.
In Debian the database may be installed by running:
apt-get install mysql-server
The client used to access the server is also available, and may be ran interactively:
apt-get install mysql-client mysql --user=root databaseName
Other clients are available, such as the Perl DBI classes:
apt-get install libdbi-perl
Once it's up and running common tasks may be performed using the documentation available online. This small article includes a few non-obvious things which I had to lookup speciallyDetermining MySQL version
Logging Slow Queries
To determine the version of MySQL you are connected to execute the following SQL:SELECT VERSION();
For example you could run the following:skx@lappy:~$ mysql --user=root -e 'SELECT VERSION();' +---------------------+ | version() | +---------------------+ | 4.0.22_Debian-6-log | +---------------------+
Showing Server Status
If you're running a website, or web application, which is centered around a MySQL database you might wish to check that your SQL is fully optimized.
It's hard to optimize things without knowing where the current bottlenecks are, but thankfully MySQL allows you to log slow queries so you can find the bottlenecks in your application.
Edit the file /etc/mysql/my.cnf and uncomment the following lines:log-slow-queries = /var/log/mysql/mysql-slow.log
Restart mysql by running /etc/init.d/mysql restart and you will find a logfile created containing all the slow queries executed against your server.
To speedup the server I usually make sure I disable other logging, by commenting out the following line:log = /var/log/mysql/mysql.log
This stops the server from logging all the statements executed.
Resetting AutoIncrement variables
Showing server status, and statistics can be achieved by running the following SQLSHOW STATUS
Or from the command line:steve@skx:~$ mysql --user=root -e 'SHOW STATUS'
Selecting a random row
Many tables are created with an auto-incrementing value, which means that every time you insert a record it will get a unique id of some sort.
If you delete all the records in your table you'd expect this to be reset to start from 0 - but it doesn't.
To reset the autoincrement values of your table run:truncate table TABLENAME
This will cause the id to start from zero again.
Selecting Items and Counts
Selecting a random row from a table is a common task, and very simple:SELECT * FROM table ORDER BY RAND() LIMIT 1
Resizing a Column
If you have a table which contains entries such as the name of a person, and you wish to count how many times each name is listed you can use something like this:SELECT name,COUNT(*) AS Number FROM table GROUP BY name ORDER BY Number DESC, name"
This gives :+-------------------+--------+ | Name | Number | +-------------------+--------+ | skx | 15 | | Anonymous | 7 | | Paul | 7 | | Toby Smith | 6 | | Steve | 4 | +-------------------+--------+
Add a new MySQL User
If you have a table called articles which has a field called title and you wish to make this larger:ALTER TABLE articles MODIFY title VARCHAR(65);