MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

7
votes
5answers
14k views

MySQL : DROP TABLE Starting with a Prefix

EXAMPLE : I have more than 30 tables starting with a prefix "dp_" and about 12 starting with "ex_". QUESTION : How can I drop all tables starting "dp_" in one query ?
7
votes
2answers
433 views

Data Compression Through Base36 Encoding

I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type. I'm thinking about using Base36 encoding to accomplish this. Are ...
7
votes
3answers
764 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.
7
votes
1answer
2k 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? ...
7
votes
3answers
16k 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 ...
7
votes
2answers
18k 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
5answers
33k 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 ...
7
votes
2answers
17k views

How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings. Any idea on how to do a case sensitive search in SQL query?
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
2answers
466 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
3answers
6k 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 ...
7
votes
3answers
757 views

InnoDB import performance

I'm struggling with bulk importing a quite big InnoDB-Table consisting of roughly 10 Million rows (or 7GB) (which for me is the biggest table I've worked with so far). I did some research how to ...
7
votes
2answers
1k 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 ...
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
511 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
1k 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
8k 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
2answers
15k 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 ...
7
votes
3answers
194 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
5k 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
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
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
15k 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
6k 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
1answer
3k views

Replicate tables to different database name

Our QA environment has all it's database names suffixed with "test". For example dbname1 in production would have a counterpart dbname1test in QA. (This is largely to help protect against prod/qa ...
7
votes
4answers
17k 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
208 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
2answers
18k 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
782 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
2answers
7k views

Amazon RDS for MySQL vs installing MySQL on an Amazon EC2 instance

At work, we host all our webservers on Amazon EC2 and usually have used MySQL databases installed on the same box as our Apache webserver, and communicated with them on localhost. We now face a need ...
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
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
7k views

How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production. So I would just like to make all collations which are utf8_bin to ...
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
11k 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
728 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
23k 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
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 ...