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

learn more… | top users | synonyms

0
votes
1answer
37 views

Will MySQL redirect a read request to the master after a certain time? [closed]

I can't find what variable controls this or any information about the time limit. Thanks!
1
vote
1answer
56 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
2
votes
2answers
725 views

MySQL COUNT Query taking too long time to execute

I have below query that takes 7 secs to run SELECT COUNT(*) AS `count` FROM `yuldi`.`businesses` AS `Business` LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory` ON ...
1
vote
1answer
192 views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
1
vote
2answers
77 views

Nested query efficiency in MySQL

I have a table with several attributes like this: id id2 attribute -------------------------------- 1 100 blue 2 100 red 3 100 green 4 100 white 5 102 blue 6 102 green 7 102 ...
1
vote
2answers
41 views

Are relay logs necessary for replication?

I'm trying to understand when/where using relay log is beneficial. According the documentation, mysql captures and writes events from the master's binary log as the result of the I/O thread. Then, the ...
0
votes
0answers
15 views

How is the key length determine in an index?

Aside from explicitly setting the key length when creating an index, how does MySQL determine the appropriate key len shown when running "explain"? Thanks
1
vote
1answer
22 views

Is there any value in adding an already indexed column to a composite index?

The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE ...
0
votes
0answers
12 views

Is there any value in adding a pk column to an composite index? [duplicate]

I recently inherited a database with indexes that contain the table's primary key as part of some composite indexes. Given MySQL adds a hidden column representing the PK regardless of whether you ...
1
vote
1answer
49 views

Migrating MyISAM tables from 5.5 => 5.6

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync? Is this safe? I know we need to do a full ...
1
vote
2answers
47 views

How can I store least used table files to a slower drive?

Is it possible to separate the least used and most frequently used data files on to separate disks when using InnoDB?
1
vote
1answer
23 views

How to decipher the transaction message in innodb status log.

I have several transactions that seem have stalled. Looking at the innodb status log, I found several messages similar to the following: ---TRANSACTION 69DBBB, ACTIVE 947 sec starting index read, ...
1
vote
1answer
56 views

How to store temp tables in memory during table “optimize”?

When optimizing tables, SHOW PROCESSLIST lists it as "copying to tmp table". I'm trying to ensure the copy takes place in memory to speed up the process. I've tried adjusting the tmp_table_size to ...
1
vote
2answers
39 views

Mysql Master Offline

In a unidirectional Master-Slave setup, should the slave be manually stopped in cases where the Master is offline due to Maintenance, Crash etc. ? Or should one just leave it so that it keeps trying ...
1
vote
1answer
68 views

What is the difference between uniqe index/unique/unique key/unique constraint keywords?

I am using mysql 5.5.16 What is the difference between the following DDL statements? 1) create table sql_query ( id int not null , query_text varchar(21826) not null , query_md5 ...
1
vote
2answers
225 views

Fastest way to create local copy of live InnoDB MySQL database?

We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+). Every month or so we need to copy this database to our development machines to work on locally. ...
0
votes
2answers
74 views

MySQL Slave not updated

This might be a duplicate of questions on the same problem asked before. But all the suggestions to the previous questions do not seem to work in my case. I have set up Replication using SSH Tunnel. ...
2
votes
1answer
305 views

Space not reclaimed after dropping MyISAM table

I just dropped a rather large MyISAM table (~4 gb); it was 350k rows with about 11k of data per row. However, when I run df -h on my database server, I am still at 100% disk usage, so much so that I ...
0
votes
1answer
58 views

How to create two same name databases in two different MySQL users?

I am using MySQL 5.5 running on Ubuntu 12.04. I have created two different users as 'root' and 'abc' and I want to create 'Items' database in this both user accounts but I want this two databases ...
2
votes
3answers
90 views

How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

I have two tables 1. Customer_details and 2. Order_details. 1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name, 2.Order_details table --> fields-- Ord_Id(Primary ...
0
votes
0answers
151 views

#2002 Cannot log in to the MySQL server / Connection for controluser as defined in your configuration failed

I use PHP/MySQL in Ubuntu 13. To reduce the size of ibdata1, i followed this post What is the best way to reduce the size of ibdata in mysql? and make necessary changes as told in the answer. I did ...
0
votes
0answers
23 views

How to acheive Concurrency in mysql when there is 500+ request simultaneously to the server

I am using Mysql (InnoDb), in this I am facing problem when we checked the curl request upto 500 connection at a time (simultaneously) but when we try more than that then my servers goes to idle ...
0
votes
2answers
139 views

How can I execute multiple statements at once in MySQL Workbench?

I'm trying to execute multiple LOAD DATA INFILE statements at once using MySQL Workbench. However, they execute one at a time. Anyway of forcing them to run in parallel? Thanks for the help!
1
vote
2answers
81 views

Is there a rule of thumb regarding the size and number of a buffer pool instances?

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to ...
13
votes
3answers
2k views

effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

I am looking for advice on table/index design for the following situation: I have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
0
votes
1answer
86 views

How to overwrite csv file of MySQL result set?

My SQL query is given below, select company.Company_name as COMPANY_NAME, IFNULL(followup.contact_date, ' ') AS FOLLOW_UP_DATE, IFNULL((select Full_name from user where user_id = followup.user_id), ...
0
votes
1answer
113 views

update variable from multiple tables in one row

I have two databases, and I need to UPDATE variable 'birthday' from one row to another checking 'name' (from db1 to db2). The problem is, that 'name' variable in second db is located in another table ...
0
votes
0answers
53 views

How to update Status using IF Condition in Mysql trigger

I'm trying to write an update trigger that will Design_Update_Status to Customized when a date is entered in Custom_Design_Update_Date field and if nothing is entered that field it should update ...
-3
votes
1answer
65 views

Need help on query optimization in MySQL

Below is the query, I need to optimize it. It is using subquery, I need to re-write it using join how to do this SELECT Client_ID FROM SQL_AuditPro.clients c WHERE (SELECT ...
4
votes
1answer
5k views

SUPER privilege not defined for master user in Amazon MySQL RDS

I have created one medium instance on amazon rds in asia pecific (singapore) region. i have created my master user with master password. and it is working/connecting fine with workbench installed on ...
0
votes
1answer
32 views

MySQL is not logging queries into log file

I am using MySQL 5.5 with Amazon Linux 2013. Following is my configuration for log file. slow_query_log = 1 long_query_time=0 log-error = /var/log/mysqld.log slow_query_log_file = ...
0
votes
3answers
51 views

MySQL backup breakes a table every time

I've a MySQL Server 5.1.58-1ubuntu1 installed on an Ubuntu 12.04 LTS. Every night we do a backup of all the databases with this script: rm -rf /var/backup/mysql/ * for i in $(echo 'SHOW DATABASES;' ...
0
votes
1answer
43 views

Immigrate to file-per-table mode in MySQL

I have 20 MySQL databases and in each there are about 20 tables. All the data is saved in a file called ibData1. Now I discovered about the file per table mode but if I change the value in the my.ini ...
1
vote
1answer
40 views

What's the complexity of renaming a column in MySql?

We want to rename a column in our MySql (version 5.5) database with something like this: ALTER TABLE t1 CHANGE a b INTEGER; Is this an O(N) (N=number of rows in the table) or an O(1) operation? I ...
0
votes
2answers
89 views

Show Users That Connect To DB

We are using Maria DB (v5.3 I believe) in our production environment. One issue that we have, is that historically, the dev user (which has full rights and privileges) is used in all applications ...
2
votes
2answers
34 views

Mysql used key changes between two consecutive EXPLAINs

I have a huge unmaintained innodb table (~1.5billion rows), I just started to try to query it. I write && instead of AND once; and surprise! it uses different key! EXPLAIN EXTENDED SELECT * ...
1
vote
1answer
179 views

Generate INSERT statement from SELECT query

I have two databases, in first database I have a table called zoom_city and in the second database I have a table called courrier_city. The SQL for each is below: CREATE TABLE IF NOT EXISTS ...
-2
votes
1answer
443 views

how to autoincrement varchar datatype depending upon the product details name using trigger

I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id ...
5
votes
3answers
392 views

MySQL varchar(255) limitation & Anomaly

I have defined a column in my MySQL database as : ALTER TABLE `my_table` CHANGE `desc` `desc` VARCHAR( 255 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL However, when I enter text ...
1
vote
3answers
65 views

Contains statement Query

I need to check the string with the column if the column contains that string or not. example:String 'how to make pizza' and the column if has 'pizza' i need to fetch that.. like SELECT * FROM ...
1
vote
0answers
73 views

Mysqldump --flush-logs results in small backup files?

New to MySQL and just learning how to setup a daily full backup (binary logs enabled). My goal is to run a daily full backup of the databases/schemas and reset the binary log. I'm currently using an ...
1
vote
0answers
62 views

MySQL query with order by decreased performance

Here is my schema: CREATE TABLE `UserMst` ( `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `EMailID` varchar(50) DEFAULT NULL, `UserName` varchar(20) DEFAULT NULL, `FullName` ...
1
vote
3answers
758 views

