Tagged Questions

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

learn more… | top users | synonyms

1
vote
1answer
70 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
1k 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
2answers
55 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 ...
1
vote
2answers
102 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 ...
0
votes
0answers
18 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
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
23 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 ...
1
vote
1answer
61 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
279 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 ...
2
votes
1answer
31 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
76 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
3answers
117 views

Will redirecting queries to dev/null warm up indexes?

I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice? e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt Thanks
0
votes
2answers
67 views

MYSQL: Join with many to many

I'm trying to create a view and trying to see what can be done, I have a main table a table I wanna join to and also a junction table. Event table (it's very big so it's just dummy data): ...
1
vote
2answers
45 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
79 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 ...
0
votes
2answers
105 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. ...
1
vote
2answers
404 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
1answer
94 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
1answer
503 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
0answers
210 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
24 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
270 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!
0
votes
2answers
112 views

How to verify data dumps between two MS SQL and MySQL

I'm trying to verify data exported from MSSQL and imported into MySQL is 100% accurate using MD5 hash. On Linux, I'm using md5sum and WinMD5 on Windows. The encoding is the same: UTF8. I tried both ...
1
vote
2answers
105 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 ...
0
votes
1answer
60 views

MySQL: How to calculate potential wait time?

I'm trying understand the relationship between WAIT_MS and COUNT_STAR. Is this saying the average wait time is 3.2s and occurred 11,543 times for a total of approximately 10.5hrs of aggregate wait ...
0
votes
1answer
133 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
188 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
57 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 ...
1
vote
1answer
619 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
67 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 ...
0
votes
1answer
44 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
1answer
188 views

How should I config the my.ini(or my.cnf) file for mysql?

Thanks for taking your time to look at my question, and I hope that you can help me with this. I have a Windows Server 2012 server, it has: Memory : 3840MB Ram Processor: Intel(R) Xeon(R) CPU ...
1
vote
1answer
42 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 ...
1
vote
1answer
62 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 ...
2
votes
2answers
35 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
290 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 ...
0
votes
2answers
137 views

How to autoincrement Prod_id based on Product_Name using trigger in mysql

Product_Details table : (Fields) Prod_id(Primary key), Product_Name, Prefix and Slno(Auto incremented) i need to increment Prod_Id based on the product name field and prefix field (Input will be ...
0
votes
1answer
93 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
-2
votes
1answer
713 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 ...
3
votes
2answers
355 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
1
vote
1answer
110 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 ...
2
votes
3answers
112 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
3answers
54 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;' ...
1
vote
0answers
75 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` ...
0
votes
0answers
21 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 ...
0
votes
1answer
31 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
84 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
169 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 ...
2
votes
3answers
506 views

Getting 0KB of size file while backup mysql database using mysql dump

I am using below code to backup my database using php script but I am getting 0KB size file. How can I backup my full database with all routines and functions? $dbhost = 'localhost'; $dbuser = ...