MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

7
votes
2answers
17k 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: ...
7
votes
2answers
454 views

What is the purpose of the USING parameter in SQL?

Working with some code on a standard MySQL database, and I came across this statement: SELECT * FROM foo LEFT JOIN bar USING ('bar_id') WHERE foo_id = 1 What's throwing me is: what is USING for, ...
7
votes
4answers
12k views

Replicating a remote MySQL database to MS SQL Server 2008

I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database. Is this possible? Can anyone outline the steps required in order to achieve this? Thanks.
7
votes
3answers
7k views

Best of MyISAM and InnoDB

Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
7
votes
4answers
2k views

For a MySQL query, how do you determine physical and logical I/O?

I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
7
votes
2answers
437 views

ROLLBACK doesn't work after INSERT INTO newly created destination table

I am working on PHP-script which imports CSV file (customers.csv) into MySQL table (customers). Before inserting contents of CSV-file into the mysql table I am first backing up the original customers ...
7
votes
2answers
930 views

How to drop empty tables

Is it possible to drop all empty tables from my huge database (mysql)? I'm looking for a sql command to automatically remove all those empty tables. Currently, I have 305 tables in my dataset, and ...
7
votes
3answers
7k 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. ...
7
votes
3answers
193 views

Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?

Title sums it up. I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been ...
7
votes
5answers
4k 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 ...
7
votes
3answers
10k 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 ...
7
votes
2answers
8k 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, ...
7
votes
7answers
43k 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 ...
7
votes
3answers
2k views

Deleting all duplicates

I am trying to delete all the duplicates but keeping single record only (shorter id). Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones. ...
7
votes
3answers
5k views

How to Best Implement nearest neighbour search in mysql?

So, in short, What should be the data type of latitude and longitude? What SQL command I should call to get the first 100 nearest restaurants for example? Detail: I have 100k biz record each with ...
7
votes
3answers
1k views

I am using the MEMORY storage engine but MySQL still writes to my disk…Why?

I am using the MEMORY Engine for all tables associated with a particular MYSQL query because speed of access is paramount to my project. For some reason, I have noticed that a large amount of disk ...
7
votes
4answers
14k views

MYSQL ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref used (client option 'secure_auth' enabled)

when I tried to restore all database dump which is in 5.0 version to 5.6 version, it got restored and after that when I tried to reconnect, am getting the following error ERROR 2049 (HY000): ...
7
votes
2answers
5k views

Slow MySQL Database Import with mysqldump and USE .. SOURCE

I have about 12 tables in MySQL innoDB, one of them has 11 million records in it. I used this command to back things up: mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz ...
7
votes
4answers
3k views

MySQL Cluster RAM requirement

From MySQL 5.1, the data doesn't need to be entirely in memory anymore. I've read that the indexed columns (I think the entire index structure) must still be in memory (MySQL High Availability, 2010, ...
7
votes
2answers
13k 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 ...
7
votes
4answers
16k 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) ...
7
votes
1answer
206 views

Are relations slower than a big, inefficient table?

I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a ...
7
votes
1answer
486 views

Deleting large amounts (4 million rows) of mysql data efficiently, on a very regular basis

We have a mysql table that at any given time has about 12 million rows. We need to delete old data to keep the size of the table somewhat manageable. Right now we are running this query daily, at ...
7
votes
2answers
16k views

MySQL creates temporary tables on disk. How do I stop it?

We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in ...
7
votes
3answers
760 views

Should I separate frequently updated columns?

I have a users table that contains users' information and a column named credits that is frequently updated. On index page I'm showing list of users with basic user information but I don't need ...
7
votes
1answer
5k views

Why would an UPDATE SET REPLACE() statement match rows, but change none and give no warnings?

I'm seeking a certain string in a field and want to replace it with a new string. Specifically, I want all references to one URL to be changed to another URL. I've crafted this SQL statement and am ...
7
votes
2answers
2k views

What's a good way to add a PRIMARY KEY to a large InnoDB table?

I have a table with around 30 million rows in MySQL 5.5 using the InnoDB storage engine. This table has a foreign key that links to another table with around 3 million rows. I want to add a primary ...
7
votes
1answer
10k 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 ...
7
votes
1answer
2k views

MySQL reads / writes per table

I am optimising our DB. Essentially I am trying to find the most written and the most read tables in our db. After that I will follow by sym-linking those tables into separate drives. Is there a way ...
7
votes
1answer
8k views