Backup / Export data from MySQL 5.5 attachments table keeps failing!

Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
0
votes
0answers
15 views

MySQL: Archival How to?

I would like to know the correct Archival Strategy for my DB Schema. We are using Mysql 5.5. Here is the full scenario: We have around 35 tables in DB out of which 10 would be transactional in ...
11
votes
3answers
5k views

MySQL InnoDB crash post-mortem

MySQL crashed on me this morning. With the exception of the standard MySQL included databases everything I use is InnoDB. I attempted to restart the MySQL daemon but it failed twice. I then ...
0
votes
1answer
459 views

SQL Query is sometimes slow, sometimes fast

I have two hosts, one with my rails app, one with my MySQL database. Going through my rails Logfile I can see that sometimes queries take much more time than the same query at other times. Think 200ms ...
1
vote
1answer
455 views

InnoDB - How to get top locked tables and rows which are locked

I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?
0
votes
1answer
30 views

Check whether MySQL Innodb Rows are compressed

After setting the various variables to enable compression on the Innodb Barracuda tables, what query will you use to check that those tables are indeed compressed?
-4
votes
2answers
78 views

Performance with IN clause

Does indexed data affect query performance? means If I passed value as 1,2,3,4... in ordered form in any IN clause, can it improve query performance? and what happen in case I pass value randomly ...
0
votes
1answer
126 views

XML dump of MySQL database don't include foreign key constraints. What can be the reason? sqldump(.sql) file list it

I am accessing schema information of databases in XML format. I am taking dump of the database in XML format using the command below. mysqldump --no-data --xml -u root -p bakerydb > bakerydb.xml ...