315
If you're just importing from a dump file from the CLI on *nix, e.g.
mysql -uxxx -pxxx dbname < /sqlfile.sql
then first install pipe viewer on your OS then try something like this:
pv sqlfile.sql | mysql -uxxx -pxxxx dbname
which will show a progress bar as the program runs.
It's very useful and you can also use it to get an estimate for mysqldump ...
308
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 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Answer before 2019-10-29
Note: The following is now considered a better practice (see bikeman868's answer):
CREATE ...
178
If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans!
Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All children of the parent row are killed (deleted), too. If any of these children has ...
167
Please do not just delete them in the OS.
You need to let mysqld do that for you. Here is how mysqld manages it:
The file mysql-bin.[index] keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.[index] are:
PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE '...
146
The valid syntax is close to your second try, but you need to escape the column names with backticks not with single quotes:
ALTER TABLE `blog` CHANGE COLUMN `read-more` `read_more` VARCHAR(255) NOT NULL;
113
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
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
Why these settings ?
innodb_buffer_pool_size will cache frequently read data
...
99
select * from information_schema.user_privileges;
EDIT:
As mentioned by Shlomi Noach:
It does not list database-specific, table-specific, column-specific,
routine-specific privileges. Therefore, the grant GRANT SELECT ON
mydb.* TO myuser@localhost does not show in
information_schema.user_privileges. The common_schema solution
presented above ...
97
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 referential actions:
CASCADE
RESTRICT
NO ACTION
SET NULL
SET DEFAULT
To answer the question:
CASCADE
ON DELETE CASCADE means that if the parent record is ...
83
Rather than killing the process, it would be safer if you did it within MySQL:
$ mysqladmin processlist -u root -p
Enter password:
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+-------------------+...
83
A note to expand on the answer by RolandoMySQLDBA.
The script he included is a great approach for including (and table_name in) or excluding (and table_name NOT in) a list of tables.
If you just need to exclude one or two tables, you can exclude them individually with the --ignore-table option:
mysqldump -u -p etc. --ignore-table=Database.Table1 --ignore-...
80
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, and then performing the join in the application. For example, instead of this single query:
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id = tag.id
JOIN post ...
79
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() is a function that returns the index position of a comma-delimited list if the value you are searching for exists.
IF id = 1, then FIELD(id,3,2,1,4) returns ...
76
SUGGESTION #1 : Standard Indexing
CREATE TABLE mytable
(
id int not null auto_increment,
myfield varchar(255) not null,
primary key (id),
key (myfield)
);
If you index like this, you can either look for the whole string or do left-oriented LIKE searches
SUGGESTION #2 : FULLTEXT Indexing
CREATE TABLE mytable
(
id int not null ...
72
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 difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.
71
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,
REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_NAME = 'My_Table';
You can also add (to the WHERE clause) if you have more than one ...
69
I previously addressed this issue in: Cannot open table mysql/innodb_index_stats
These tables are created for you when you install MySQL 5.6. However, upgrading from MySQL 5.5 does not invoke the creation of these tables. Here are the scripts to create them manually:
innodb_index_stats
USE mysql;
CREATE TABLE `innodb_index_stats` (
`database_name` ...
60
Is there a construct in SQL that would allow me to do something like the following:
Yes, there is, almost exactly as you wrote it. Just put col1, col2 inside parentheses:
-- works in PostgreSQL, Oracle, MySQL, DB2, HSQLDB
SELECT whatever
FROM t --- you missed the FROM
WHERE (col1, col2) --- parentheses ...
59
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 --single-transaction option is not used, and (as of MySQL
8.0.21) PROCESS if the --no-tablespaces option is not used.
Certain options might require other privileges ...
59
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 here:
mysqladmin -u root -p variables
57
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) as the encoding format for the file to import without issue.
56
Given the table description, I see
66 bytes per row of data
4 bytes per row for the primary key
7 bytes per row for country code index
3 bytes for the country
4 bytes for Clustered Key attached to the country code
Total of 77 bytes of data and keys
This does not factoring housekeeping for BTREEs or Tablespace Fragmentation
For a million rows, that would ...
55
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 the question is:
I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf
You can do this from an included option file using the option skip-log-...
53
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 unique_checks=1;
SET foreign_key_checks=1;
at the end.
Now it took 3 minutes.
(Courtesy of @andreasemer via twitter)
52
My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach.
There is one other, tricky way though that works no matter if the table has or not such a ...
51
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 some you can even write (NOT) EXISTS (SELECT 1/0 ...) and the result is the same - without any (division by zero) error, which proves that the expression there ...
51
Use a compound primary key:
CREATE TABLE yourtable
(
employeeid INT,
blahblah VARCHAR(255),
blahblah2 VARCHAR(255),
recordmonth DATE,
recordyear DATE,
PRIMARY KEY (employeeid, recordmonth, recordyear)
)
And if your table already exists, drop the old primary key:
ALTER TABLE yourtable
DROP PRIMARY KEY;
And ...
48
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 index, e.g. do a range scan on your table a using the search-key. It estimates to get 174 rows out of using that index, which is the number in rows. This step is ...
48
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 always backup your database in case something goes wrong.
Copy the following query into gen_queries.sql, replacing the 4 occurrences of YOUR_DATABASE_NAME with ...
44
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 SELECT, thus only allowing users to insert into or select from them. This option has no performance overhead, as the privilege check is a part of any statement ...
44
MySQL and PostgreSQL are quite difference performance-wise. InnoDB and PostgreSQL tables are optimized for different sorts of queries. Understanding these differences is important to understanding how to get good performance out of either.
As an example, let's look at the most obvious difference.
PostgreSQL vs MySQL/InnoDB Table Structure and What This ...
Only top voted, non community-wiki answers of a minimum length are eligible
