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.

learn more… | top users | synonyms

11
votes
6answers
24k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
11
votes
1answer
7k views

MySQL Sharding vs MySQL Cluster

Considering performance only, can a MySQL Cluster beat a custom data sharding MySQL solution? sharding = horizontal partitioning When I refer to sharding, I'm considering sharding made in the ...
11
votes
2answers
9k views

Percona vs MySQL

What is Percona? How does it differ from MySQL? When should we consider switching (or upgrading) from stock MySQL to Percona? To add some specifics in our situation we almost exclusively use ...
11
votes
2answers
3k views

Must an index cover all selected columns for it to be used for ORDER BY?

Over at SO, someone recently asked Why isn't ORDER BY using the index? The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
11
votes
3answers
16k views

MySQL table_cache and Opened_tables

I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so ...
11
votes
3answers
676 views

MySQL instance stalling “doing SYNC index”

Problem An instance of MySQL 5.6.20 running (mostly just) a database with InnoDB tables is exhibiting occasional stalls for all update operations for the duration of 1-4 minutes with all INSERT, ...
11
votes
3answers
8k views

How to convert mysql to postgresql? [closed]

I am desperately looking for a conversion tool to convert a big mysql database to postgresql. I need the tool/script to be: Free Works under Linux Simple to use and not buggy You actually tried and ...
11
votes
3answers
9k views

move data folder and drive mysql centos

i need to move the data to a different folder and drive on my cent os db server. i added new hard drive (since we ran low on space), i have a 1tb of hard drive and i have a new 2tb i want to put the ...
11
votes
3answers
1k views

Where is the INNODB Performance Leak?

I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me. The Situation I am working on a system which handles a lot ...
11
votes
1answer
9k views

Clarification on MySQL innodb_flush_method variable

Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in ...
11
votes
1answer
4k views

MySQL table with 100,000 records queried often

I have a single database of about 100 tables to store various kinds of information. The most important table is our order table which is used to store customers orders and is over 100000 records as ...
11
votes
2answers
4k views

What are the benefits of using MySQL Cluster?

I have never dipped my hands in MySQL Cluster and want to know how it can benefit me. When is it best to use it, and does it have performance benefits?
11
votes
3answers
1k views

Use MySQL to regularly do multi-way joins on 100+ GB tables?

Background: I’ve created a web application that I would like to be able to scale reasonably well. I know I'm not Google or Twitter, but my app uses a fairly large amount of data for each user and thus ...
11
votes
1answer
350 views

MySQL Commited data not seen to select query

Context: The framework used is Spring and all queries are run with JdbcTemplate. Mysql Server version is 5.6.19. The table is an InnoDB table and defaults like auto commit and isolation level ...
10
votes
3answers
4k 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.
10
votes
1answer
12k views

How is INFORMATION_SCHEMA implemented in MySQL?

The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL? When I connect to a fresh ...
10
votes
2answers
45k views

How to configure global charset on MySQL

I tried to change the charset for our MySQL via my.cnf and it failed. I added the charset settings listed below: # The MySQL server [mysqld] port = 3306 socket = ...
10
votes
5answers
13k views

Why using innodb_file_per_table?

There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
10
votes
5answers
52k views

Matching single column against multiple values without self-joining table in MySQL

We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data: user_id ...
10
votes
3answers
35k 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: ...
10
votes
5answers
3k views

Is it unwise to run replication on same physical server?

I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
10
votes
3answers
6k views

Where can I find example databases for MySQL, SQL Server and Oracle?

I want to experiment with the 3 large database management systems. For this purpose it would be cool to have the same database (the same tables, etc.) for each of those. Where can I find sql scripts ...
10
votes
3answers
9k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
10
votes
4answers
7k views

Installing default MySQL Tables in Windows (mysql_install_db)