MySQL column defined with NOT NULL is allowing nulls

I had created a table a while back and started adding data to it. Recently I added a new column (address) to it with NOT NULL as part of the new column. The old rows (pre-addition) are still null, ...
7
votes
2answers
2k views

MySQL deadlock - cannot restart normally?

MySQL version 5.5.13-1 A snippet from the SHOW ENGINE INNODB STATUS\G: LATEST DETECTED DEADLOCK ------------------------ 111218 10:22:34 *** (1) TRANSACTION: TRANSACTION 1318D95B, ACTIVE 0 sec ...
7
votes
3answers
701 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 ...
7
votes
3answers
2k views

What Mechanism does MySQL use to schedule Backups on Windows Server 2008?

When scheduling database backups on MySQL Server (v5.1.45-community) running on Windows Server 2008, what mechanism is used to trigger the backups to run? I think it's using the Windows Task ...
7
votes
3answers
1k views

Can you “su -” in MySQL?

If I have root on a mysql database and I want to drop privileges to that of a normal user, without using their password, can I do it? if so how? think # su - username on unix. Basically, I'm just ...
7
votes
1answer
1k views

What's the maximum number of tables that can be referenced in a query in MySQL?

In the days of SQL Server 2000 and 2005, the maximum number of tables in a query was 256. Does MySQL have a similar limitation? I know that such query of such size means that either there's ...
7
votes
1answer
6k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select ...
7
votes
5answers
21k views

How to properly kill MySQL?

I have CentOS 64bit with CPanel installed and I use service mysql stop However, it keeps doing ..... for minutes and it never stops. It used to be instant. Any idea why it does that and how to fix? ...
7
votes
1answer
3k views

Deleting MySQL table with pending transactions

Is there a way to delete an InnoDB table or database with pending transactions in MySQL (preferably on file system level)? What happened: I use MySQL 5.5.28 and ran LOAD DATA INFILE… to import a ...
7
votes
2answers
6k 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 ...
7
votes
2answers
3k views

MySQL CAST vs. CONVERT

Let's say that I have a VARCHAR (which contains numeric data) that I want to use for a simple computation (adding 10 to it). According to the MySQL documentation on CAST functions, I could accomplish ...
7
votes
3answers
2k views

Is MySQL Replication Affected by a High-Latency Interconnect?

We've got a vanilla master and slave MySQL setup that reside in different data centers, and another slave in the same datacenter as the master. The bandwidth between the datacenter is pretty high (in ...
7
votes
3answers
4k views

From where does the MySQL Query Optimizer read index statistics?

I'm trying to determine from where the MySQL optimizer obtains the list of indexes that are available for a table when it estimates the cost of (prepares) a query from.
7
votes
4answers
4k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
7
votes
7answers
2k views

MySQL Indexing VarChar

I am trying to index my blogentries database for better performance but found an issue. Here is the structure: CREATE TABLE IF NOT EXISTS `blogentries` ( `id_id` int(11) NOT NULL AUTO_INCREMENT, ...
7
votes
2answers
168 views

Optimization problem: compound clustered keys, flag conditions and index-merge

Three tables: product: with columns: ( a, g, ...a_lot_more... ) a: PK, clustered g: bit-column main: with columns: ( c, f, a, b, ...a_lot_more... ) c: PK, clustered f: bit-column (a, b): UQ ...
7
votes
3answers
1k views

Appropriate multi-language schema, or overkill?

UPDATE 2: I've actually ended up using this, and it's great after a couple tweaks. Here's my post on its actual design, and in action: http://tim.hithlonde.com/2013/lemon-schema-works/ I am building ...
7
votes
2answers
2k views

BigInt or Varchar for a large number I won't ever sort / calculate on

I need to store users' Facebook IDs in a column in my DB. The ID always consists of only digits, and can be as large as a number in excess of 500 million. Considering that I won't ever need to do ...
7
votes
4answers
3k views

One slave, multiple masters MySql

Is it possible to set-up MySql Replication to have one slave listening to two different masters?
7
votes
1answer
6k 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?
7
votes
3answers
7k views

InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

This server is a virtual machine running CentOS 6.0, MySQL 5.5.21 There is a database named devSystem. There are InnoDB tables within. Running the following commands causes error ERROR 2006 ...