MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

9
votes
3answers
6k 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 ...
9
votes
5answers
4k 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 ) ?
9
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 ...
9
votes
5answers
2k 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 ...
9
votes
3answers
5k 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 ...
9
votes
6answers
6k views

Splitting Tables in MySQL. Good practice?

I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data. The tables look like this: ...
9
votes
4answers
8k views

Mysql int vs varchar as primary key (InnoDB Storage Engine?

I am build a web application (project management system) and I have been wondering about this when it come to performance. I have an Issues table an inside it there are 12 foreign keys linking to ...
9
votes
3answers
5k 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 ...
9
votes
4answers
1k 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 ...
9
votes
2answers
5k 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 ...
9
votes
3answers
5k 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 ...
9
votes
1answer
9k 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 ...
9
votes
1answer
3k 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 ...
9
votes
3answers
4k views

How to convert mysql to postgresql?

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 ...
9
votes
4answers
11k views

Restore mysql database with different name

How can we restore mysql database with different name from mysqldump file. I dont't want to open dump file and edit it. Any other better methods?
9
votes
2answers
8k views

kill all queries - MySQL

Sometimes during a SNAFU I have to run kill query xxxxxxx twenty or thirty times. Any sort of kill all command I am missing? On account of how I don't like typing.
9
votes
1answer
5k 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 ...
9
votes
2answers
5k views

innodb_file_format Barracuda

I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda. I was looking to play around with some compresses innodb ...
9
votes
2answers
4k views

Reasons for occasionally slow queries?

We are running MySQL 5.1 on Windows Server 2008 R2. We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
9
votes
2answers
1k 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, ...
9
votes
2answers
10k 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 ...
9
votes
1answer
224 views

Power went out - Did query finish?

Is there a way to check and see if a query finished? I ran 3 very long running update queries (+/- 25 hours each) as I went out the door for vacation last week. Unfortunately, somewhere during the ...
9
votes
3answers
2k views

MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEs

I am creating a module where every user often gets a record into a table for 10 to 300 seconds. When the time expires a record gets deleted. The case is: there will be a lot of users and records will ...
9
votes
1answer
4k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
9
votes
2answers
2k 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 ...
9
votes
1answer
5k 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 ...
9
votes
4answers
4k views

Adding an index very slow…is there a mysql cmd to get an ETA or show progress?

I'm currently running an alter query on my table (20M entries) in order to add an index. it's running for more than 3 days already (stuck on 'copy to tmp table'). is there a way I can see the ...
9
votes
1answer
402 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 ...
8
votes
9answers
1k views

Which DBMS are fast enough for an online game (few thousand players)?

I'm currently making an MMORPG game, which could have a few thousand players online at the same time (probably not; just wishful thinking). First we wanted to use MySQL, but I have heard it isn't fast ...
8
votes
2answers
4k 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 ...
8
votes
5answers
6k 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 ...
8
votes
4answers
749 views

Is splitting a 'users' table for authentication purposes a good idea?

Suppose I have a user table in my site in which there are around 2-3 Million users (records) in the table. For speeding up my login process, is it a good approach to split my user table, one for ...
8
votes
2answers
2k views

Does MySQL cache queries?

I'm interfacing a MySQL database with PHP Data Objects (PDO) and executing an extensive SQL query. Normally, it takes about 1500 ms; I still need to optimize it. When I run the PHP script twice ...
8
votes
4answers
8k 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; ...
8
votes
2answers
8k views

How do I show the binlog_format on a MySQL server?

How do I show the binlog_format on a MySQL server? And if I dont like it how do I set it to XX permanently? Where XX is STATEMENT, ROW or MIXED.
8
votes
3answers
4k 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 ...
8
votes
3answers
4k views

How to stress test a SQL server?

I've been tasked to stress test our MSSQL Server and MySQL Server. I would like to know if there are any tools or scripts which I could use on our current systems and the new system to compare ...
8
votes
3answers
46k views

MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

I use the root account created the account 'a'@'%'. But I can't use the it to connect to MySQL server when I specify the host parameter. I can successfully connect without the -h parameter. Please see ...
8
votes
6answers
3k 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 ...
8
votes
4answers
1k views

How can I optimize this MySQL query further?

I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
8
votes
3answers
3k views

Dynamic SQL in MySQL stored routines

According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And ...
8
votes
2answers
260 views

Why would MySQL do serial synchronous I/O?

When looking at a particularly annoying query over MyISAM tables which takes a long time to execute on a number of occasions, I have noted that MySQL seems to expose a rather strange I/O pattern: when ...
8
votes
1answer
9k 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 ...
8
votes
2answers
4k 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 ...
8
votes
4answers
11k 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 ...
8
votes
3answers
4k views

Monitor MySQL activity per database?

What is the best way to monitor activity on each database of my server ? I tried mtop/mytop which display activity in real time but aren't very practical to see all the queries executed on it. I ...
8
votes
2answers
6k 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 ...
8
votes
2answers
10k 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 ...
8
votes
2answers
1k views

Database redesign opportunity: What table design to use for this sensor data collection?

Background I have a network of approximately 2000 sensors, each of which has about 100 data points that we collect on 10 minute intervals. These data points are typically int values, but some are ...
8
votes
6answers
11k views

Alternatives to MySQL Workbench

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 ...