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 »
We all know what a new line is represented in c and in PHP as: "\n" And a carriage return: "\r" In a SQL / MySQL query, when you want to add new lines or line feeds into the strings, you will need the CHAR() function of MySQL which accepts an ASCII character code as …
What are New Line Feed and Carriage Return in a MySQL query? Read More »
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 »
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 »
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 »
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 »
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 …
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 »
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 »
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 * …
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 …
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 »
A quick tip — I kind of have the feeling that I’ve covered this before, but whatever — well, just the simple SQL query below will do: CREATE TABLE temp SELECT id, COUNT(id) FROM `book` GROUP BY isbn, subject HAVING COUNT(id) > 1 Wherein each combination of column isbn and column subject in each of …
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 »
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 »
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 »
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 »
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 »
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 …
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 …
