MySQL my.ini Performance and Optimization Tips

The major advantage of identifying the performance-driving factor for a database is that it allows you to avoid over-provisioning and reduce costs by right-sizing your servers. It also gives you insights into whether moving data storage or adding server capacity will improve performance and, if so, how much it will be. The tuning database for MySQL query performance optimization doesn’t come with pale challenges. However, once tuned properly, the database gives worthwhile performance results with great functionalities. It lowers…

Read More

Posted in MySQL Tagged , Comments Off on MySQL my.ini Performance and Optimization Tips
How to Restore a MySQL Database with Command Line

In MySQL, you can use the mysql command to restore the database from a dump file. mysqldump is a command-line utility used to generate a MySQL logical database backup as a single .sql file with a set of SQL statements. The utility helps you dump MySQL tables, multiple databases, or their objects. Keep in mind that it is not possible to back up MySQL databases or data to separate .sql files with the mysqldump utility Step 1: Create the database In the command prompt,…

Read More

Posted in MySQL, SQL Tagged , Comments Off on How to Restore a MySQL Database with Command Line
Illegal mix of collations (utf8_general_ci,IMPLICIT) – MySQL

— Preparations SET SESSION group_concat_max_len=4294967295; SET SESSION foreign_key_checks=0; — Set default character sets and collations ALTER DATABASE dwdata CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER DATABASE dwnotification CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER DATABASE dwsystem CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER DATABASE dwworkflowengine CHARACTER SET utf8 COLLATE utf8_general_ci; — Create statement that adjusts collations for tables in dwdata SELECT GROUP_CONCAT(CONCAT(‘ALTER TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘ CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’) SEPARATOR ‘\n’) AS “Prepared statement” FROM INFORMATION_SCHEMA.TABLES…

Read More

Posted in MySQL Tagged , , Comments Off on Illegal mix of collations (utf8_general_ci,IMPLICIT) – MySQL
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’

Searching for answers to an identical “Illegal mix of collations” error with conflicts between utf8_unicode_ci and utf8_general_ci. I found that some columns in the database were not specifically collated utf8_unicode_ci. It seems mysql implicitly collated these columns utf8_general_ci. Specifically, running a ‘SHOW CREATE TABLE table1’ query outputted something like the following: | table1 | CREATE TABLE `table1` ( `id` int(11) NOT NULL, `col1` varchar(4) CHARACTER SET utf8 NOT NULL, `col2` int(11) NOT NULL, PRIMARY KEY (`photo_id`,`tag`) ) ENGINE=InnoDB DEFAULT…

Read More

Posted in MySQL Tagged Comments Off on Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
MySQL DELETE JOIN

This quick reference introduces you a more flexible way to delete data from multiple tables by using INNER JOIN or LEFT JOIN clause in the DELETE statement. MySQL DELETE JOIN with INNER JOIN Syntax MySQL DELETE JOIN with INNER JOIN Example MySQL DELETE JOIN with LEFT JOIN Example

Read More

Posted in MySQL Tagged Comments Off on MySQL DELETE JOIN
MySQL TRIM() function

MySQL TRIM() function returns a string after removing all prefixes or suffixes from the given string. Syntax TRIM( str) Arguments Name Description BOTH Indicates that prefixes from both left and right are to be removed. LEADING Indicates that only leading prefixes are to be removed. TRAILING Indicates that only trailing prefixes are to be removed. remstr The string to be removed. FROM Keyword str The actual string from where remstr is to…

Read More

Trigger That Will Remove All Blanks Spaces From a Field

If you are importing data into a table and some of the data like a customer fields has blank spaces on it and you want to get rid of then a trigger for Insert event may a quick solution. Here is a request: “ Is it possible to create a trigger that will remove all blanks from a field? They have an check number and when we read it through Import process, it keeps putting random spaces in the…

Read More

Posted in MySQL, SQL Tagged , Comments Off on Trigger That Will Remove All Blanks Spaces From a Field
SQL SELECT TOP Clause

The SQL SELECT TOP Clause The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance. Note: Not all database systems support the SELECT TOP clause. SQL Server / MS Access Syntax SELECT TOP number|percent column_name(s) FROM table_name;   SQL SELECT TOP Equivalent in MySQL and Oracle MySQL Syntax SELECT column_name(s) FROM table_name LIMIT number; Example SELECT *…

Read More