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

learn more… | top users | synonyms

2
votes
2answers
36 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
565 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
3answers
194 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
125 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 ...
-1
votes
1answer
1k 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
4answers
577 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
155 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
163 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
62 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
88 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
26 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
39 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
111 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
249 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
4answers
743 views

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

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 = ...
3
votes
1answer
332 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 ...
1
vote
0answers
56 views

crossplatform mysql_install_db script in mysql 5.5.34 on Windows

I downloaded mysql-5.5.34-win32.zip and found out there is mysql_install_db.pl script. However mysql 5.5 documentation says: mysql_install_db is a shell script and is available only on Unix ...
1
vote
0answers
239 views

Upgrading from MySQL 5.5 to 5.6 - timestamp issue?

I'm looking at upgrading my Percona Server setup from 5.5 to 5.6. Reading the upgrade docs from MySQL sounds mostly straightforward except for anything involving time/date columns. As far as I can ...
0
votes
2answers
577 views

Cannot stop MySQL 5.5 replication threads

We have a master-master replication setup using MySQL 5.5. On one of the servers I am trying to stop the slave thread, but without success. To ensure that the replication does not connect to a real ...
1
vote
2answers
203 views

MySQL replication: slave not updated

This has been asked many times over but we really cannot get a simple Master-Slave Mysql (5.5.25) replication to work for our two dbs (FatTree and Torus). And no trace of error in the logs. The ...
0
votes
2answers
151 views

mysql forbidden database names

I was wondering whether there were a list of forbidden (reserved) database names in mysql. For instance, mysql is obviously one. But is there a determinated list? I don't want to cover 99% in my ...
1
vote
2answers
560 views

Can I disable an account in mysql after x failed login attempts?

The question is in the title. I would like to disable accounts after x failed login attempts. Is it possible? Thank you
1
vote
1answer
2k views

How to create trigger to update timestamp when record is updated or inserted in another table

I have a table t_task and a table t_taskaction with a foreign key to t_task. I would like to update timestamp from t_task that is refering the foreign key form t_taskaction with every update in ...
0
votes
0answers
89 views

Is this a normal query time for a left outer join type?

I have Employee Table - 80K records about 100 fields I have an app table - 8K records and 30 fields Then a subset of employee table also stored as table that has our divisions subset - call this ...
0
votes
2answers
64 views

Mysql replication from existing slave to new slave

I'm hoping you experts can help advise me, I've got this topology: Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB Backup server is a slave of Live DB, and Test DB is a slave of ...
0
votes
0answers
89 views

Mysql query getting hung!

I am using mysql 5.5. I create following temporary table: CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s ( `count` int(10) NOT NULL, `m_class` enum('SMS','EMAIL') NOT NULL, ...
2
votes
0answers
3k views

Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example: $ mysql -u root -p --column-type-info Enter password: --- Copyright and help message snipped for brevity --- ...
1
vote
1answer
60 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, ...
1
vote
1answer
62 views

Count/Statistic for number of SHOW INDEX queries in MySQL/MariaDB 5.5 [closed]

We have a lot of SHOW INDEX FROM queries coming from our ORM layer. Usually there is a mechanism to prevent this but it doesn't seem to work at the moment. To better analyze the problem it would be ...
0
votes
1answer
100 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
84 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
2answers
123 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 ...
0
votes
0answers
240 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
1answer
164 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
364 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 = ...
1
vote
3answers
444 views

What happens when we take a timediff between timestamp and datetime

Say I have following table in mysql (5.5+): CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL, `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ...
0
votes
1answer
67 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
2answers
112 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) ...
1
vote
1answer
1k 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
1k 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 ...
1
vote
1answer
87 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
2answers
90 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
3k 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
0answers
136 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: ...
0
votes
1answer
65 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 ...
1
vote
2answers
123 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 ...
0
votes
2answers
208 views

Uninstalling MySQL on Solaris

I have installed MySQL 5.5.31 in Opensolaris. When I tried starting the MySQL server, it says bash: mysql: command not found So, I decided to uninstall the whole lot and try doing again. But I could ...
1
vote
1answer
344 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
50 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
175 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 ...