MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

7
votes
1answer
2k views

Unexplained InnoDB timeouts

I've been seeing some very basic updates timing out lately and have not been able to determine the cause. An example: //# Query_time: 51 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 UPDATE photos ...
6
votes
4answers
3k views

What should I ask at a MySQL DBA interview?

I am a software developer and am helping my team hire a MySQL DBA. The core challenges that we are facing are: Slower queries and performance due to Hibernate. Database management (backups, tuning, ...
6
votes
5answers
3k 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 ) ?
6
votes
3answers
3k 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., ...
6
votes
4answers
473 views

Purpose of IDs in a User Table Database

In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
6
votes
3answers
253 views

What does the output of a JOIN statement look like?

I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use. Let's say I have 2 tables: CREATE TABLE Cities ( id INT UNSIGNED PRIMARY ...
6
votes
1answer
1k views

Is PostgreSQL replication production ready?

How does PostgreSQL native replication compare to MySQL? I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects? ...
6
votes
5answers
2k views

From a set of values, how do I find the values not stored in a table's column?

I have a table which will potentially store hundreds of thousands of integers desc id_key_table; +----------------+--------------+------+-----+---------+-------+ | Field | Type | ...
6
votes
2answers
2k views

Is one-to-one relationship normalized?

Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
6
votes
2answers
1k views

Is DB2 more reliable and stable compared to PostgreSQL or MySQL?

Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps. I had data loss few times and thanks to old backup i was always up and running. But for a recent project for Airport, ...
6
votes
3answers
12k 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 ...
6
votes
5answers
17k 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 ...
6
votes
3answers
405 views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
6
votes
3answers
3k views

Too Many Connections

I'm using MATLAB to feed the database but after a time I'm getting a too many connections error. I always using opendb and closedb in pair. Is it to fast the feeding? Do I have to wait after I ...
6
votes
2answers
4k views

Do I have to buy MySQL license?

I've read in a forum that if your database capacity is more than 1G you should buy its license. Is this true? How much will it cost?
6
votes
3answers
4k views

Avoiding temporary tables while sorting by a column in a different table

So I have this MySQL InnoDB query here: SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt FROM stories s LEFT JOIN votes v ON s.vid = v.vid WHERE s.lv = 1 AND s.status = 1 GROUP BY s.vid ...
6
votes
2answers
972 views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
6
votes
2answers
12k views

What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

I am very new to Database Administration. I face a lot of problems while setting up mysql master-slave replication. I also face regular mysql replication troubleshooting issues. Can anybody helps ...
6
votes
1answer
235 views

Why does Oracle keep working on MySQL 5.1

2 years ago the first version of Mysql 5.5 was released. What is the reason to continue working on MySQL 5.1 - the current release is Mysql 5.1.62 It looks like there are should be fundamental ...
6
votes
3answers
2k views

What is the best approach to run a DELETE query on 92K of 4 million rows?

Options that have occurred to me: One big delete statement. For example: delete from x where (y = 'x' and x = 'y') or (y = 'a' and x = 'b') Same as above, but chunk in smaller pieces, 10k at a ...
6
votes
2answers
802 views

Single Drive vs Multiple Drives

Generally bottleneck of RDBMS (I am a MySQL user) performance is disk access. SSD provides great performance compared with conventional spindle drives. Question : Is it possible to improve ...
6
votes
4answers
737 views

When to use MySQL query_cache?

Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a ...
6
votes
4answers
6k views

How to improve InnoDB DELETE performance?

