MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
63 views

Is normalization advisable on transactional data?

I have read a lot of articles about normalized vs denormalized schemas, but I find very little mention of the cost of row insertion. I am considering the impact of converting a 100% denormalized ...
0
votes
1answer
42 views

JOIN on 3 tables being slow

everyone. Can't figure out what indices are missing or how to rephrase my query. I created about a milllion of test entries in each table to see performance. Query takes about 1.5 seconds which is ...
1
vote
1answer
31 views

Adding time to an event that runs on a weekly basis

I have this event that runs every Thursday CREATE EVENT le_drop_database ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_DATE + INTERVAL 3 - WEEKDAY(CURRENT_DATE) DAY DO BEGIN drop database carbon; ...
0
votes
0answers
13 views

RAID configuration for mysql [closed]

I attached 4 10G disks to mysql Server and I used RAID 0 but still same mysql performance the same, Do I have to change configuration in my.cnf to let mysql know about RAID 0
1
vote
2answers
132 views

RAID configuration for mysql

I attached 4 10G disks to mysql Server and I used RAID 0 but still same mysql performance the same, Do I have to change configuration in my.cnf to let mysql know about RAID 0. this is my mount ...
0
votes
1answer
45 views

What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE?

What's best performance wise, two separate UPDATEs: UPDATE table1 SET enabled = 0 WHERE code NOT IN ( SELECT id FROM table2 ); UPDATE table1 SET enabled = 1 WHERE ...
0
votes
0answers
13 views

MySQL - Moving binary logs to Network Server On Windows

Is it possible to move the Binary logs for MySQL (on Windows), to a different physical server? My MySQL Databases are MySQL 5.5. (Bearing in mind that ultimately this will be a Master DB, and there ...
1
vote
1answer
33 views

Table is dropped but ibd file still exists

I was dropping some unwanted tables I have in the MySQL. When I run a drop table, its showing table doesnot exists. But when a check in the data directory, the .ibd file exists and its almost 1GB. How ...
0
votes
2answers
29 views

Rank variable value in MySQL is always being set to null

In a sample procedure I am trying to find the rank the of the cities according to the number of offices. I have a Location Table and an Office Table and a location can have multiple offices ...
1
vote
1answer
37 views

Should I drop this column or is there a query that can achieve a results table without duplicate columns?

I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information. ...
0
votes
0answers
11 views

long mysql insert and update script user variables appear to be lost while running script

I have a mysql database table called 'dash_something' dash_something ------------- date, DATE *this is the date of the summary type, CHAR(10) entity, CHAR(25) number_of_emails_1, INT ...
0
votes
1answer
40 views

MySQL JDBC does not behaves as the MySQL Workbench

I'm running the following from the MySQL Workbench, and from my local tomcat using jdbc. START TRANSACTION; CREATE SCHEMA IF NOT EXISTS `ROOT`; CREATE TABLE IF NOT EXISTS `ROOT`.`all_tables` ( ...
1
vote
0answers
39 views

Many small queries or one single query

This is not exactly a question, more of a survey. Here at BlackBean we have a time series aggregation and analysis platform built on top of MySQL, where each client have your own database, and each ...
1
vote
1answer
31 views

Indexing the difference between two columns in MySQL

Is there a way to optimize/index a query that sorts the difference between two columns. I have a modest sized table of 5 million rows that I'm trying to do the following: SELECT pk_column, column_x, ...
0
votes
3answers
50 views

PostgreSQL equivalent of MySQL query variables?

Is there a straightforward way to adapt these types of MySQL queries to PostgreSQL: setting variables in MySQL like set @aintconst = -333 set @arealconst = -9.999 It seems not. Assigning ...
0
votes
1answer
66 views

queries for hierarchical data

This table is a questions and answers CMS and i want to know how data is fetched from these tables. +------+----------+-------+ | id | parentid | type | +------+----------+-------+ | 1 | 0 ...
1
vote
1answer
107 views

Adding foreign keys constraint on databases used

We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all ...
8
votes
2answers
239 views

Why are simple SELECTs on InnoDB 100x slower than on MyISAM?

I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy. I copied (description follows) ...
0
votes
1answer
65 views

Speed up a MySQL BETWEEN query

SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind, dateFrom, ...
0
votes
1answer
53 views

MySQL Replication: Seconds Behind Master super high

I've set up a slave db server for my production database, but when I checked the show slave status, I noticed a super big number in seconds behind master. This is the output: ...
1
vote
1answer
32 views

InnoDB deadlock with INSERT .. SELECT

I am trying to understand why a deadlock occurs when two workers in parallel are running INSERT .. SELECT queries on a table which has both 1) auto incremented primary key, 2) a unique index on ...
0
votes
0answers
33 views

Storing DB credentials in an application source code?

I am making an app which connects to a DB, and the app will have plenty different userslogins. Is it a good design choice to have a specific DB login/password for the application, using which the ...
0
votes
0answers
14 views

How to migrate a mysql server in production to Percona XtraDB Cluster

I need some help : I have a mysql server in production and i want to migrate all the database to an existing cluster (percona xtradb). For now , my idea is : 1) Lock all tables 2) do a dump per ...
0
votes
1answer
17 views

I cant figure out the error probably with the date field in this mysql stored procedure

This is my empDetails table I am trying to create a stored procedure on. mysql> describe empDetails; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
0
votes
0answers
43 views

Double subquery is very slow, but independently they are fine

