0
votes
1answer
21 views

query execution time in mysql

I have run the query in mysql which consists of approximately 33million records when i have executed the query select population1 < 200000 from city; it shows like this after execution ...
0
votes
1answer
15 views

change character set in mysql permanently

I want to change following parameters to utf8mb4 so that mysql can store emoji characters and special language characters. but when i try running following statements one by one SET ...
0
votes
2answers
32 views

Using a join to return multiple values of the same column?

I have two tables user Rank In the Rank table, I saved rank_id and supervisor_id. In the Rank table, I stored the rank name. The rank_id and supervisor_id are foreign keys from the Rank table. ...
-1
votes
1answer
31 views

Btree index and Bit Map Index [duplicate]

Please clarify the difference between BitMap Index and Btree index. I am using mysql version 5.5.37-0ubuntu0.12.04.1. I can able to create Btree index but unable to create the same, Please find the ...
0
votes
1answer
39 views

Could not access database

I have been trying to setup a local instance for a new application. I am new to MYSQL and I cannot understand how to actually connect this database to the actual web application sitting in the ...
1
vote
2answers
19 views

getting one day timestamp in mysql

Please consider the following stored procedure I have: ORIGINAL Stored Procedure: -- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments ...
0
votes
1answer
35 views

How performance schema in mysql 5.6 improve performance?

Since MySQL 5.5 performance_schema database has been added likewise information_schema. As we know that tables in information_schema contains statistical information like tables, plugins, partitions, ...
1
vote
2answers
54 views

Are there benefits to using more than 4 CPUs for MySQL?

We are running MySQL 5.5 on linux under VMWare, running on 2 CPUs. We are planning on increasing this to 4, but we have un-used CPUs available, and I'm wondering if there is any benefit to increasing ...
2
votes
2answers
52 views

How to monitor InnoDB Disk I/O using Server Status Variables?

This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? " As mention in my previous question i want to count total number of disk i/o per query, but ...
1
vote
1answer
37 views

Safely changing innodb_buffer_pool_size on MySQL replication master?

We have a MySQL server which is a replication master. We need to change the innodb_buffer_pool_size and innodb_flush_method to (hopefully) improve performance as the database has grown. What is the ...
0
votes
1answer
50 views

Dedicated mysql server slow response [closed]

I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ... With pingdom.com test i have for server: 10 sec and simple hosting: 2sec is ...
0
votes
1answer
28 views

Two MySQL requests at the same time - Performance issue [closed]

I have a MySQL server with many innodb tables. I have a background script that does A LOT a delete/insert with one request : it deletes many millions of rows from table 2, then insert many millions of ...
0
votes
0answers
15 views

output from mysqltuner.pl (from mariadb.service)

While running mysqltuner.pl script, I'm getting following message: [OK] Currently running supported MySQL version 5.5.35-MariaDB-log [OK] Operating on 64-bit architecture -------- Storage Engine ...
0
votes
0answers
11 views

mysql service failed to start when datadir changein ubuntu

I have dual Operating system on PC, windows 8 and ubuntu 14.0, and I am mostely using ubuntu because my MySql database is hosted on ubuntu. Recently when I am trying to restore my database, which is ...
0
votes
2answers
42 views

MySQL Community Edition Capacity

I am using MySQL Community Edition on Amazon RDS service. It is a very large database, 2/3 tables has about 10 millions records and 4-5 tables has 7-8 millions records. Questions How much capacity ...
0
votes
0answers
24 views

tables don't exist after unmounting and remounting external drive where database was installed

I want to create a database that's too big to keep on my own machine, so I'm trying to create one on an external hard drive. It works up until I unmount and remount the drive, at which point show ...
1
vote
2answers
32 views

MySQL user without SUPER privilege

This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question. When creating users in mysql using grant all ...
0
votes
1answer
22 views

ON DELETE CASCADE pseudo delete

For data integrity ON DELETE CASCADE is necessary but it's really dangerous when a delete affects like a Chain Reaction! I know one way to prevent this is to make a pseudo delete by putting a ...
0
votes
1answer
33 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | ...
2
votes
4answers
219 views

Aggregate query optimization

Could you please help me to optimize this query - currently it takes ~4 minutes to run this query. It looks like keys: ip and timestamp are non-unique so shall I add some indexes? mysql> EXPLAIN ...
1
vote
1answer
57 views

Master-Master Replication Duplicate Key

I have set two servers master-master replication mysql , i manage to set successfully but the problem is the duplicate key i got MASTER 1 Configuration /etc/my.cnf [mysqld] server-id=1 bind-address ...
0
votes
1answer
54 views

Whether to use or not to use: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