So I have this audit table (tracks actions on any table in my database): CREATE TABLE `track_table` ( `id` int(16) unsigned NOT NULL, `userID` smallint(16) unsigned NOT NULL, `tableName` ...
6
votes
4answers
3k views

MySQL Replication - Introduce new Slave to replication

I've recently taken on a sys admin position managing a group of 20 (or so) servers. One thing I haven't dealt with before (apart from in a test situation), is introducing a new slave to the MySQL ...
6
votes
4answers
3k views

How to search a MySQL database with encrypted fields

Suppose I need to encrypt certain table-fields of a MySQL database. Additionally, I need to search some of those fields I did encrypt. How would one search those fields anyway? Decrypting each ...
6
votes
3answers
8k views

mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

I am following this solution here http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size ...
6
votes
1answer
457 views

Loyalty reward - record for every day of a week

I have a table that contains every login a user makes. I am trying to make a loyalty system that rewards users if they've logged in every day for a week. So, the real question, how do I get every ...
6
votes
7answers
35k 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 ...
6
votes
2answers
195 views

Is there any way to trace optimizer's work in MySQL?

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Is there anything in MySQL through which I can get the information ...
6
votes
2answers
6k views

How to use Unique key via combinations of table fields?

Take a look at the following sqlfiddle: http://sqlfiddle.com/#!2/dacb5/1 CREATE TABLE contacts ( id int auto_increment primary key, name varchar(20), network_id int, ...
6
votes
2answers
1k views

Amazon RDS I/O requests - 1 query = 1 billable I/O?

I have an InnoDB database that I'd like to migrate onto Amazon RDS. My current implementation, hosted on my own server, shows roughly 8M queries per month. The RDS site say that I/O Rate $0.10 ...
6
votes
2answers
4k views

Ibdata usage and Recommendations?

I have a Production server On November 5,2011 the ibdata size was 100G. In Approximate 3 months it is increased to 200G.So it has just doubled its size.So it is a Huge Data.Currently lvm is 251G. I ...
6
votes
2answers
10k 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 ...
6
votes
7answers
6k views

What tools are there for data masking? (MySQL, Linux)

I'm looking for (ideally free, open-source) data masking tools. Do any such exist? Note: this related question deals with tools for generating test data, but in this question I'm more interested in ...
6
votes
4answers
2k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
6
votes
2answers
2k views

Scaling Percona datacenters: setup and replication

Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you. We have already scaled alot of our application stack: we offloaded ...
6
votes
1answer
7k views

Trigger to UPDATE after UPDATE?

I want to make a trigger to record the time of any update as CREATE TRIGGER col_update AFTER UPDATE ON col FOR EACH ROW BEGIN UPDATE col SET updated=NOW() WHERE id=NEW.id; // or OLD.id END The ...
6
votes
3answers
2k views

Users complain that system runs slow when mysqldump is in progress.

The MYSQL database (ibdata1) is of size 73 GB and is configured to run as a dedicated database server on Windows 2008 O/S for INNODB tables. We are running the backup using mysqldump mysqldump ...
6
votes
4answers
13k views

Why does MySQL say I'm out of memory?

I was trying to execute a fairly large INSERT...SELECT in MySQL with JDBC, and I got the following exception: Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes) ...
6
votes
2answers
4k 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 ...
6
votes
2answers
15k views

How do I shrink the innodb file ibdata1 without dumping all databases?

InnoDB stores all tables in one big file ibdata1. After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and ...
6
votes
3answers
5k views

MySQL replication - slave is continuously lagging behind master

I am using MySQL-5.1.50 with a Master-slave replication setup. Most of the time the slave is lagging behind the master. When I run show processlist;, there is no query that's taking a long time. ...
6
votes
4answers
2k views

Recommended MySQL statistics for production environment

Other than usual system stats (i/o, ram usage, cpu, load, etc) I am currently collecting questions, qps, running queries and buffer pool hit. I find qps pretty useless since our production servers ...
6
votes
2answers
306 views

Less restrictive query return less result due to simple removing one additional constraint

Look at this query SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance FROM ...
6
votes
2answers
13k views

How do you remove fragmentation from InnoDB tables?

I have a Database having number of tables. I want to delete some records from the tables say the no of records are more than 20K or 50K. The All the Tables are InnoDB. And file_per_table is off. ...
6
votes
2answers
1k views

MySQL warm procedure

How do you usually warm up your database ? I run a similar queries to this for every table in DB: SELECT * FROM ip_log ORDER BY ID; SELECT ip, member_id FROM ip_log ORDER BY ip, member_id; Is that ...
6
votes
3answers
458 views

Combining InnoDB with MyISAM [duplicate]

Possible Duplicate: Is it common practice to mix InnoDB and MyISAM tables on same server? I have a movie clips and photos table in an application; these tables contain a tag column, which ...
6
votes
1answer
1k views

Using FORCE INDEX

I was in a presentation recently given by a Facebook MySQL engineer and he mentioned there that using FORCE INDEX reduces I/O. It was something to do with the MySQL Query Optimizer not needing to go ...
6
votes
2answers
3k views

MySQL Auto_increment going 2 by 2

I installed MySQL Workbench the other day, accessed my company's database and made myself a table to work with. So far so good. The problem is, I noticed my auto_increment is incrementing 2 by 2. For ...
6
votes
3answers
570 views

Do I need to add a new single column index to a table if a multi-column index on that field already exists?

I've a table with a multi-column UNIQUE index on _job_id__ and __keyword_id__. Would I also need to add another index to __job_id__ if I have a frequent query which performs a GROUP BY on that ...