Tagged Questions

MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

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
20 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 ...
1
vote
2answers
54 views

Truncating and inserting in a safe transaction on MySQL

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table. I wanted to make sure that if any statement in the transaction failed, the data will be untouched. I can't have an empty or ...
0
votes
0answers
24 views

Billion records mysql performance with large result sets

I'm currently developing an application which will have several mysql tables where 3 of them have very large amounts of records. The thing is that my app will have to sync each account on a hourly ...
1
vote
0answers
24 views

MySQL not using multicolumn index on self join

I'm trying to optimise a query that performs an inner join with itself. The table size is currently 10GB ~50 million rows. /* Count all occurrences between start and end site id of a given speed ...
0
votes
1answer
31 views

MySQL - How to see the queries executing by slave io thread

We have a strange problem with MySQL replication. We have observed huge load and IO wait on server while replication is in progress. We can have information of the queries from bin-log by using ...
1
vote
1answer
48 views

How to get all distinct words in a column

I have a table with a text column that can contain a single word, more words separated by a space or a NULL value. I need to get all the distinct words in that column (words non column entries). For ...
0
votes
1answer
43 views

Steps or evaluation order of the SQL statement

How the SQL statement containing simple statement like Select x From y Where z Having, order by , group by etc. are evaluated.( In which order they are evaluated.?)
1
vote
1answer
84 views

MySQL: Why queries with subqueries are much faster than single query?

