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
44 views

Poor query performance in MySQL

I have a 10 million record table in a MySQL 5.5 InnoDB. It's running in a 16GB RAM server with good CPU and fast HD. When I run the following query SELECT DISTINCT knowledge_id, ...
0
votes
1answer
59 views

Slow MYSQL performance due to sub queries

Here is my query: SELECT BAL,NAME, (SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR , (SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < ...
0
votes
1answer
37 views

Annonymous user has no privileges, but still able to show databases?

Output of SHOW GRANTS FOR ''@'localhost'; GRANT USAGE ON *.* TO ''@'localhost' However, when I log in using my default user account, which is later defaulted to an annonymous account by mySQL as ...
0
votes
0answers
12 views

Performing a dump breaks replication

I have a master/slave setup in our production environment (MariaDB). I've found that when a dump is done on a schema that is heavily used, it causes the replication between the master & slave to ...
0
votes
0answers
32 views

What happens when an SSH session is terminated while a query is running?

I have a huge database on a dedicated server, whenever trying to modify the db I use PUTTY to establish an SSH connection and then do the work. Yesterday I started an INDEX query on an innodb table ...
0
votes
0answers
35 views

Alternative to TEMPORARY tables capable of re-opening in a query?

I use TEMPORARY table for calculation of temporary data, but TEMPORARY tables in mysql has a big limitation, which made it useless for me. A table cannot be re-opened in a query. This mean I cannot ...
0
votes
0answers
24 views

Query for multi-table search with concatenated fields

Thank you in advance for bearing with me, as I am new to database design and implementation. The Setup: I have a MySQL 5.5 InnoDB database with several tables, 4 of which are relevant to the problem; ...
0
votes
1answer
56 views

How to UPDATE MAX and MIN of a GROUP BY?

I have a SELECT query of a table with GROUP BY id col1 category 1 some 1 2 x 1 3 another 1 4 x 2 // should be removed 5 some 2 6 x ...
0
votes
1answer
59 views

Use string functions and arithmetic calculations in SQL query or by programming language?

I need heavy arithmetic and string functions for processing the data captured from mysql database. Which is preferred: mysql functions or programming language (e.g., PHP)? $result = ...
0
votes
3answers
481 views

Amazon RDS MySQL performance issues

I have published my website on Amazon EC2 (Singapore region) and I have used MySQL RDS instance for the data storage. Everything is working very fine except performance. It seems that all queries, ...
1
vote
2answers
71 views

Is column order reliable in UPDATE?

Can we reply on the order of columns in an UPDATE? SET @x = 0 UPDATE table1 SET col1=IF(col1 IS NULL, NULL, @x:=@x+col1), col2=IF(col2 IS NULL, NULL, @x:=@x+col2), col3=IF(col3 IS NULL, NULL, @x:=0) ...
0
votes
2answers
52 views

Grant access on a database to all users

I have a global database, as I all users should have access to it. Is it possible to grant permission on a database to all users? something like GRANT ALL ON my_database.* TO *@localhost; Even (for ...
0
votes
1answer
37 views

How can I efficiently search for all records from a given month, or a given year?

We have a couple queries that involve datetime fields. In particular, we are checking for things like SELECT field1, field2, ..., field20 FROM table WHERE MONTH(startDate) == 1 SELECT field1, field2, ...
1
vote
1answer
82 views

How to use LOW_PRIORITY for heavy queries?

I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources. I do not want them to stop end users from ...
0
votes
1answer
78 views

column auto updated after 24 hours in mysql

i have a table T1 with column : Name State time mon 1 2014-01-09 11:23:00 tue 0 2014-01-07 14:40:00 wed 2 2014-01-08 09:23:00 thu ...
1
vote
1answer
55 views

Query result set

I've 1 query written in our legacy system. (using MySQL 5.5) Now, with growing data - the below mentioned query taking huge time. In our system, we've somewhat 2,000,000,000 (2 billion rows) approx ...
0
votes
1answer
86 views

MYSQL - Problem with foreign keys on same table

In Mysql 5.5, I have a table, in which I store some informations about "events".. In order to have a chronological order, I made two columns that reference to the previous and to the next event. So I ...
0
votes
1answer
47 views

Few Insert queries rollbacked due to system crash

Well, this problem occured to me first time. Yesterday, one of our mysql system server crashed and when it restarted last few inserts were completely rollbacked. I am sure that they were proper ...
0
votes
0answers
55 views

Cannot find /etc/init.d file in opensolaris

While installing MySQL , I noticed a point, I mean to start the MySQL server manually , use the command cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql The error shown is : cp: ...
1
vote
2answers
53 views

Indexes slowing Insertions(Can't use Explain Insert to Analyse)

I have a MySQL database, I am parsing files from a log and inputting it inside. So far including a simple 4MB file in the database could take me about 4 minutes to insert ! and i have about 10,000 ...
1
vote
1answer
80 views

char vs varchar in mysql

I was reading the book MySQL high performance, it has mentioned that : CHAR is also better than VARCHAR for data that’s changed frequently, because a fixed-length row is not prone to ...
0
votes
0answers
35 views

I can't get MySQL server to stop running

The output from MySQL Workbench when I try to stop it is: Executing '/usr/local/mysql/support-files/mysql.server stop' 2013-12-23 21:08:29 - Stop server: ERROR! MySQL server PID file could not ...
0
votes
0answers
76 views

MySQL Load Avg High — CPU bound or IO Bound or Memory Issues

We are using MySQL 5.5 and the database is approximately 10TB. The load avg is constantly high during day time. Following are the configurations and a snap shot of a 'top' command. I wanted ...
1
vote
1answer
106 views

MySQL with high CPU and memory usage

here we have a VPS with 3.2GHz quadcore processor with 2GB of RAM and 4GB of SWAP which need to work with a high intensive legacy system (read: bad coded). The main problem is that the CPU (~80 - ...
2
votes
2answers
224 views

How to SELECT rows with time duration stored in varchar column?

There is a column in table defined like this : duration varchar(10) It has value for duration of a MP3 file stored in it like : 52:19 (Minutes : Seconds) How can I create a SQL query, for ...
1
vote
1answer
319 views

Query State hangs on sending data

For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why. This is the table creating script: CREATE TABLE IF NOT EXISTS `esp_game` ( `gameID` ...
0
votes
1answer
263 views

Unable to take backup of mysql database using Xtrabackup

I am trying to take mysql backup without any downtime but unable to take the backup using Xtrabackup . Actual Database is almost about 40+ GB in size. I am trying to first satisfy myself. Hence, I ...
3
votes
2answers
484 views

What is the meaning of tinyint(N)?

When we use an argument length with numerical data types, as far as I know this specifies the display width. I tried the following: mysql> create table boolean_test (var1 boolean, var2 tinyint); ...
1
vote
2answers
4k views

MySQL Installation error: Unable to update security settings

I tried to configure MySQL server 5.5 on Windows and this is what I get mysql-server-5.5-winx64:56 - Attempting to update security settings. mysql-server-5.5-winx64:100 - Unable to update security ...
2
votes
1answer
168 views

How to modify `proxies_priv` in MySQL

I recently received a warning from MySQL that stated: [Warning] 'proxies_priv' entry '@ root@serverdomain' ignored in --skip-name-resolve mode. I looked in mysql.proxies_priv and there is ...
1
vote
1answer
82 views

Upgrading MySQL 5.1 to 5.5 - All tables gone [closed]

I'm trying to upgrade from MySQL 5.1 to MySQL 5.5. I followed the instructions here: http://dev.mysql.com/doc/refman/5.5/en/windows-upgrading.html I also adjusted the Datadir. But after the upgrade, ...
2
votes
2answers
65 views

How to store logs correctly?

(This is somewhat a follow up on my last question) I am using MySQL 5.5 with innodb. My Log table consists of two columns. One for user number, and the other for a string consisting of IP, request ...
0
votes
0answers
159 views

What is the ideal connection pool size for RDS MYSQL?

I have a tomcat server hosting REST APIs. It is connected to a MySQL (RDS Instance) server on the backend. A majority of the operations are write operations (Around 75%). I'm using the Tomcat's ...
2
votes
1answer
1k views

InnoDB: Error: Table “mysql”.“innodb_table_stats” not found after upgrade to mysql 5.6

I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup I found a possible solution here, but it does not seem official. ...
4
votes
2answers
820 views

Does a TRIGGER improve the performance?

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too? Replacing two or three queries as INSERT INTO table1 ...
0
votes
2answers
120 views

Warning before issuing delete/update command in mysql/oracle , then executes

Is there any utility , function either in mysql /oracle that if i issue some delete/update command wrongly , a message must prompt at command_line* (if possible in ubuntu) asking whether "you sure to ...
1
vote
1answer
46 views

addition in datetime column during insertion in mysql

i have a table with below 5 columns..timestamp is data type for timedate related column : Region event_time start_time mid_time end_time India 2013-11-03 13:00:00 America ...
1
vote
1answer
41 views

MySQL geospatial query optimisation suggestions

I am new to DB Administration. Trying to sort out this query which is the heart of our application. I am searching google on similar questions, but would like an experts opinion on my particular ...
2
votes
0answers
87 views

Understanding Indexes and using them better

I've been spending some time trying to optimise our MySQL queries that we use which essentially joins one table with 2 million rows onto another which has 1-2 million. I know MySQL is able to easily ...
1
vote
1answer
56 views

Get rows in a range of time

I have a table with two columns that stores TIME type values. This is the CREATE statement: CREATE TABLE `sdriving_turno` ( `idturno` bigint(20) NOT NULL AUTO_INCREMENT, `idempresa` int(10) ...
0
votes
2answers
129 views

avoiding unique key collisions across tables

This might be related to the idea of data warehousing, but i'm not sure, though it is extreme theory. We have a table where records get written to first, let's call it tbl_recording which looks like: ...
1
vote
1answer
252 views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
0
votes
3answers
182 views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
1answer
134 views

Datetime Difference in MySQL when format is Varchar

I have two columns in a table: STOP and START, of type varchar. I want to have the difference between both columns, and I'm using : SELECT DATEDIFF(stop, start) AS difference from T1; It is ...
0
votes
2answers
587 views

What is the difference between NULL and \N while loading data from a csv using load data local infile

I have a table which has four fields create table test_hc1(cola int,colb int,colc varchar(20),cold int); Now, I am trying to load following data 1,2,\N,4 and the third field in table is coming ...
0
votes
1answer
499 views

MySQL - Server collation utf8_unicode_ci vs table collation utf8_bin: compatibility and performance

I'm not sure whether this belongs to SE DBA, but it sounds more logical ... Our MySQL 5.5.32 on Ubuntu 12.04 64 uses utf8_unicode_ci for the Server collation and some tables, like phpBB3, uses ...
1
vote
1answer
162 views

Squeezing Maximum Out Of My Server

I have a dedicated server only built to hold MySQL databases. Currently I have a database that has 173 million records and increasing by 5-15+ million records very day. My question is what and why I ...
2
votes
2answers
3k views

What max_allowed_packet is big enough, and why do I need to change it?

I have MySQL (5.5) in master-slave setup and created another slave server. I stopped the original slave, dumped the data, copied and reimported and it worked fine. I noted the master_log pos of the ...
0
votes
1answer
78 views

Update the host field in Grant privileges in mysql

I have an existing table and I want to update host part in privileges using Grant command mysql> show grants -> ; ...
0
votes
0answers
34 views

Is there any way to set server-wide and schema-wide default row formats for InnoDB tables in MySQL?

Is there a way to set server-wide and database-wide default row formats? Currently it seems that I must specify ROW_FORMAT in CREATE TABLE or ALTER TABLE statements. Am I reading the documentation ...