In one Database table, created_at & updated_at are defined as: `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP `updated_at` timestamp NOT NULL DEFAULT ...
-1
votes
1answer
45 views

Query to retrieve data based on several where conditions ( Not off Topic , Answer provided below is right ) [closed]

Database : Mysql 5.5 i have a table T1 containing 4 columns : No. , Height , start_time , end_time In column height i have 3 distinct values only : Small Medium High Now i ...
0
votes
0answers
19 views

Log-slave-updates in multi master replication using Tungsten replicator

I m setting up a system as follows with 2 masters and 2 slaves. M1 <----> M2 (Tungsten Replication) M1 ----> S1 (MySQL Replication) M1 ----> S2 (MySQL Replication) I have enabled the ...
1
vote
1answer
30 views

Is there a product to auto-build indexes based on slow queries?

I'm looking to either find, purchase, or write a script to identify slow queries, identify potential missing indexes, and when needed create the appropriate index within a reasonable amount of time of ...
0
votes
0answers
36 views

Upgrade from mysql 4.1 to mysql 5.5

I was going through upgrade process of mysql server. It is mentioned that to upgrade from 4.1 to 5.5 we need to upgrade step by step like first to 5.0,5.1 ...5.4 and then 5.5. I have some questions : ...
2
votes
0answers
20 views

MySQL slow query log not logging all slow queries [duplicate]

I am trying to analyze the hibernate query performance in our application. I tried logging all hibernate queries which take more than 100ms execution time from application side as well as using mysql ...
0
votes
1answer
25 views

MySQL database configuration for fastening the select and insert queries

There are several DBEngines are available for MySQL DB. Currently I'm using InnoDB. My actual problem is, I have a table that consist of millions of data. Therefore the select queries that given to ...
0
votes
0answers
11 views

Will performing a physical copy of a MySQL database on Windows to a MySQL instance on Linux still perform? [duplicate]

I'd was told you can copy text files and such from Windows to Linux just fine, I was wondering if I could copy my innodb files such as .ibd and .frm files to a mysql instance running on Linux, fire it ...
0
votes
2answers
17 views

Joining on 3 different keys as they're set

I have these tables: TABLE vessels ID int PK organization_id int FK fleet_id int FK name VARCHAR TABLE settings ID int PK organization_id int NULL FK fleet_id int NULL FK vessel_id int NULL FK ...
0
votes
0answers
27 views

stress testing mysql - maximizing qps for a single user

I am running a stress test and I am trying to churn out as much qps as I can for a single user. I know this is a loaded question so I will try to be more specific. Trying to target 30,000 qps with a ...
1
vote
1answer
30 views

Will composite indexes or unique indexes help with query performance?

I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The ...
0
votes
0answers
18 views

How can I determine the performance (rows/second) for a certain query on one of my tables?

I want to find the performance (rows/second) for an UPDATE query on one of my tables so I can adjust my WHERE clause to select an appropriate number of records. The table has around 10 million rows. ...
1
vote
1answer
50 views

Slow queries InnoDB occasionaly

I have the next table: CREATE TABLE `sessions` ( `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL, `session_expires` int(11) NOT NULL DEFAULT '0', `session_data` varchar(10000) COLLATE ...
0
votes
0answers
17 views

How to ensure accents are view as different characters when adding to unique indexes?

What can I do to ensure MySQL views the following as two different values? Bee Bée When I try to create a unique index, it fails because they're the same value. I looked at the following thread, ...
1
vote
2answers
136 views

How to outer join multiple tables to retrieve uncommon records

I'm trying to do an outer join in MySQL to identify records not common to 3 tables. Here's my current effort http://sqlfiddle.com/#!2/02b92/30 I'm trying to retrieve the records not common to all ...
0
votes
1answer
42 views

Is there a way to truncate the microseconds of a CSV value when importing data using LOAD DATA INFILE?

I'm loading data using LOAD DATA INFILE in 5.5 where the value includes microseconds. I must use DATETIME data type. Currently, I'm using regular expressions to remove them from the CSV prior to ...
0
votes
2answers
72 views

Buffer pool above 90% utilized causes MySQL to crash

Without getting deep into the architecture and setup, is it common behavior for MySQL to act in this manner? EDIT I'm using Rolando's script to identify how full the innodb_buffer_pool is. SELECT ...
0
votes
0answers
11 views

How to recover from a corrupt relay file on a slave?

I recently had a master shutdown on its own. In this case, the slaves all recovered fine. However, I starting thinking about how to recover if the master shuts down and for some reason the relay log ...
0
votes
0answers
22 views

How to return nothing but the number of rows and execution time?

I'm trying grab the # of returned rows and execution time rather than the actual records. Or,at the very least discard the results. Any tricks to do this? I could use outfile or SP and then parse it, ...
0
votes
1answer
110 views

How to resolve “Error log file size is increasing in mysql 5.5” issue w/o restarting mysql

I have an Ubuntu server with MySQL 5.5 installed. In /var/lib/mysql a .err file is kept on updating with these warnings only: [Warning] IP address '234.22.11.221' could not be resolved: Temporary ...
0
votes
0answers
29 views

How to decrease load time by chunking large file using Percona pt-fifo-split?

I'm loading data into a fresh instance which takes ~48s to load a single file (1G; 2.5m rows). To decrease load time, I tried chunking it into 100k rows using Percona's pt-fifo-split. Unfortunately, ...
0
votes
1answer
36 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
44 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
376 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
39 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
60 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
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
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
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 ...