I made few queries on my db lately First query: SELECT `table`.* FROM (`table`) WHERE table.id in(27172172,[...bunch of ids...],27171770) ORDER BY field (`table`.`id`, ...
0
votes
1answer
18 views

Pros and cons in combining current data and historical data in the same table

What is the pros and cons in designing a RDB table to store both current data and historical data together? Is it scaleable? Is this approach better than having one table for current data and one ...
0
votes
1answer
34 views

COUNT() and link between tables

I have some difficulties to make a query. Basically, I have a 3 tables: member, build and up: member --+--- id|... build --+-----------+--- id|member (fk)|... up --+-----------+--------- id|member ...
1
vote
1answer
32 views

MySQL data to Postgres every X time

Our team (Uni students) have a project that we are updating. Old project team has a Live MySQL database. We want to make a archive/warehouse from it in PostgreSQL. My question is: How can we import ...
0
votes
0answers
21 views

Convert J1950 Date to Gregorian Date using a MySQL Query

I have a database table with a column named "time." The cells in the "time" column each contain a timestamp entry which was inserted in J1950 time format. I would like to convert the J1950 time to a ...
1
vote
1answer
17 views

Change entire MySQL drive location?

We cloned a VM server and on Server A MySQL is located on the F drive, but on Server B it's located on the E drive. I updated the ini file and the Windows path variables, but it's still trying to pull ...
1
vote
1answer
34 views

Mathematical or Computing Alternatives for MySQL Combination Queries operating toward their Performance Limit

We regularly run a contest that is becoming quite popular. In the contest, we need to select the combinations of contestants from 8 groups whose cost is under a threshold, and who combined have the ...
0
votes
0answers
28 views

MySQL multiple update queries vs. one insert … on duplicated key update query

I need to update hundreds to thousands rows. I wonder which of the following options is better: sending multiple (hundreds to thousands) "UPDATE" queries sending one (big) "INSERT INTO ... ON ...
0
votes
0answers
18 views

How to partition a MySQL table by Week using a timestamp column?

I have the following MySQL 5.6 table, which uses InnoDB and contains a timestamp column (I've omitted all the extra columns, in order to simplify my question): create table partition_by_week ( id ...
0
votes
1answer
34 views

How to skip OR if AND statement row count is not 0

Heey Guys, I have a question and i can't find the solution. So this is my QUERY SELECT * FROM `hwr_story` WHERE question_count = '8' AND (lower(subcategory) = lower('True') OR subcategory IS ...
0
votes
1answer
34 views

MySql - Reorder primary key with auto-increment

I have two table(s) in mysql as follows : CREATE TABLE UserMst ( UserID mediumint(8) unsigned NOT NULL AUTO_INCREMENT, UserName varchar(20) NOT NULL, CreatedOn timestamp NOT NULL DEFAULT ...
0
votes
0answers
42 views

Displaying SELECT Query Results Row as Columns

I have 3 tables: scoreboards, problems, and users. A user can do several problems, and the score will be stored in scoreboards table. Every user will have same amount of row in scoreboards table ...
0
votes
0answers
20 views

High CPU Usage for Mysql after adding partitions

On an amazon m3.large instance (vCPU:2, Mem: 7.5), I run a mysql(5.5.29) server. Initially, the state of the server is OK except that the query is too slow. However, after partitioning the table ...
0
votes
0answers
10 views

MariaDB Galera cluster nod won't start

I have configured a MariaDB Galera cluster with three nodes. The master node and one of the slave-master node is up and running, but the third node just won't run. The system specs and cluster config ...
0
votes
0answers
30 views

WHERE NOT a = b vs WHERE a <> b

Do the following queries have identical effects in MySQL, and are they just a different way of writing? I am asking this because I am creating a query builder in php, ideally if one way is identical ...
0
votes
0answers
18 views

Differing AUTO_INCREMENT values between 5.1 vs 5.6 slaves

I have a MySQL 5.6.21 slave and a MySQL 5.1.73 slave that are both replicating from the same MySQL 5.1.73 master. I noticed that for several of the tables ( I said several because when I try to rerun ...
0
votes
1answer
18 views

MySQL - using ON DELETE CASCADE properly?

Trying to implement a simple user follower system in MySQL, using a junction table: CREATE TABLE users ( id int NOT NULL AUTO_INCREMENT, email varchar(255) NOT NULL, username varchar(25) NOT NULL, ...
0
votes
0answers
31 views

MySQL Inno DB Crash

This is the log of the crash. Basically every time I run a query it looses connection an crash. 2014-11-03 19:46:42 58021 [Note] Event Scheduler: Loaded 0 events 2014-11-03 19:46:42 58021 [Note] ...
5
votes
3answers
443 views

Speed of MySQL query for the most recent 800 entries in chronological order

I have a MySQL database running on an Ubuntu box that pings 250 clients once a minute (24x7). My column names are: ip_address varchar(16), status varchar(7), timestamp datetime Been running it for ...
0
votes
1answer
19 views

How to investegate MySQL crash on windows?

I have MySQL 5.6.19 running on Windows Server 2008 R2. I have 3 servers A, B, C Server A is a stand alone server. This server had data that can't be placed on SERVER B (ie. users information.) ...
1
vote
1answer
32 views

Append duplicate records with count (mysql)

We need to update a set of titles that have some duplicate entries. Because of the size of the database I would prefer to use direct mysql method, but I am open to any solutions out there. If we have ...
0
votes
1answer
58 views

NDB Cluster seems to ignore foreign_key_checks=OFF

According to MySQL documentation on the local foreign_key_checks variable Beginning with MySQL Cluster NDB 7.3.2, setting this variable has the same effect on NDB tables as it does for InnoDB ...
0
votes
0answers
12 views

SQL nodes in MySQL Cluster

I running mysql cluster in windows 8.1 on 3 machines 2 mngmd 2 sql node 3 data nodes in the first machine I tried to connect MySQL server with localhost and tried to put my old database it ...
0
votes
0answers
11 views

lot of relay log formation in mysql5.6

Reason for lot of small relay log file formation. I Have setup a slave and it relay log rotates to soon and creates thousands of relay log file.whats the reason for more relay log formation.
0
votes
0answers
10 views

Mysql table size reported, on disk and on slave

I thought I understood MySQL table sizes, but I have some confusion about it. I have two database servers, one primary and one slave (which was created recently). All DBs are replicated; no dangerous ...
0
votes
0answers
14 views

MySQL stored procedure return variable values from seconds query? [migrated]

SET @v1 = ''; SET @v2 = ''; SET @Query = CONCAT('SELECT sum(colName1), sum(colName2) INTO @v1, @v2 FROM tableName WHERE id=1 '); PREPARE stmt FROM @Query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET ...
0
votes
0answers
29 views

How to avoid 2013 error when executing ALTER TABLE

I have a huge table (2.8 GB) and I need to add a column. After I run this query I experience a lost connection error (Error code: 2013. Lost connection to MySQL server during query): ALTER TABLE ...
0
votes
2answers
46 views

Moving MySQL to another server without updating connection strings

Currently, I have a Windows 2008 R2 Server which runs both all my websites and all my MySQL databases. I am considering moving MySQL to another seperate server which may also be Windows or may be ...
0
votes
0answers
29 views

mysql trigger Update a row after insert according to condition

I am writing a MySQL trigger. In which I would like to update branch_form_no with auto increment form number according to branch id. How should I do? I have table hcm_staffing_applicantform with ...
2
votes
2answers
40 views

How to retrieve elements from another table in a SQL query?

Sorry for my english !!! I get data from a table wp_posts and now I need to get data from the table wp_postmeta that match the ID captured in wp_posts table. The data that I need to grab in the ...
0
votes
1answer
26 views

Speeding up a simple select query on Mariadb

I have a 100k rows table with the following structure: +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
0answers
26 views

Database still slow after index added

I have a mysql database with 2 million rows. when using "order by firstname" which firstname is not an index, the database is exteremely slow. I added an index for "firstname" and I thought it will ...
0
votes
0answers
17 views

MySQL stops unexpectedly on CentOS 6.5 with WHM/Cpanel after migrating server with transfer tool [duplicate]

I recently moved my entire websites from a dedicated CentOS5 server to a CentOS 6.5 on Amazon EC2 with the same cPanel/WHM version. WHM 11.44.1 (build 19) However after angry calls from clients I ...
6
votes
2answers
325 views

Transaction speed benchmarks for mySQL v5.6 replication - seems very slow

I'm trying to select the best configuration for our new infrastructure but got a bit confused about the results. I used sysbench v0.5 for the tests: prepare data sysbench ...
1
vote
0answers
45 views

Lost Connection to mysql server at 'reading authorization packet', system error 0"

OK, this has been bugging me all afternoon now, I have a new MySQL Database '5.5.40-0+wheezy1-log' running on Debian Linux. It is to all extents and purposes a clean install. I have created two ...
0
votes
1answer
34 views

How to insert values in junction table for many to many relationships?

I have three tables in database: trips(trip_id(pk), trip_name(unique), user_id(fk)) places(place_id(pk), place_name(unique)) trips_places_asc(trip_id(fk), place_id(fk)) Since, many trips can ...
0
votes
0answers
19 views

MySQL Crash due to “InnoDB Assertion” after Query

Current setup: Ubuntu 12.04 LTS MySQL 5.5.31 Current Use: Slave - Only ETL queries 64 GB RAM Intel(R) Xeon(R) CPU X5670 @ 2.93GHz - 12 Cores - Hyperthreaded (24 Logical) MySQL Main Settings: ...