MySQL : Open-Source, Relational Database Management System
6
votes
2answers
1k 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
3k 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
625 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
3answers
13k 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 ...
6
votes
4answers
4k 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 ...
6
votes
1answer
205 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
1k 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
4answers
544 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
3answers
3k 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 ...
6
votes
1answer
493 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 ...
6
votes
2answers
7k views
When to use views in MySQL?
When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table?
One reason that I would prefer to use views is that the database schema ...
6
votes
5answers
5k views
slow load speed of data from mysqldump
I've got a moderate size MySQL database with about 30 tables, some of which are 10 million records, some 100 million. The mysqldump of all the tables (into separate files) is fairly fast, takes maybe ...
6
votes
1answer
305 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
1answer
173 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 ...
6
votes
2answers
121 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
3answers
574 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
1answer
790 views
Difference between count(*) and count(1) in mysql?
In MySql we can count the total number of records by using count(1) or count(*).
Is there any technical difference between them?
6
votes
1answer
2k 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 ...
6
votes
1answer
2k 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
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 ...
6
votes
2answers
1k 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 ...
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
1answer
7k 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 ...
6
votes
4answers
4k 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
912 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 ...
6
votes
3answers
15k 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 ...
6
votes
1answer
784 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
1k 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 ...
6
votes
7answers
621 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,
...
6
votes
1answer
2k views
When to switch from MyISAM to InnoDB?
We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.
My original plan was to upgrade ...
6
votes
4answers
1k 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
4k 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 ...
6
votes
3answers
306 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
2answers
2k views
Trouble deciphering a deadlock in an innodb status log
We are accessing MySQL from the Microsoft ADO.NET connector.
Occasionally we are seeing the following deadlock in our innodb status and haven't been able to identify the cause of the problem. It ...
6
votes
3answers
8k views
How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline?
MySQL Server 1 is running as Master.
MySQL Server 2 is running as Slave.
With both DBs online, they are in "perfect sync". If Slave goes offline, there's no problem if Master still online; they will ...
6
votes
2answers
949 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 ...
6
votes
6answers
2k views
Maximum value from multiple tables
Consider the following situation: we have T1, T2, & T3 tables in which saved id and price of products. Now we need to find the id of products which have maximum price of all 3 tables. I have this ...
6
votes
1answer
1k views
How can I disconnect clients from MySQL?
I need an efficient way to disconnect all clients with a given username from MySQL. I thought about changing the users password but I think that is only checked when the connection is made.
Ideas?
6
votes
1answer
259 views
Application Settings in Database
In the past I have stored all generic settings and configuration items such as: custom application title, home address, version, debug, etc.. all in a "settings.php" file that I would include into all ...
6
votes
1answer
2k 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?
6
votes
2answers
2k 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 ...
6
votes
1answer
2k views
Is it possible to change ENUM() lists?
I wasn't sure if changing ENUM() list isn't possible so I made a test.
In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no').
Then I executed...
...
6
votes
1answer
551 views
recover a single mysql database on a busy master-slave replicated system
Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system.
I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave ...
6
votes
2answers
218 views
How should I Optimize storage for this table?
I'm using MySql to store a basic table of this format:
id int(11) //Auto-Incrementing ID
data varchar(5120) //Random input data, compressed by a program, not mysql.
...
6
votes
2answers
7k views
MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load
i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
6
votes
1answer
783 views
What are some important MySQL status variables to keep an eye on? [closed]
I'm a fairly new DBA with a programming background. As such, I like to build tools to manage tasks to help me familiarize myself with the intricacies of a technology.
In this case, I am managing ...
6
votes
3answers
3k views
MySQL status variable Handler_read_rnd_next is growing a lot
In MYSQL status, Handler_read_rnd_next value is very high.
I am aware that, this value will be incremented when a query is executed which is not having proper indexes.
But, even when we execute ...
6
votes
2answers
2k views
Performance of a Trigger vs Stored Procedure in MySQL
A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
6
votes
1answer
220 views
MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`
I know very little about database administration but I have to deal with some very large tables on my site.
This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
6
votes
1answer
228 views
LOAD DATA INFILE blocks on concurrent use against MyISAM tables
I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks ...