I have this query: SELECT mac, pid, node_category.name, status, computername, user_agent, detect_date, regdate, unregdate, last_dhcp, node.notes, (SELECT ip FROM iplog WHERE iplog.mac = node.mac ...
1
vote
1answer
15 views

MySQL - reformat a column value dependant on existing value

Am needing to provide a concatenated field from a select query which is dependant on existing values. SELECT `delegate`.`FirstName` AS `First Name`, `delegate`.`LastName` AS `Last Name`, ...
0
votes
1answer
26 views

Recovering MySQL root password (XAMPP)

I posted this on StackOverflow and was told to come here. I have an XAMPP MySQL installation and want to recover the root password. I am aware that questions have asked this already, however they are ...
0
votes
0answers
19 views

MySQL master/slave replication - replicate all tables

I am doing a master/slave replication, but getting errors about my tables does not exist. Is there not a way where i can make the whole table structure replicate aswell? Thought it did on default tbh. ...
0
votes
1answer
36 views

Proposal: MySQL blob handling revision [closed]

Why does MySQL store blobs in the direct table rather than off to the side so that if it needs to read the corresponding stuff it will read the stuff off to the side? Essentially it would create its ...
0
votes
0answers
35 views

Unresponsive MySQL database

I've setup a MySQL server on Ubuntu machine. When running a heavy query (the table has over 1M rows), the database becomes unresponsive. Upon connection it returns: Can't connect to MySQL server ...
0
votes
0answers
19 views

Optimised way to search over 2 milllion poi data in mysql

We have over 2 million poi data in our mysql db. At any time we need to find what is the nearest point based on the following formula found at this link http://sqlfiddle.com/#!2/abba1/4 select *, ( ...
0
votes
1answer
35 views

SymmetricDS not using Lookup Transform on primary key

I'm syncing records between two MySQL databases using SymmetricDS, but need to keep the primary key (fieldname = "id") fields unique. Symmetric doesn't seem to want to obey the "lookup" transform_type ...
1
vote
0answers
44 views

MySQL 5.1 High Cpu usage on CentOS 6

I am running MySQL 5.1 on a CentOS 6 server with 16G ram. I got also apache on it. MySQL is constantly using too much cpu. top - 17:40:36 up 112 days, 12:13, 1 user, load average: 1.24, 1.72, 1.82 ...
0
votes
1answer
26 views

Useful guide & FAQ for Application+SQL [closed]

Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know ...
0
votes
0answers
61 views

Change a query so it no longer takes 20 mins and uses a 93G tmp table

Edit: So how would I go about changing this query to something more sane (given that I can work around the hibernate madness) This might be a whole different problem, but Hiberate has created a ...
0
votes
1answer
50 views

Can a primary key of one table be a foreign key of another table?

I am working on a scenario like this : I have an employee table which has columns like this Employee_ID First_Name Last_Name Employee_Designation Also I have a Joining_Vital table Employee_ID ...
2
votes
1answer
28 views

Innodb DB Consuming IO even when no operations are being done

I am currently doing some stress testing where I am pushing inserts to the database as fast as I can. The simulation inserts 10000 records per second. After inserting around 200 million records I ...
2
votes
2answers
209 views

“All joins can be rewritten to sub queries”, is this statement true?

I wonder if there any case that a join can not be converted to a sub-query. BTW: I am not discussing advantages and disadvantage of using sub-query or using join.
1
vote
1answer
40 views

How to change rows to columns in mysql?

I have data as following(it is just an sample, I have 100+ different types.): advertiser_id activity_type counts 1 1 2 1 2 5 2 1 ...
0
votes
2answers
45 views

Retrieving row ranking in a table

I'm aware there are already many questions alike however none of those answers helped me achieve row rankings in a table. I have a table set up as so: ...
0
votes
0answers
13 views

mysqldump replace option - ubuntu / mediawiki

I know this is probably a silly question, but I'd like to be sure, rather than make assumptions. There's the option to use 'replace' in a mysqldump, which uses the 'REPLACE' rather than the 'INSERT' ...
3
votes
0answers
104 views
+50

How to setup complex multi-column index for massive table

I've searched what I could search on Google, SO, etc and haven't found an answer that would seem to fit what I'm looking for. I'm hesitant to make a post about this as I'm sure the answer is out there ...
0
votes
1answer
39 views

Can't restore from mysql dump file

I used mysqldump command to export/dump .sql file with rows like: String cmd1 = mysqldump + " -h" + host + " -u" + username + " -p" + password + " " + "--compact --no-create-info " + "--where=ID=" + ...
1
vote
1answer
29 views

mysqldump - select table in database only if it contains a specific string

(I'm new to Database Administrators, and am not a Database Administrator. However I originally asked this question on Stack Overflow (here) but didn't get any answers. Sorry if this is not the right ...
1
vote
2answers
47 views

0xDBE how to connect over local socket?

I want to connect to mysql over socket. This is what I'm trying to do: and it's not working. This is how it works in mysql workbench:
0
votes
1answer
30 views

how to setup [master] - [master-slave] - [slave] mysql replication

i am trying to setup a mysql replication architacture between 3 mysql server. (by following some tuto like ...
0
votes
1answer
30 views

duplicate entry issue with large mysql database table

I have a very large my sql database table. The other day, for no apparent reason, I now keep getting :- Duplicate entry '148245' for key 'PRIMARY' If I remove the entry at with primary of 148245, ...
0
votes
4answers
34 views

How to subtract fields and insert the value in other field automatically?

id val1 val2 total 1 10 4 10-4(automatically get the value) . . . . I am using php and mysql. I have a total column I need to make calculation automatically in a field, how it ...
1
vote
1answer
24 views

MySQL Delete Rows from Slave

I want to truncate the majority of data on a slave such that it only has the most recent records. I'll eventually demote the current master, making it a read-only archive, and promote the truncated ...
0
votes
2answers
19 views

How to delete a column named grave accent in MySQL

As title. Every time when i try to execute the query: ALTER TABLE tablename DROP COLUMN ```, got syntax error, which is understandable. So, what should i do to delete this column whose name is the ...