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
57 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) ...
1
vote
1answer
441 views

MySQL equivalent of WITH in oracle

I need the mysql equivalent of the WITH clause in Oracle. Please help. Thank You
1
vote
0answers
29 views

MySQL - Randomly long DESCRIBE queries

I have several queries like this (running on MySQL 5.5): DESCRIBE `TABLE`; And it randomly takes 5-10 seconds on my production servers. We have an ORM that caches this data once per day (per web ...
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 ...
-1
votes
1answer
43 views

How do I create a trigger that cascades new rows into another table?

I am having a table name link with two columns link1 and link2 and another table name hide with four columns hide1, link1,link2 and hide2. If i insert data into link table the data should insert into ...
1
vote
0answers
114 views

innobackupex: What causes Slave_open_temp_tables did not become zero after waiting 15 minutes?

As you might know, in order to assure a consistent replication state, the slave should be backup only when there are no open temporary tables: ...
0
votes
0answers
90 views

Total of select by all current session and by date

In MySQL : This query allows us to have the total number of "select" on the server (query 1) : mysql> SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE variable_name like ...
0
votes
0answers
21 views

How to handle constant update of geo spacial lat\long update by an web service?

I have a table which contains person details and their current geo-spacial lat\long. I am using default engine as innoDB Lat\long will be updated constantly through web service. When the user hits ...
2
votes
1answer
47 views

Make sure slave has finished reading from master

I am planning to have a master-slave system in which occasionally the master and slave will reverse roles. To do this, I will stop further updates to master, and redirect traffic to the slave. But, ...
0
votes
1answer
73 views

How we take dump with batch of records option?

we have 30 GB database.we want to take backup of this database. here the question is we want to take the backup batch of every 10k records. How we can take batch of records backup using mysqldump
1
vote
2answers
3k views

MySQL using too much CPU

I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server: CentOS 5.9 24gb Ram 8 ...
1
vote
1answer
95 views

Is there any query to fetch the a single row data in chunks?

The data stored in a row is just like "Phone Number:--- xxxxxxxxxxx Call Type:--- MISSED Call Date:--- 2013-06-13 Call duration in sec :--- 0 Phone Number:--- xxxxxxxxxxx Call Type:--- OUTGOING Call ...
0
votes
0answers
21 views

mySql Event gets expired on closing of the command prompt

As I am quite new to server side, I tried to create a event using CREATE EVENT enetname ON SCHEDULE AT '2013-06-13 18:00:00'+INTERVAL 30 SECOND DO update tablname set status= 'Expired' where ...
0
votes
0answers
379 views

mysql deadlock while updating: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction update

I am using the mysql version 5.5.29 in my production server. while executing set of update statement inside the procedure it is throughing the following error: ERROR 1213 (40001): Deadlock found when ...
0
votes
0answers
15 views

Get turno.tipo, maquina.patente and operador.nombre and status, complex query

I've this DB design I'm trying to build a query where I should get turno.tipo, maquina.patente, operador.nombre and detalle_emisores.revisado` but it's a bit complex to my knowledge. I started this: ...
0
votes
1answer
128 views

MySQL - What is the diffrence between ClearText authentication and PAM authentication plugins?

PAM Authentication: PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory. Cleartext Client-Side ...
0
votes
0answers
21 views

MySQL - What is the diffrence between ClearText Authentication and PAM Authentication MySQL?

PAM Authentication: PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory. Cleartext Client-Side ...
1
vote
2answers
225 views

Multiple table rows in one row

I have a table from where want to get such classes where 3rd position candidate got more marks than difference of 1st and 2nd. class position name total_marks 10 1 a 100 10 2 ...
1
vote
1answer
3k views

BEFORE INSERT trigger in MySQL

I am new to MySQL and I'm having problems creating a BEFORE INSERT trigger. I get an unexpected END error. I have a table named verlof_aanvragen with a column datum (and 6 more columns). What I ...
0
votes
1answer
128 views

What's the practice to generate long user id like those on Facebook?

Now I am about to launch a site and I have trouble coming up with a way to create long user id(e.g. 4000002). Do I set id to auto-increment(Starts at 0) and then add something(e.g. 4000000) to this ...
0
votes
0answers
99 views

Having trouble connecting tungsten replicator from EC2 to RDS

I am testing using Tungsten Replicator to help migrate large databases to RDS. I have no trouble setting up the service on the EC2 "master". I am using a second EC2 instance as a kind of relay server ...
0
votes
1answer
63 views

Select unique value whereas the time is highest in the most optimal way

Given a simple, with a text and time column, I want to select X unique values from the text column, whereas that row contains the highest value for time. INSERT INTO `test` (`id`, `text`, `time`) ...
1
vote
1answer
2k views

How to run a SELECT query within while loop in PHP?

Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as $result1 = $mysqli->query("SELECT * FROM table1"); while ( $row = $result->fetch_assoc() ) { if ( $row['X'] == ...
3
votes
1answer
310 views

Getting Random Sample from large database MYSQL (No Auto Inc Field)

You were so helpful with my last question, I thought id throw another one at you thats stumping me. Basically I got a table, requirements from company was it was all supposed to be 1 table so I got ...
1
vote
1answer
155 views

Is there a way to find the least recently used tables in a schema?

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...
0
votes
1answer
90 views

Am I wrong in table design or wrong in selected index when made the table?

I've build web application as a tool to eliminate unnecessary data in peoples table, this application mainly to filter all data of peoples who valid to get an election rights. At first, it wasn't a ...
0
votes
1answer
278 views

HOW to “SUM” and “JOIN” for single query of a poll results?

I have a poll database as CREATE TABLE poll_answer answer_id, answer varchar(255), poll_id int(11), FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE, PRIMARY KEY(answer_id) ) ...
0
votes
2answers
248 views

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
0
votes
0answers
60 views

Replicate database without Internet connection

I'm working on a company where I have a central DB and several agency databases. See the diagram at here. All the databases are identical, just the data can change and in some cases. I've no internet ...
1
vote
2answers
367 views

MySQL+Web-App Performance Issues using different servers

We are having a performance issue with our MySQL servers that does not make any sense. I have read countless articles from many people (mostly Percona) and have made my.cnf tweaks. We have even manage ...
2
votes
1answer
84 views

MySQL Subquery hangs / terrible perfomance

I am using MySQL 5.5.16 @ CentOS. Please have a look at this query: SELECT inventory_ID, ( SELECT price_brutto FROM _inventory_prices ...
0
votes
1answer
487 views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...
0
votes
1answer
83 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
1
vote
2answers
310 views

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got ...
0
votes
1answer
776 views

ERROR 13 (HY000) When Creating Database

I'm trying to create a database and when I type in CREATE DATABASE epik; I get the error ERROR 13 (HY000): Can't get stat of './epik' (Errcode: 13) I am running kubuntu 13.04 with mysql 5.5.31.
0
votes
1answer
123 views

Do certain string patterns affect query performance on a VARCHAR column?

I am a beginner on MySQL Administration and performance problems. Now this is confusing me. I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, ...
1
vote
1answer
63 views

ORDER BY optimization issue

I have a query: SELECT user_details.id , user_details.first_name , user_details.last_name, user_accounts.name account_name, jtl0.account_id account_id, user_details.title , ...
0
votes
1answer
303 views

Errors while creating multiple mysql-5.5 instances

I have installed 3rd mysql instance on my testing server. 2 instances already running without any issues. When I installed 3rd instance by mysql-5.5.30 zip source, it installed successfully but when ...
3
votes
1answer
75 views

skip columns while using LOAD command mysql

I have a csv file having 5 columns and i want to load only one out of that, is there a way i can do it. I want to use LOAD statement of MySQL 5.5 Any pointers are welcomed, help would be much ...
0
votes
0answers
46 views

Commit count while using LOAD statements in MYSQL

I have a very big text delimited file, I am using Mysql LOAD statement but i want to put some commit count, for example if i put commit count 1000 then after inserting every 1000 rows it should commit ...
1
vote
3answers
1k views

How do I use subquery on the same table in MySQL?

I have a query like this which takes a really long time to run. The table is around 4 million rows. DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now; I'm hoping I could ...
2
votes
1answer
259 views

datetime vs timestamp in mysql 5.5

What is the difference between datetime and timestamp in MySQL with respect to data being inserted from different timezones? Does anyone have any clues on this? I have a situation where I am trying ...
1
vote
1answer
137 views

Unable to use mysql_upgrade:

I updated mysql recently and found problem related to privileges and backup. When I am using mysql_upgrade command it says- mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check ...
-1
votes
1answer
186 views

Having too many connection problem

We have an magento store and having 9K products and 2 store views the trouble is we are getting too many conection error while the site is in normal loads. 1) We have 100 connectoin open in mysql. 2) ...
0
votes
1answer
317 views

Dealing with empty strings while loading a table from a CSV

I am trying to load a CSV file into a table. The CSV file contains some empty strings for example: 1,2,,4 I want to load the empty field as null. One way I know is to use \N i.e: 1,2,\N,4 but it ...
0
votes
1answer
1k views

In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

On Windows, I upgraded from 5.1 to 5.5 no problem. Copied my 5.1 data folder into my 5.5 instance Started mysqld skipping grants Ran mysql_upgrade All good, but going from 5.5 to 5.6: Copied ...
0
votes
0answers
21 views

Should store procedures be cached?

Should store procedures be cached in MySQL? If so, How long it is stay in cache? In my case, When I call one stored procedure first time, it is giving me results in 1 second, after that it gives me ...
5
votes
1answer
6k views

Should I increase max_connections in AWS RDS t1-micro for MySQL?

I have an AWS RDS t1-micro running MySQL 5.5. It gives me too many connections error. I checked and it allows 34 maximum connections concurrently. What I have read is that i can increase this max ...
1
vote
1answer
367 views

Table corruption: How to perform Innodb Checksum checks in MySQL 5.5 for Windows?

Having a corrupted Mysql 5.5.31 (Windows) database, my question relates to the the top solution provided in How do you identify InnoDB table corruption? , more precisely to the following script that ...
0
votes
0answers
39 views

Select query very slow [duplicate]

I have a query which is take less then 0.15ms to execute on mysql on local box. Same query takes 0.348 to 0.468ms to execute on RDS medium instance. Both has same table schema, indexes. Infect on my ...