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 ...
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 --...
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
...
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 ...
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()...
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, ...
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 ...
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 ...
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 ...
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,
...
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 ...
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) ...
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 ...
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 ...
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 ...
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 ...
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,...
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 ...
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 ...
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....
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 ...
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 ...
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 '...
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 ...
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 ...
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 ...
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 ...
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"
...
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 ...
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/...
Only top scored, non community-wiki answers of a minimum length are eligible
Related Tags
mysql × 21549innodb × 1978
performance × 1728
database-design × 1396
replication × 1348
mariadb × 1287
mysql-5.5 × 968
mysql-5.6 × 850
query-performance × 818
mysql-5.7 × 794
index × 764
join × 719
optimization × 700
mysqldump × 643
php × 547
backup × 535
myisam × 479
select × 431
trigger × 415
mysql-workbench × 406
query × 382
stored-procedures × 379
amazon-rds × 369
mysql-8.0 × 367
linux × 343