In Linux, the default MySQL grant tables can be created with the mysql_install_db script, but that does not work on Windows. How can the default grant tables be installed on Windows? (No, I’m not ...
10
votes
3answers
6k views

MySQL data type for 128 bit integers

I need to store 128 bits unsigned integers into MySQL and I was wondering what is the best data type to store such big numbers. Right now, I'm using binary(16) but that involves a lot of conversion ...
10
votes
4answers
2k views

eCommerce orders table. Save prices, or use an audit/history table?

Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product A product can ...
10
votes
5answers
58k views

importing large SQL file phpmyadmin

I am trying to import this SQL file in phpmyadmin and got this message #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right ...
10
votes
2answers
9k views

PHP -> Mysql persistent connection pooling WITHOUT mysql_pconnect - Possible?

I have been trying to figure out a nice way of doing this for a while now. But have had a hard time finding the right pieces to do this. I am guessing this must be possible. To put it in simple terms ...
10
votes
2answers
18k views

InnoDB create table error: “Row size too large”

We have some engineers that flatten a normalized db structure into a temporary table for the purposes of generating a report. The columns are specified as TEXT NOT NULL (I know "why are they doing ...
10
votes
6answers
4k views

What could be causing strange query timeouts between PHP and MySQL?

I am the senior developer on a Software-as-a-Service application used by many different customers. Our software runs on a cluster of Apache / PHP application servers, powered by a MySQL backend. On ...
10
votes
2answers
20k views

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
10
votes
5answers
5k views

Are there any MySQL Benchmarking tools? [closed]

I've heard a long time ago that there is this tool that helps you tweek mysql settings for better performance but i can't seam to find it. I am aware that I can use ab for apache to simulate high ...
10
votes
3answers
24k views

Any better way out of MySQL InnoDB log “in the future”?

I've got this InnoDB error in MySQL 5.0. Mysqld was stopped cleanly, but I managed to lose ib_logfile0 & ib_logfile1 afterward. Now after a clean startup, InnoDB has done its "crash recovery". ...
10
votes
2answers
18k views

Should I increase max_connections in AWS RDS t1-micro for MySQL?

I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
10
votes
2answers
6k views

What does 'ibfk' stand for in MySQL?

If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
10
votes
3answers
7k views

Replicate RDS-MySQL to a non-amazon host?

I have a fairly large MySQL (5.1) database running in Amazon as an RDS instance. I'm working on migrating it out of Amazon. To do that smoothly I'd like to set up the new hardware as a read-only ...
10
votes
2answers
19k views

Why is MySQL is creating so many temporary tables on disk?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
10
votes
5answers
17k views

Alternatives to MySQL Workbench [closed]

I've been running into some problems with MySQL Workbench that I'm not sure will be able to be fixed (namely, what appears to be an old bug that may have resurfaced), so I'd like to be able to find a ...
10
votes
1answer
10k views

Error creating foreign key from MySQL Workbench

I'm trying to synchronize schema changes from MySQL Workbench to my database. I'm getting the following error when it tries to create a foreign key: Executing SQL script in server ERROR: Error 1005: ...
10
votes
2answers
6k views

PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
10
votes
3answers
2k views

Is a 5+ column primary key bad for large (100 million+) table?

I was reading about some real life DB issues, and one project had a 100 million row plus table that had 5 columns as its primary. I'm thinking this is bad, but can anyone tell me exactly why? EDIT: ...
10
votes
4answers
24k 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 ...
10
votes
2answers
18k views

What is the reason of Host 'host_name' is blocked in MySQL?

When I use incorrect password to connect to mysql server, and the test count is over the max_connect_errors, and I find the Aborted_connects is also arise as the count I test, but the host is still ...
10
votes
2answers
225 views

Find total duration of each consecutive series of rows

MySQL Version The code will run in MySQL 5.5 Background I have a table like the following one CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT , patient_id INT NOT NULL , bed_id INT NOT NULL , ...
10
votes
1answer
834 views

Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

I am trying to have a score for entire section of SELECT statements SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword') UNION SELECT ...
10
votes
1answer
680 views

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
9
votes
3answers
9k views

One Big Database vs. Several Smaller Ones

We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g., ...
9
votes
2answers
13k views

What to set innodb_buffer_pool and why..?

I have 170GB of InnoDB index and Data. I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB. So what should be the optimal ...
9
votes
3answers
9k views

Why query_cache_type is disabled by default start from MySQL 5.6?

We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6. We enabled it again and ...
9
votes
3answers
22k views

Modify DEFINER on Many Views

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result. Got error: 1449: The user ...