Posts tagged MySQL

News – 4000% Performance Increase by Sorting in PHP vs MySQL

0

A buddy at work sent out the following link which proved to be an interesting read and should be read by any developer supporting a high traffic website.

http://highscalability.com/blog/2010/3/23/digg-4000-performance-increase-by-sorting-in-php-rather-than.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+HighScalability+%28High+Scalability%29

MySQL – INSERT SELECT – Setting Constant Value

2

I had a situation arise where I needed to insert data into a linking table where one column was made up of ids from one table, but the other column was set to a default value (1). Never did this type of query before so had a bit of a struggle with it, but finally figured it out.


INSERT INTO linking_book_category (book_id, category_id) SELECT book.id, 1 FROM books AS book;

Notice the 1 after SELECT book.id.

MySQL — Show InnoDB Status

0

This morning I had an issue where my transactions weren’t being committed and needed to figure out why. When Googling for a way to see what transactions were conducted, but not active I came across a great ‘Show INNODB Status’ post from MySQL Performance Blog.

If you have the correct admin privileges; log into your MySQL DB server and type the following:

show innodb status

You will be presented a wealth of information, including transaction history (or snapshot if there are many). The aforementioned link will give you a section by section explanation, which proved to be quite helpful in debugging my specific issue.

MySQL Common Queries

0

Came across an awesome site where the author posts queries for a lot of common data retrieval scenarios. Definitely worth a look if hazy on more complex types of queries.

Check it out @ www.artfulsoftware.com

MySQL Add Columns

0

Add a column with no regard for order of entry among other columns:

ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0;

Add a column, but specify that it should be the first column of the table:

ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0 FIRST;

Add a column, but specify it's location other than first:

ALTER TABLE `table_name` ADD COLUMN `column_name` int(11) unsigned not null default 0 AFTER `some_other_column`;

MySQL Tuning Primer Script

0

Suggested from #MySQL on irc.freenode.net, this appears to be a very useful utility for performance tuning your MySQL database. I haven’t used it yet, but plan too on the next project.

MySQL Tuning Primer Script

MySQL – Import data from .sql file

0

The following command will import schema and/or data from a mysqldump file:

mysql -h <host_name> <db_name> -u <username> -p < <file_name>.sql

MySQL – Resetting auto_increment counts

0

There may be times where you need to reset the auto_increment counter of a MySQL table, here’s how:

ALTER TABLE <table_name> AUTO_INCREMENT = 1;

MySQL Database Dump – mysqldump

0

MySQL provides a way to get a snapshot of your schema via mysqldump. Care needs to be taken though, if your tables are large you are better off with a SELECT INTO OUTFILE, or implement replication to a low traffic server because the mysqldump command will lock your tables for as long as it takes to grab the data.

mysqldump -u <user_name> -h <db_host> --opt <database_name> -d --single-transaction > backup.sql

The -d flag tells mysqldump that you don’t want any data, only the schema.

If you are only dumping schema and you wish to remove the auto_increment references from the resulting file, there is a how-to here: http://melikedev.com/2011/06/01/mysql-remove-auto_increment-from-schema-dumps-mysqldump/

Mysql – Granting permissions via Command Line Interface

0

Needed to do this to setup a MySQL server on my local machine to facilitate unit test testing…  I used XAMPP and started the MySQL app. After it starts open your terminal and type the following:

mysql -u root
mysql: GRANT ALL ON *.* TO '<some_user>'@'%' IDENTIFIED BY '<some_password>'
mysql: FLUSH PRIVILEGES;
mysql: EXIT
mysql -u <some_user> -p<some_password>
mysql: exit

You should now be able to connect to your local mysql server from any host using the supplied name and password.

Go to Top