MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

16
votes
3answers
5k views

How Does Table Partitioning Help?

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
16
votes
5answers
8k 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? ...
16
votes
1answer
4k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
16
votes
6answers
49k 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 ...
16
votes
2answers
30k views

What is the difference between MySQL VARCHAR and TEXT data types?

After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types? I was reading the list of ...
16
votes
2answers
2k views

Primary key index with a DATETIME as first part of the compound key is never used

I have a problem with INDEXING a DATETIME (or even a date) as first part of my PRIMARY KEY. I use MySQL 5.5 Here are my two tables: -- This is my standard table with dateDim as a dateTime CREATE ...
15
votes
6answers
17k 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 ...
15
votes
6answers
283 views

Securing DB passwords

Looking at the structure of most PHP/MySQL-based websites I've seen, it appears that it's not terribly difficult to discern the database password if you dig a bit, as there's invariably a setup or ...
15
votes
7answers
42k views

Need to migrate SQL Server to MySQL

I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the ...
15
votes
1answer
30k 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 ...
15
votes
3answers
6k views

Is it common practice to mix InnoDB and MyISAM tables on same server?

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
15
votes
4answers
3k views

How to create a conditional index on MySQL?

How to create an index to filter a specific range? AFAIK it's impossible to create directly but I think it's possible simulate this feature. Example: I wanna create an index for NAME column just to ...
15
votes
2answers
17k views

MySQL: Create index If not exists

Is there a way to create an index in MySQL if it does not exist? MySQL does not support the obvious format: CREATE INDEX IF NOT EXISTS index_name ON table(column) ERROR 1064 (42000): You have an ...
15
votes
1answer
4k 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: ...
14
votes
4answers
44k 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 ...
14
votes
3answers
11k 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 ...
14
votes
5answers
8k views

How do I force one record to have a true value for a boolean column, and all others a false value?

I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table. Basically, I want to guarantee that this query will always ...
14
votes
1answer
19k views

INT(5) vs SMALLINT(5)

In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?
14
votes
3answers
3k views

Why doesn't InnoDB store the row count?

Everyone knows that, in tables that use InnoDB as engine, queries like SELECT COUNT(*) FROM mytable are very inexact and very slow, especially when the table gets bigger and there are constant row ...
14
votes
4answers
21k views

How do you identify InnoDB table corruption?

I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
14
votes
3answers
7k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
14
votes
2answers
16k views

What is the recommended way to backup a MySQL/Amazon RDS database to S3?

I have two purposes for this: To have an offsite backup in case of a region wide problem with Amazon Web Services. To copy production data from a production billing account to a beta billing ...
14
votes
2answers
19k views

How can I change the default storage engine in phpmyadmin?

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
14
votes
4answers
45k views

Access denied for user 'root'@'%'

I used to access the root user in MySQL just fine. But recently, I am no longer able to. I am able to login fine : mysql -u root -p Here is the mysql status after login : mysql> status ...
14
votes
1answer
406 views

Does anyone have a good recommendation for a book which teaches sharding?

Does anyone have a good recommendation for a book which teaches db sharding (from scratch at best) I have read like 40 different websites talking about sharding. I'm not saying online sites/blogs ...
13
votes
7answers
21k 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 ...
13
votes
3answers
5k views

Why does importing a 12 GB .sql file take more than 36 hours?

I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB. Considering the ...
13
votes
2answers
3k views

Rows missing after online conversion from MyISAM to InnoDB

We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down. Now that the ...
13
votes
7answers
21k 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 ...
13
votes
2answers
3k views

Can I create index on a table in a MySQL database while in use

And if I can, any reason (performance/stability) why I shouldn't?
13
votes
5answers
14k views

Why do MySQL threads often show “freeing items” status when the query cache is disabled?

When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the "freeing items" state. The MySQL manual suggests that at least part of the reason for a thread to be in ...
13
votes
2answers
24k 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 ...
13
votes
2answers
2k views

What can we do in MySQL 5.0 Replication to address bandwidth concerns?

I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of ...
13
votes
2answers
16k views

MySQL: Tree-Hierarchical query

SUB-TREE WITHIN A TREE in MySQL In my MYSQL Database COMPANY, I have a Table: Employee with recursive association, an employee can be boss of other employee. A self relationship of kind (SuperVisor ...
12
votes
5answers
10k views

How to manage millions of users?

I am about to launch something really big. I need to prepare my server and database. I would like to group each set of 100,000 users in separate user tables but I do not know how to associate one ...
12
votes
3answers
2k views

What to do when a field in a table approaches the max signed or unsigned 32 bit integer?

In any given database that hold user records in the form of an unique auto-increment field (for the sake of the example, inter-user messages)... what to do when the time comes and it approaches the ...
12
votes
5answers
11k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
12
votes
1answer
10k views

Possible INDEX on a VARCHAR field in MySql

I am working in a MySql database, with a table like this: +--------------+ | table_name | +--------------+ | myField | +--------------+ ...and I need to make a lot of queries like this (with ...
12
votes
1answer
9k 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, ...
12
votes
2answers
9k views

MySQL and window functions

It seems that MySQL does not support window functions. E.g. the simple: COUNT(*) OVER() AS cnt does not work. What I am not sure is if this applies to commercial version as well (I assume the ...
12
votes
2answers
1k 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) ...
12
votes
1answer
17k views

Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week. I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual ...
12
votes
2answers
4k views

Is the overhead of frequent query cache invalidation ever worth it?

I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
12
votes
2answers
1k views

Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
12
votes
5answers
2k views

Infrastructure for Highly Concurrent, High Write DB

My requirements are: 3000 Connections 70-85% Write vs Read Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of ...
11
votes
7answers
10k views

Too many columns in MySQL

I have a table with 1699 columns and when I'm trying to insert more columns I get the too many columns in the table error. In this table I have only 1000 rows. For me the most important thing is the ...
11
votes
3answers
10k views

check constraint does not work?

I have the following table. create table test ( id smallint unsigned AUTO_INCREMENT, age tinyint not null, primary key(id), check (age<20) ); The problem is that the CHECK ...
11
votes
5answers
7k views

Where can I find the mysql slow log?

Just as the title says, where can I see it ? Are there any config options for it ( like how many ms would determine if a query is slow or not ) ?
11
votes
3answers
2k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...
11
votes
3answers
9k views

What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data: create table t(k int, v int,index k(k)) engine=memory; insert into t values(10, 1), (10, 2), (10, 3); When issuing select * from t where k = 10 with ...