Questions tagged [mysql]
All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.
22,406
questions
53
votes
2
answers
132k
views
selecting where two columns are in a set
This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following:
SELECT whatever WHERE col1,col2 IN ((...
- 655
53
votes
6
answers
152k
views
How to update 10 million+ rows in MySQL single table as Fast as possible?
Using MySQL 5.6 with InnoDB storage engine for most of the tables. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Server has 32GB RAM and is running Cent OS 7 x64.
I have ...
user16108
52
votes
8
answers
148k
views
Calculating disk space usage per MySQL DB
I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can ...
- 664
51
votes
10
answers
391k
views
restore table from .frm and .ibd file?
I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-...
- 613
51
votes
3
answers
184k
views
How to make MySQL table name case insensitive in Ubuntu?
I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default.
Now, I want to make MySQL work as case ...
java baba
50
votes
4
answers
348k
views
How to update one table based on another table's values on the fly?
I have a table in the name of ips as below:
CREATE TABLE `ips` (
`id` int(10) unsigned NOT NULL DEFAULT '0',
`begin_ip_num` int(11) unsigned DEFAULT NULL,
`end_ip_num` int(11) unsigned DEFAULT ...
- 3,636
50
votes
6
answers
161k
views
Why do we use Group by 1 and Group by 1,2,3 in SQL query?
In SQL queries, we do use Group by clause to apply aggregate functions.
But what is the purpose behind using numeric value instead of column
name with Group by clause? For example: Group by 1.
- 2,071
50
votes
4
answers
203k
views
How can I move a MySQL database to another drive?
I'm using MySQL 5.5 on a local machine to analyze a large amount of government data. I've created a local database that resides on my default drive (Win7 C: drive). I'd like to store the data on my E: ...
user1243473
50
votes
20
answers
262k
views
Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect
I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on?
I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL ...
- 601
49
votes
6
answers
168k
views
MySQL Set UTC time as default timestamp
How do I set a timestamp column whose default value is the current UTC time?
MySQL uses UTC_TIMESTAMP() function for UTC timestamp:
mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| ...
- 11.7k
49
votes
8
answers
97k
views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
- 3,142
49
votes
6
answers
96k
views
SSD vs HDD for databases
I am trying to purchase a new Server to run MySQL Server on. This new server will be a slave of my main machine. However, this server will be dedicated for reporting only "Lots of reads and complex ...
- 609
49
votes
6
answers
45k
views
In MySQL, does the order of the columns in a WHERE clause affect query performance?
I am having performance issues on certain database queries that have large possible result sets.
The query in question, I have three ANDs in the WHERE clause
Does the order of the clauses matter?
...
- 4,229
49
votes
9
answers
31k
views
Is there a tool like Microsoft's "SQL Server Profiler" for MySQL? [closed]
While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler.
For ...
- 1,104
49
votes
1
answer
78k
views
Dump only the Stored Procedures in MySQL
I need to dump only the stored procedures : no data, no table creation. How can I do this using mysqldump?
- 733
48
votes
7
answers
88k
views
How can I verify I'm using SSL to connect to mysql?
I have configured my server to allow SSL, and have modified my client ~/.my.cnf so I use SSL:
[client]
ssl
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=~/certs/ca-cert.pem
When I log in with my client and ...
- 1,222
48
votes
3
answers
232k
views
How to speed up queries on a large 220 million rows table (9 gig data)?
The issue:
We have a social site where members can rate each other for compatibility or matching. This user_match_ratings table contains over 220 million rows (9 gig data or almost 20 gig in indexes)....
- 641
47
votes
2
answers
78k
views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
- 1,104
46
votes
3
answers
488k
views
select rows where column contains same data in more than one record
I have a table that has a column called article_title. Let's say the table name is articles. I need to find out the records where the article_title data is the same on more than one record.
Here's ...
- 807
46
votes
5
answers
78k
views
How to estimate/predict data size and index size of a table in MySQL
I am finding what is the best way to estimate size of a table for that I have studied lot of blogs and forums but unable to find any accurate answer
For example, we have a table City with InnoDB ...
- 9,677
46
votes
5
answers
91k
views
How to SELECT from SHOW TABLE STATUS results
I'd like to limit the rows and columns that come back from the
SHOW TABLE STATUS
command in MySQL 5.1. Is there a way to get this same information through a SELECT statement so I can manipulate ...
- 825
46
votes
1
answer
38k
views
Scalability limitations of PostgreSQL and MySQL
I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB.
I suspect that limits as such do exist as one would not come up with Netezza, ...
- 749
45
votes
5
answers
48k
views
Benefits of using backtick (`) in MySQL queries?
In MySQL we can create queries with or without the backtick (`) symbol. Example:
SELECT * FROM TEST;
SELECT * FROM `TEST`;
Both works fine in mysql-console.
Is there any technical difference ...
- 683
45
votes
2
answers
114k
views
InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6
I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup
I found a possible solution here, but it does not seem official.
http://forums.mysql.com/read.php?22,...
- 1,407
45
votes
3
answers
58k
views
How do you calculate mysql max_connections variable?
How do you calculate mysql max_connections ?
What do you take into consideration ?
- 985
44
votes
4
answers
157k
views
How do I change the DEFINER of a VIEW in Mysql?
When I run mysqldump, I get an error:
mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES
This makes sense because foobar is a legacy ...
- 849
43
votes
9
answers
257k
views
MySQL DB import/export command line in Windows
How do I import and export a local instance of a MySQL database via command-line interface?
My operating system is Windows and WampServer server is installed.
- 539
43
votes
7
answers
173k
views
How to cast an integer to a boolean in a MySQL SELECT clause?
I'm new here so be kind to me. I have the following scenario:
I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value ...
- 533
43
votes
1
answer
144k
views
How long is "too long" for MySQL Connections to sleep?
I logged onto my server to check the status of the database and noticed on the SHOW PROCESSLIST; that there are a large number of connections sleeping that are very old.
What duration should the ...
- 4,229
43
votes
2
answers
57k
views
MySQL - varchar length and performance
Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?
- 533
42
votes
2
answers
20k
views
NULL or NOT NULL by default?
In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls? Or doesn't it matter?
I know in some DBMSs they ...
- 4,903
41
votes
1
answer
37k
views
Changing mysql table comment
I know that mysql table comment can be defined at the creation with:
create table (...)comment='table_comment';
And you can display comments by:
show table status where name='table_name';
How do ...
- 513
41
votes
2
answers
67k
views
Is MySql's LAST_INSERT_ID() function guaranteed to be correct?
When I do a single row INSERT to a table that has an AUTO_INCREMENT column I'd like to use the LAST_INSERT_ID() function to return the new AUTO_INCREMENT'ed value stored for that row.
As many ...
- 1,336
40
votes
6
answers
78k
views
Error Code 1117 Too many columns; MySQL column-limit on table
I have a table with 1699 columns and when I'm trying to insert more columns I get,
Error Code: 1117. Too many columns
In this table I have only 1000 rows. For me the most important thing is the ...
- 1,017
40
votes
4
answers
23k
views
mysqldump vs mysqlpump
I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? ...
- 541
40
votes
4
answers
45k
views
What is the optimal way to upgrade production RDS instance?
I have MySQL small RDS instance as part of my production system and I want to upgrade it to medium instance with provided IOPS.
As old-school DBA I'm aware about "add slave; promote to master; ...
40
votes
6
answers
70k
views
How do you tune MySQL for a heavy InnoDB workload?
Assuming a production OLTP system with predominantly InnoDB tables
What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from ...
- 1,377
40
votes
1
answer
57k
views
intermittent MySQL crashes with error "Fatal error: cannot allocate memory for the buffer pool"
Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
- 503
40
votes
4
answers
17k
views
Why does MySQL not have hash indices on MyISAM or InnoDB?
I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
Alex
39
votes
10
answers
161k
views
Get the rank of a user in a score table
I have a very simple MySQL table where I save highscores. It looks like that:
Id Name Score
So far so good. The question is: How do I get what's a users rank?
For example, I have a users ...
- 595
39
votes
4
answers
95k
views
MySQL relay log corrupted, how do I fix it? Tried but failed
A MySQL v5.1.61 relay got corrupted when the machine suddenly shut down. I tried to fix it but it didn't work.
— How do I fix it? Did I do something wrong?
As far as I've read, corrupted MySQL relay ...
- 1,209
39
votes
5
answers
6k
views
When is the right time to use MariaDB instead of MySQL, and Why?
What we earn and what we lost with this migration?
What should I expect as drawbacks after the migration?
Is it really unnecessary to change the applications in any situation?
- 2,728
39
votes
2
answers
65k
views
Multi cores and MySQL Performance
Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
- 1,271
39
votes
3
answers
15k
views
MySQL 5.1.66 SSL connection error ERROR 2026 (HY000)
UPDATE2
Using WireShark I found out the problem string (I hope I did):
28 | 9.582638 | 192.168.18.128 | 192.168.18.129 | MySQL Response Error 1043
And the error is (according to docs):
Error: 1043 ...
- 440
38
votes
5
answers
208k
views
MySQL: How to create Column if not exists?
I am trying to create a Column for my table only if it does not exist.
I have researched a lot but I could not find any solution yet.
Is this really possible to conditionally create Column ?
- 483
38
votes
3
answers
45k
views
For absolute performance, is SUM faster or COUNT?
This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.
I tend to prefer
COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)
However, this is ...
- 4,330
38
votes
5
answers
57k
views
Why are simple SELECTs on InnoDB 100x slower than on MyISAM?
I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy.
I copied (description follows) ...
- 563
37
votes
4
answers
84k
views
mysql dump import incredibly slow on my developer's machine
I have an SQL dump, it's pretty big (411 MB) and it took 10 minutes to import on server A, the same import on my workstation B has an estimate (pipeviewer) of 8 hours to import (it imported 31 MB in ...
- 1,203
37
votes
2
answers
100k
views
What is the difference between OPTIMIZE TABLE and ANALYZE TABLE table in MySQL
What is the difference between OPTIMIZE TABLE and ANALYZE TABLE table in MySQL? I have read the online documentation but not sure what the difference is.
- 611
37
votes
7
answers
53k
views
What are reasons **NOT** to use the MEMORY storage engine in MySQL?
I recently discovered that MySQL has a "memory" engine that I wasn't aware of (most of my database work is for hobby projects so I learn what I need as I go). It seems like this option should give me ...
- 789