355
votes
Accepted
Create a MySQL database with charset UTF-8
Update in 2019-10-29
As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice:
CREATE DATABASE mydatabase ...
- 3,704
215
votes
'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
use --no-tablespaces
see mysqldump-Documentation
mysqldump requires at least the SELECT privilege for dumped tables,
SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES
if the --...
- 2,251
148
votes
Accepted
MySQL any way to import a huge (32 GB) sql dump faster?
Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB
You definitely need to change the following
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
...
- 174k
128
votes
Accepted
Difference between On Delete Cascade & On Update Cascade in mysql
A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here.
In the SQL 2003 standard there are 5 different ...
- 27.5k
96
votes
Accepted
How does ORDER BY FIELD() in MySQL work internally
For the record
SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4);
should work as well because you do not have to order the list in the WHERE clause
As for how it works,
FIELD()...
- 174k
92
votes
What is faster, one big query or many small queries?
What would address your question is the subject JOIN DECOMPOSITION.
According to Page 209 of the Book
You can decompose a join by running multiple single-table queries instead of a multitable join, ...
- 174k
90
votes
'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
tl;dr Breaking change introduced in minor MySQL update, use --no-tablespaces option in mysqldump from now on (recommended) or add the global PROCESS privilege to the user running the command.
I ...
- 1,001
80
votes
Create a MySQL database with charset UTF-8
You should use:
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Note that utf8_general_ci is no longer recommended best practice. See the related Q & A:
What's the ...
- 900
78
votes
Accepted
Disable MySQL binary logging with log_bin variable
This is an old question, but it came up in a search while I was trying to jog my memory about the correct option name and so now that I've figured it out I'm adding the details here.
The key part of ...
- 906
77
votes
Accepted
How to check foreign keys related to a table
Firstly, find out your FOREIGN KEY constraint name in this way:
SELECT
TABLE_NAME,
COLUMN_NAME,
CONSTRAINT_NAME, -- <<-- the one you want!
REFERENCED_TABLE_NAME,
...
- 27.5k
66
votes
Accepted
What is the meaning of filtered in MySQL explain?
To filter here means to apply a condition on a set of rows that were selected by a type-search as potential rows, and to only keep rows that fulfill the condition:
MySQL will first try to use an ...
- 1,051
64
votes
Cannot locally import UTF-8 encoded SQL database on Mac
Based on this blog post
http://ilikekillnerds.com/2014/08/fixing-sequel-pro-sql-encoding-error-for-imported-sql-files/
When importing your database dump, you need to select Western (Mac OS Roman) ...
- 741
61
votes
mysql dump import incredibly slow on my developer's machine
This answer speeded up everything a lot:
https://stackoverflow.com/a/2167641/292408
I simply
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
at the beginning, and
COMMIT;
SET ...
- 1,083
61
votes
How to make MySQL table name case insensitive in Ubuntu?
Open terminal and edit /etc/mysql/my.cnf
sudo nano /etc/mysql/my.cnf
Underneath the [mysqld] section.add:
lower_case_table_names = 1
Restart mysql
sudo /etc/init.d/mysql restart
Then check it ...
- 611
60
votes
How to easily convert utf8 tables to utf8mb4 in MySQL 5.5
This solution will generate and then run queries needed to convert databases, tables and columns. It converts all columns of the type varchar, text, tinytext, mediumtext, longtext, char.
You should ...
- 701
56
votes
Accepted
EXISTS (SELECT 1 ...) vs EXISTS (SELECT * ...) One or the other?
No, there is no difference in efficiency between (NOT) EXISTS (SELECT 1 ...) and (NOT) EXISTS (SELECT * ...) in all major DBMS. I've often seen (NOT) EXISTS (SELECT NULL ...) being used as well.
In ...
- 93.4k
50
votes
Accepted
Query to compare the structure of two tables in MySQL
TWO TABLES IN THE CURRENT DATABASE
If you want to know if two tables are different, run this
SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
SELECT
column_name,...
- 174k
48
votes
Accepted
MySQL - varchar length and performance
This is a very common "exam/interview question". I will answer as good as I can:
In the standard row formats for InnoDB and MyISAM (dynamic/compact) a VARCHAR(50) and a VARCHAR(255) will store the ...
- 14.2k
46
votes
MySQL relay log corrupted, how do I fix it? Tried but failed
Error: Last_SQL_Errno: 1594
Last_SQL_Error: Relay log read failure: Could not parse relay log event entry.
This error message means that the copy of the MySQL replication log could not be read on ...
- 561
45
votes
Accepted
MySQL Count rows from another table for each record in table
You are just missing the GROUP BY
YOUR QUERY WITH GROUP BY
SELECT
student.StudentID,
student.`Name`,
COUNT(attendance.AttendanceID) AS Total
FROM
student
LEFT JOIN attendance ON student....
- 174k
44
votes
Accepted
How do I enforce a write-once then read only database table in SQL?
I see at least two ways of accomplishing this. The first approach is to not grant DELETE and UPDATE privileges on these write-once tables, or, for that matter, any privileges apart from INSERT and ...
- 22k
43
votes
Accepted
How can I mysqldump all databases except the mysql schema?
You need to collect all the database names into a space delimited list. Use that for mysqldump
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all ...
- 174k
41
votes
Accepted
mysql Lock wait timeout exceeded; try restarting transaction
You can set variable innodb_lock_wait_timeout=100 for lock time to 100 sec.
mysql> set innodb_lock_wait_timeout=100;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like '...
- 761
41
votes
What are the main differences between InnoDB and MyISAM?
A bit late to the game...but here's a quite comprehensive post I wrote a few months back, detailing the major differences between MYISAM and InnoDB. Grab a cuppa (and maybe a biscuit), and enjoy.
The ...
- 893
40
votes
What is faster, one big query or many small queries?
In Postgres (and probably any RDBMS to a similar extent, MySQL to a lesser extent), fewer queries are almost always much faster.
The overhead of parsing and planning multiple queries is already more ...
- 154k
39
votes
Why do we use Group by 1 and Group by 1,2,3 in SQL query?
This is actually a really bad thing to do IMHO, and it's not supported in most other database platforms.
The reasons people do it:
they're lazy - I don't know why people think their productivity is ...
- 177k
38
votes
Why query_cache_type is disabled by default start from MySQL 5.6?
You need the history of InnoDB to understand why. Here it goes:
WAR STORY
InnoDB and the query cache are in a constant state of war. InnoDB tends to be very heavy-handed when inspecting changes in the ...
- 174k
37
votes
Accepted
Why is the default page size for databases so small?
I'm running Linux (Fedora 34, 64 bit, two cores, four CPUs, 32 GB RAM - PostgreSQL 13.3).
If I run stat -f some_random_file as follows:
[pol@fedora inst]$ stat -f blah.txt
File: "blah.txt"
...
- 27.5k
36
votes
Accepted
MySQL - Difference between using count(*) and information_schema.tables for counting rows
There are various ways to "count" rows in a table. What is best depends on the requirements (accuracy of the count, how often is performed, whether we need count of the whole table or with variable ...
- 93.4k
35
votes
Accepted
How do I resolve this error, "ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'"?
Simply run mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
Enter password:
Warning: Unable to load '/usr/share/...
- 56.7k
Only top scored, non community-wiki answers of a minimum length are eligible