Tagged Questions

MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
22 views

WHERE in CASE in mysql

$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))." FROM (select CASE when r.agent_id=24 THEN r.Unit ELSE '--' END AS MyUnit, CASE when r.agent_id=24 THEN ...
0
votes
2answers
38 views

Do I need to reindex mysql table after bulk delete?

I have a table in MySQL with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
0
votes
2answers
48 views

Do I need to reindex my table in mysql after delete?

I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
0
votes
0answers
20 views

Data on the slave becomes inconsistent after couple of days

We are planning on upgrading our production MySQL server from MySQL 5.0.77 to Percona 5.6.19-67.0. Since our downtime window is small, we setup a slave of the production master on a new host and ...
0
votes
1answer
38 views

Copy Data To Different Server using SELECT * INTO?

I'm not a DBA by any means. I'm just knowledgeable enough with the MySQL command line to be capable half the time and dangerous the rest. Today I was working on exporting data from a one DB into ...
0
votes
0answers
30 views

Table maintenance after dump/source

I just completed a major DB migration, all went well. I had a 450 GB DB on old underpowered windows hardware using MySQL 5.0 and I moved everything to much more powerful (512gb memory, server 2012) ...
1
vote
1answer
31 views

MySQL InnoDB + high table creation-destruction rate

We are transferring a microsoft SQL based website (+app + plugins) structure to MySQL. The website has a high "Content + Comments + Activity Tracking" behaviour. Mainly because of the activity ...
1
vote
1answer
19 views

Adding a “position” number in Mysql

I got the following query and result currently: mysql> SELECT songname,COUNT(*) as count FROM etrstats WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH) GROUP BY ...
1
vote
2answers
66 views

Are there any special characters that are never allowed in SQL?

I am a Java programmer. We always make database-related things configurable so that they can be easily modified by the DB team as per their requirements. I want to make some queries as configurable, ...
0
votes
1answer
31 views

How can this table possibly be converted to a MySql table?

I got a TABLE in Excel format like: This is a STEN TABLE containing FACTORS (A,B,C,D,E....etc) Each Factor is like a separate test. Each Factor i.e. Factor A has a [Raw Score] and a [Sten] ...
1
vote
1answer
38 views

optimizing query for slow queries

I have this query in my slowqueries table which essentially is taking about 5-8 seconds to complete the query time: SELECT s0_.id AS id0, s0_.updated AS updated1, s0_.created AS created2, ...
2
votes
1answer
56 views

MySQL - Old ibdata file shall I delete safely

On a production server I have changed "innodb_file_per_table=ON" and I can see separate tablespace files for each table on my server. I have used parititioning for tables so for each partition there ...
0
votes
0answers
60 views

Performance tuning self-joining MySQL query

I asked a question recently on the correct way to update a table using multiple joins to itself in order to generate the desired values. Updating MySQL innodb table based on multiple joins After ...
0
votes
1answer
36 views

Insert record only if a combination of column values doesn't exist

I have a simple MYSQL insert query: INSERT INTO table('col1','col2','col3') VALUES('123','456','some text') I want to insert only if the above combination of columns does not already exist and this ...
0
votes
0answers
34 views

Using triggers or php code

I have a table called Salons, which has over 15k Salon names. I have another table which takes count of the pageviews of each salon. My question is that if I want to update reputation of the Salons ...
0
votes
0answers
27 views

Migrate existing database onto Percona XtraDB Cluster (using innobackupex?)

I'm setting up Percona XtraDB Cluster on 3 nodes to replace an existing MySQL database server. The existing databases will need to be migrated to the cluster with as little down time as possible. ...
0
votes
0answers
47 views

Very poor MySQL query performance after upgrading from server-5.5 to server-5.6

I'm in the process of trying to migrate a database from an old server to a new one. The old server is running an Xampp installation which has been more than adequate for the use case. For the new ...
0
votes
1answer
79 views

MySQL taking significantly longer to query via PHP than CLI

