MySQL 5.5 brought many performance enhancements and expanded the feature set of the popular open-source RDBMS.

learn more… | top users | synonyms

4
votes
2answers
97 views

Query only for the first n results of like

What I'm trying to accomplish is a huge speedup (read: incremental results) when searching a database with a like operator which is kind of a last resort, if everything else already failed, to come up ...
4
votes
2answers
1k views

Replication error

We have a slave server that has stopped replication due to the following error: Slave SQL: Query caused different errors on master and slave. What could be the cause of this error? And what would ...
4
votes
1answer
3k views

Can I safely kill OPTIMIZE TABLE on an InnoDB table?

MySQL's documentation for kill warns: Warning Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to ...
4
votes
1answer
3k views

Setting up MySQL Cluster on Two Systems

I am attempting to setup a MySQL Cluster. I have the folllowing setup one management node two data nodes another machine on which two more nodes reside Below is the config.ini file: [ndbd ...
3
votes
3answers
3k views

Issue after moving the ib_logfile1 and ib_logfile0 files

I wanted to increase the innodb_log_file_size for better performance. And I increased it from the default value to 256 MB. As usual I got the error saying InnoDB: Error: log file ./ib_logfile0 is of ...
3
votes
3answers
259 views

Why set up static data in views vs. using tables in mysql?

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields. I have the following too: LDAPIMPORTANT - view that ...
3
votes
2answers
5k views

What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?

I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-) I ...
3
votes
1answer
191 views

skip columns while using LOAD command mysql

I have a csv file having 5 columns and i want to load only one out of that, is there a way i can do it. I want to use LOAD statement of MySQL 5.5 Any pointers are welcomed, help would be much ...
3
votes
2answers
724 views

How to split a string to and get info from another table in mysql

I have a system to assign score for each sentence stored in a column: TABLE 1: word_scores word score this 3 is 4 a 5 test 1 another 0 sentence 8 TABLE 2: ...
3
votes
4answers
8k views

How to Add Federated engine after installing Mysql

I have Mysql 5.5.18 and upon show engines: show engines; +--------------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine ...
3
votes
2answers
72 views

Why wouldnt Mysql use index on the Primary query in a nested query

I have two tables: search_criteria and pricing. There is an index on search_id column in search_criteria table and on pricing_id column in pricing table. But running this nested query does not use ...
3
votes
2answers
4k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
3
votes
3answers
2k views

InnoDB insertion faster

I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think ...
3
votes
3answers
2k views

MySQL optimization - year column grouping - using temporary table, filesort

I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
3
votes
1answer
58 views

where varcharcol = 0 returns all rows

Suppose I have a table like this: create table mytable(cola int primary key, colb varchar(10)); Let's put some data in it: insert into mytable values(1, 'abc'); insert into mytable values(2, ...
3
votes
2answers
2k views

MySQL replication to slave having VPN connection to Master

I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network ...
3
votes
2answers
2k views

ALTER TABLE - Rename a column

This got me nuts! I want to rename a column from read-more to read_more in blog table Tried all this: ALTER TABLE blog RENAME COLUMN read-more to read_more; ALTER TABLE blog CHANGE COLUMN ...
3
votes
1answer
577 views

Which is more efficient, trigger or PHP code?

My requirement is to move EMR data for a patient to a set of history tables when the patient has been discharged. I proposed to use an after trigger on the table housing the discharge flag, checking ...
3
votes
1answer
984 views

MySQL 5.5 InnoDB = OFF?

We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine. However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF ...
3
votes
2answers
51 views

Fetch all words from column with like

I have a column with description and inside it can contain # value . what I want is all the words searched from that column with #tag This is my query SELECT description FROM photo where ...
3
votes
1answer
271 views

How to store static list of hashes in MySQL effectively?

Problem: I have a list of pairs (md5_hash, id). I want to store the data in MySQL and I want to make queries like this SELECT id FROM table WHERE md5_hash = <some_hash> The number of pairs ...
3
votes
1answer
2k views

Recover the disk space after deleting rows from table

I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the ...
3
votes
4answers
855 views

Stress Test to Saturate Memory for MySQL InnoDB

I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of ...
3
votes
2answers
4k views

Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?

If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find.. [mysqld] max_allowed_packet = 1M [mysqldump] max_allowed_packet = 16M What is the point? Because when you ...
3
votes
1answer
145 views

Backup and off-site restore setup

I've been reading a lot of threads about different backup options for MySQL and was hoping for some more insight so I can settle on one. Our current situation is ~100 GB of InnoDB and MySAM data in ...
3
votes
3answers
9k views

How to Restore data from binary log

