SQL / MySQL Tips and Tutorials

MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons

The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance, InnoDB is newer while MyISAM is older. InnoDB is more complex while MyISAM is simpler. InnoDB is more strict in data integrity while MyISAM is loose. InnoDB implements row-level lock for inserting …

MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons Read More »

MySQL: Count the Number of Records and Update the Stats Field in Another Table

I wrote this before in 2 separate posts. Actually, here’s a much better idea: use a sub query. For example, you have 2 tables, Table poets – Columns: id, poet, nation Table nations – Columns: id, nation, count Basically, nations to poets has a mapping of one to many, naturally. For example, there are 1000 …

MySQL: Count the Number of Records and Update the Stats Field in Another Table Read More »

Instantly Boost SQL Query Efficiency of REGEXP or RLIKE by 2000%

Naturally, using regular expressions for SELECT queries to check if certain text strings or text patterns are residing somewhere in large chunks of data is the most resource-intensive option and thus your last option. However it’s somehow unavoidable to practice regular expressions in the SQL queries for complicated patterns. For example, word boundaries are a …

Instantly Boost SQL Query Efficiency of REGEXP or RLIKE by 2000% Read More »

mysql command line character set option for importing SQL files encoded in UTF8

For languages other than English, especially those in Asia such as Chinese, each character takes 2 bytes in storage and needs to be encoded in UTF8 or other encoding specifically designed for it. Normally, the command line mysql database importing command has no problem with English database SQL files that are encoded in ANSI or …

mysql command line character set option for importing SQL files encoded in UTF8 Read More »

How to Recover or Reset MySQL root Password after You Forgot and Lost It

MySQL has come with a safe mode wherein access privileges are not checked, which essentially enables you to log in anonymously to change anything in any database. And we are going to get in this safe mode to reset the lost MySQL root password. First, you need to stop the current MySQL daemon by: /etc/init.d/mysql …

How to Recover or Reset MySQL root Password after You Forgot and Lost It Read More »

Best MySQL Books to Learn MySQL Database Programming and Development (+ PHP Applications)

MySQL is by far the most popular and widespread database on the entire planet. It’s used in a wide array of industries and the Web is probably the most prominent scenario, websites database. Below are some hand selected books for you to learn MySQL: both database administration and database SQL programming, for both professionals and …

Best MySQL Books to Learn MySQL Database Programming and Development (+ PHP Applications) Read More »

How to Transfer / Move WordPress Blog from One Domain to Another

Basically, the root URL of your blog which is your domain or subdomain is changing here. Though this doesn’t seem like a sane decision considering the huge SEO impact that might fall upon your site but with HTTP 301 permanent redirection, this can still make sense in some situations. Assume you won’t change the details …

How to Transfer / Move WordPress Blog from One Domain to Another Read More »

MySQL: String Function to Replace Substring and Change Part of the Field Value

A typical case of replacing a part of the string to another substring in a database table is switching between site domains or URLs, such as when you want to transfer your WordPress blog from one domain to another. You have to change all values containing the original site URL to the new one in …

MySQL: String Function to Replace Substring and Change Part of the Field Value Read More »

PHP, MySQL: Calculate Number of Days between 2 Dates or Number of Hours, Minutes and Seconds

This is a rather common task in both PHP and MySQL, that is, to calculate and get the days distance (number of days) between 2 given dates. In PHP, to get the number of days between now and a future date / a past date: $days = abs(strtotime(“2011-11-20”) – strtotime(date(“Y-m-d”))) / (60 * 60 * …

PHP, MySQL: Calculate Number of Days between 2 Dates or Number of Hours, Minutes and Seconds Read More »

MySQL, PHP: Display MySQL table fields and data

While this may seem simple, but it’s more complicated than it may appear because you have to first decide which data (rows, fields, etc.) to be extracted or selected and in what way the data from the MySQL table are presented. But to simply read from a MySQL table for the fields and data: SELECT …

MySQL, PHP: Display MySQL table fields and data Read More »

MySQL, PHP: Store form textarea value or data to MySQL database table

To send the textarea text value in any HTML forms to the web server and store it into a MySQL table by PHP, you will need to POST the textarea data to a PHP script and then the PHP script would connect to a MySQL database to put the data into one of the tables …

MySQL, PHP: Store form textarea value or data to MySQL database table Read More »

MySQL: Huge Table Not Responding after Adding Index

Today I’m optimizing some MySQL tables with large number of records — 1 million of them, yeah, I know — , it’s simply impossible to deal with such big chunks of data without proper indexing. So there I was, adding a variety of indexes to a few of the columns. It’s expected it’d take a …

MySQL: Huge Table Not Responding after Adding Index Read More »

MySQL: Counting Number of Records or Rows by a Foreign Column (from Another Table)

Well, MySQL doesn’t actually have anything called Foreign Key, it’s just my way of saying a alien column from another table. Let’s say 2 tables are related, 1 is books and the other is subjects. Any book in book table falls into one of the subjects, thus having a subject column referencing the ID of …

MySQL: Counting Number of Records or Rows by a Foreign Column (from Another Table) Read More »

MySQL: Update Multiple Rows or Records with One Single Query

As MySQL doesn’t have inherent support for updating more than one rows or records with a single update query as it does for insert query, in a situation which needs us to perform updating to tens of thousands or even millions of records, one update query for each row seems to be too much. Reducing …

MySQL: Update Multiple Rows or Records with One Single Query Read More »

MySQL: Replace Substring with Another String – the MySQL String Replace Function

To find / identify a part of an original string and replace it with another string in a SQL query with MySQL, use REPLACE(), the string replace function. For example, you may want to replace all spaces in one of the varchar or text fields to a single dash: UPDATE `table` SET `old_field` = REPLACE(`old_field`, …

MySQL: Replace Substring with Another String – the MySQL String Replace Function Read More »

MySQL: Deleting Duplicate Rows or Records | Selecting Unique Rows into A New Table

One of the principles of data integrity is to reduce data redundancy as much as possible and wipe out any duplicate database table entries. Another reason for this is that you need to add unique indexing to one or a group of table columns and you need the one column or combination of columns to …

MySQL: Deleting Duplicate Rows or Records | Selecting Unique Rows into A New Table Read More »

1 tip about ALTER TABLE xxx ADD INDEX yyy

Proper indexing can work wonders for large databases in query efficiency, it’s a compromise of disk storage in exchange for sorting speed. For databases with millions of records, indexing takes considerable amount of disk storage, and, AGONIZING lengths of time to create. I recently compiled a database with one table mytable consisting of over 4 …

1 tip about ALTER TABLE xxx ADD INDEX yyy Read More »

MySQL: INSERT INTO … SELECT … FROM … Selectively on Table Columns / Fields to Combine Multiple Tables into One

While you can always fulfill tasks such as creating a new table out of existing ones in MySQL by the help of PHP, it’d be much faster and way more efficient with native SQL queries. Consider when you need to combine a few tables into one, more precisely, you need just certain fields / columns …

MySQL: INSERT INTO … SELECT … FROM … Selectively on Table Columns / Fields to Combine Multiple Tables into One Read More »

Scroll to Top