TL;DR: I have a query that takes under a second to run via MySQL's CLI, but over 12 minutes via PHP and I'm at a total loss on what to check next. It's a fairly horrid query, full of joins, but the ...
0
votes
2answers
28 views

MySQL equivalent of PostgreSQL's psql \c command

I'm learning PostgreSQL though oddly enough figured out to switch users while in the psql command line to use \c database username; what is the equivalent to switch from one user to another in MySQL's ...
0
votes
1answer
27 views

Division Email Template DB Design [closed]

First question for me on the DBA SA site! This is for an Java email notification app using MySQL for backend. I have a small number of groups in our organization, [Marketing, Commerce, ...
0
votes
0answers
18 views

Recursive view for dynamic tree in MySql

Lets assume that there is a factory that produces products. the factory have catalog of a products, therefore tbl_catalog will be. ...
-1
votes
2answers
33 views

I can't find the problem? [closed]

CREATE TRIGGER request_internal_trigger AFTER INSERT ON request_internal_analysis FOR EACH ROW BEGIN INSERT INTO user_action (User_Name , Request_number , Actions , DateOfAlter , ...
0
votes
1answer
29 views

MySQL uses intersection instead compound index

I have compound index on following columns: (country_id, lazy, has_avatar, inactive, gender, id, age, challenge_count) When I run following query: SELECT COUNT(*) FROM `user_profile` WHERE ...
0
votes
1answer
21 views

How do I add databases from a crashed MySQL 5.1 server to a new MySQL 5.6 server

I was running MySQL 5.1 on a personal web server that recently died. The system drive is non-operational, but I have my second hard disk where I had installed all of my programs. This drive has my ...
0
votes
0answers
20 views

mysql uses a lot of virtual memory

I've a mysql server with a lot of small size innodb databases (each db also contains a lot of small tables) & 6GB of RAM dedicated to mysql server. Recently I've increased my ...
0
votes
2answers
39 views

moving to MySQL 6.0 is a good move?

what is most important issues and cautious we have to do before upgrade to MySQL 6.0? How do you do the upgrade? setting up a new slave and do yum upgrade? what is the best way to upgrade with ...
1
vote
1answer
21 views

How to efficiently swap a portion of a large table's data?

I run an application (price comparison website) where I need to update a pricing table with dozens of millions of rows every day. Basically, every product feed I download from a merchant results in ...
0
votes
1answer
29 views

mySQL Master not getting data from slave

I have followed the instructions on the Web on how to set up master-slave mySQL replication and I have spent a day and half trying to get it to work. The master is simply not getting new data at ...
0
votes
0answers
9 views

MySQL 5.6: creating users kills mysql, and mysql_upgrade fails

I have a fresh (not upgrade) installation of mysql 5.6.19 community edition win64 running on windows 8. I have had no problems with it until today I tried to create a new user. When I do this, mysql ...
0
votes
1answer
29 views

Lock Master Log Position in Master-Master replication

I have two servers connected via private IPs. Replication is partially working, however the problem I'm running into is when I change the MASTER_LOG_POS on Server B using Server A's SHOW MASTER ...
-2
votes
0answers
16 views

After converting .jar to .exe database connectivity lost [migrated]

I´ve made program in java (Netbeans) which is properly compiled and do what i want. After converting .jar to .exe , my program can´t access database. I use Mysql database and I´ve connected database ...
0
votes
1answer
35 views

Mysql query performance

We have a database table and we found a weird query from old code. We can't really explain it, so we decided to analyze why it could have been done that way. The weird thing is that the results are ...
0
votes
1answer
31 views

MySQL optimize range duplicate query

I need to eliminate duplicates from a time sheet. I found this solution and have adapted it for my own needs: DROP TABLE IF EXISTS `activity`; CREATE TABLE IF NOT EXISTS `activity` ( `id` int(11) ...
1
vote
0answers
39 views

Can't seem to optimize joined select query

Can't seem to figure this one out. Query is taking upwards of 7-8 seconds which is just not acceptable and seems as though it will get worse as the table(s) get larger. Without going into a ton of ...
0
votes
1answer
20 views

moving binary database folder is causing issues with innodb tables

so I have a mix of myisam and innodb tables on a database. (its a pretty big db 30gb) I copied the folder of the database from one server to the next /var/lib/mysql/databasename well the myisam tables ...
0
votes
1answer
31 views

mysql database migration of schemas with updated data and structures

I am a newbie with MySQL database dumps and am currently trying to work with data migration between a source schema and a target schema. Assuming the source schema is defined by ...
1
vote
1answer
25 views

Financial Inst - Model Role+Relationships b/w Person and Account

A Person can be on one-or-more Accounts. For each Account a Person is on, Person has a certain role on that account i.e. Person 1 is primary on Account 1 Person 1 is joint on Account 2 Person 1 is ...
0
votes
0answers
36 views

Configuration help my.cnf Mysql

Is the following my.cnf file OK for a dedicated server with 56GB of RAM (on ubuntu server and 8 processors)? I'm running just one website on the server and there is a high demand on my cpu from ...
0
votes
0answers
15 views

I Need to provide encryption at rest for a 50GB MySQL instance running on Centos 6.5

I can't use GusterFS because they don't recommend it and Ceph because it's clear they can't guarantee performance. I can't just encrypt certain database objects because it will take too much ...
0
votes
0answers
19 views

Is it possible to seperate one MySql-Account in a Firewall

I'm having a discussion with a software developer in my nears. He is using MySql direct connections for a software. There is no PHP/SSH/whatever-Bridge involved for the communication, neither at ...
0
votes
1answer
26 views

Updating MySQL innodb table based on multiple joins

I have a table with the following (slightly simplified) structure CREATE TABLE `oak_relation` ( `o_id` int(10) unsigned NOT NULL, `k_id` bigint(20) unsigned NOT NULL, `initial` float unsigned NOT ...
1
vote
0answers
26 views

MySQL table partition strange behavior (slow query suddenly)

(I asked this question in "stack overflow" and was recommended to ask in here). (MySQL version: 5.6.15) I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a ...
0
votes
1answer
28 views

single mysql query that show a particular parent and child value [duplicate]

I am facing problem to show a particular parent child value and under all child value as a relationship. I have created single table tree and three column id, child and parent. I created a tree ...
0
votes
0answers
23 views

mysql:Multi tables SELECT Query

I have 2 tables in my database table_names, with the fields: id, name, join_date, country table_sigs, with the fields: id, pet_title, act_date, member_id I would like to know how many members ...
0
votes
1answer
21 views

MYSQL improve query performance when using OR

I have a very simple MYSQL table with 2 columns and I run this query: SELECT * FROM table WHERE (col1 = '123' AND col2 = '456') OR (col1 = '456' AND col2 = '123') Col1 and ...
1
vote
2answers
27 views

MySQL improve query syntax

I have this simple MySQL query: SELECT * FROM table1 INNER JOIN table2 ON table2.col = table1.id WHERE table1.id = '123' id is the primary key in table1. table2 has composite primary key of two ...
0
votes
1answer
38 views

What is wrong with this SELECT query? No errors, no results

Haunted by this MySQL query that refuses to return results: select entity_id from customer_entity where entity_id NOT IN (SELECT sfo.customer_id FROM sales_flat_order sfo); If you divide the two ...
0
votes
1answer
15 views

MySQL - Arrange a table so that it shrinks as much as possible?

I'm a newbie and I have +10 million articles(200 to 1000 words) in a InnoDB table. I use this to select them: SELECT article,title,other_fields from table where id=123; There is no full-text search ...
1
vote
1answer
25 views

Failing to get multiple values using join

I have below scenario: create table t1 (id int, title varchar(20), description varchar(20)); create table t2 (id int, info1 varchar(20), info2 varchar(20)); create table t3 (id1 int ...
0
votes
0answers
18 views

How to insert a row which contains a foreign key in mysql

Here's the original question regarding PostgreSQL. I'd like to know of a similar solution for MySql. The simplest syntax provided in the answer works fine as long as the relevant rows already exist ...