I am trying to restore some lost data , but I am not having its backup .So I am trying to restore from the binary log .Please explain how to restore from the binary log in mysql .
3
votes
1answer
222 views

Get profiles values where message.sender_user_id = profile.user_id

I have this tables: default_messages CREATE TABLE IF NOT EXISTS `default_messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `message` text ...
3
votes
2answers
1k views

Can we assign mysql Key_buffer_size value for particular table?

In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps: SET GLOBAL myisam_table.key_buffer_size = 4194304; CACHE INDEX test.myisam_table ...
3
votes
2answers
62 views

Is this substring_index behaviour documented or a bug?

mysql> show variables like 'version'; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | version | ...
3
votes
1answer
243 views

Online Schema migration in MySQL : When to start?

I'm the tech lead of a Social Media SAAS, where we're currently experiencing a big increase in data amount we're storing in our mySQL-database. We're having a single MySQL db, and besides many ...
3
votes
1answer
311 views

MySQL / Fusion IO Configuration Question

I need a little configuration optimization. I think what I have is pretty good but feel like there's still room for improvement. Current Config [mysqld] user=mysql datadir=/var/lib/mysql ...
3
votes
1answer
110 views

MySQL Replication not proceeding

I have a weird replication problem I have not seen before. It's basic mysql replication with single master and two slaves. One of the slaves is not executing replication events and seems just stuck ...
3
votes
1answer
509 views

How this simple Stored Procedure ended up in Deadlock

Thanks for all and this forum is really helpful. After few minutes of system running we get “Try restarting the transaction.” And its end up in deadlock. We use MySQL 5.5.28 and the Java code ...
3
votes
1answer
915 views

Query not using index when it should

I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table? ...
3
votes
1answer
2k views

MySQL Slave Relay Logging but not logging Binary Log

I have a master slave setup both mysql 5.5, the setup is fine except that the slave won't Binary log to the standard mysql-binlog only to the relay logs. I fear this may be due to my lack of ...
3
votes
2answers
628 views

How to concatenate a column after x rows?

I have a table with columns object varchar(255), frequency tinyint(3) unsigned, I want to concatenate the objects if the sum of frequencies reach a value. Currently I am doing this in PHP as .. ...
3
votes
1answer
212 views

how to add removed partition?

This is the structure of my table: -- Table: t_keyword_conversion_ga -- Create Table: CREATE TABLE `t_keyword_conversion_ga` ( `id` int(11) NOT NULL AUTO_INCREMENT, `own_domain_id` int(11) DEFAULT ...
3
votes
1answer
2k views

Slow insert performance in mysql innodb table for solr indexing

I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow. ...
3
votes
1answer
829 views

Keeping MySQL staging database on same server updated from production

I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly ...
3
votes
1answer
2k views

What does the base64 BINLOG statements in mysqlbinlog output mean?

I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0. When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed ...
3
votes
1answer
211 views

MySQL RDS Instance eating up memory and swapping

I have an issue with memory on a RDS instance which is of db.m2.4xlarge class. Below are the specifications of the RDS instance Instance Class: db.m2.4xlarge ( 68.4 GB of RAM ) Engine: ...
3
votes
1answer
164 views

How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option ...
3
votes
1answer
502 views

Getting Random Sample from large database MYSQL (No Auto Inc Field)

You were so helpful with my last question, I thought id throw another one at you thats stumping me. Basically I got a table, requirements from company was it was all supposed to be 1 table so I got ...
3
votes
1answer
217 views

MySQL Stress testing issues?

I had gone through MySQL High Performance book Chapter 2 for MySQL Bench marking and Profiling,I have some questions about benchmarking How can we test an existing database and queries against it ...
3
votes
2answers
7k views

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
3
votes
1answer
238 views

What's the most unsafe MySQL configuration?

I have MySQL 5.5.27-29.0-log instance running specifically for Jenkins. Since tests data is always recreated during setup, ACID is unnecessary and I'm hoping to sacrifice it to gain more performance. ...
3
votes
1answer
433 views

How to Update same table on deletion in MYSQL?

In my Database I have a table: Employee with recursive association, an employee can be boss of other employee. Following is query I used to create table: CREATE TABLE IF NOT EXISTS `Employee` ...
3
votes
1answer
397 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
3
votes
1answer
1k views

Configured MySQL replication but its not working

I have configured MySQL master and slave replication setup on 5.5.x. But for some reason data is not getting replicated properly. What might be the reason? At Master: /etc/my.cnf [mysqld] ...
3
votes
1answer
183 views

mysqld-multi with first DB as Slave and second DB as Master

Yesterday, I setup mysqld-multi in my.cnf. It works fantastic, but there is still a further Problem. The first DB on socket 1 running as Slave from a other server. The second one from socket 2 ...