Tagged Questions

MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
5 views

Unexpected slow queries at random times

I have a moderate service, serving at most 100~ users at the same time. Mariadb slow log is open, and I occasionally see reports of slow running queries such as: # Time: 141123 19:48:38 # User@Host: ...
0
votes
0answers
13 views

Fast select = slow update, slow select = fast update

A query is super-fast when executed as a SELECT (#1), but when I try to UPDATE using the retrieved data, the performance starts to be unpredictable for me. For example, an UPDATE is faster with much ...
0
votes
1answer
9 views

MySQL rapid Data Replication from multiple Databases

I am a Freelance Web Developer but not a Database Administrator at all except basic Database Operations like DML etc. Today I got a client asking me to develop a Web based application but there is a ...
0
votes
1answer
26 views

Unknown column in 'where clause'

I've a query in my php script $result = mysql_query("SELECT State_Id FROM state_master WHERE State_Name='$statename'") or die(mysql_error()); $row=mysql_fetch_assoc($result); return $row['State_Id']; ...
0
votes
1answer
24 views

Is a query inside mysql IF clause allowed?

DELIMITER $$ CREATE PROCEDURE `InsertUser`(IN passwd VARCHAR(100), IN userType VARCHAR(20), IN mobileNo VARCHAR(20), IN refId BIGINT(20), IN companyName VARCHAR(100), IN companyMail VARCHAR(200), IN ...
2
votes
1answer
27 views

How much space is needed to reclaim space from a table in INNODB

I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the ...
-2
votes
2answers
48 views

What's wrong with my SQL statement? [on hold]

I'm trying to execute: create table `certificates`( `id` INT NOT NULL AUTO_INCREMENT, `common_name` VARCHAR(100) NOT NULL, `csr` LONGTEXT NOT NULL, `type` VARCHAR(100) NOT NULL, `certificate` ...
1
vote
1answer
17 views

Does a database update force index rebuild regardless of updated field?

With MySQL (InnoDB), I understand that having indexes slow down UPDATE queries. I would like to know if this happens regardless of what fields are updated. For example, if I have a table with 20 ...
1
vote
1answer
49 views

How to get the SUM between dates in a WHERE clause?

I'm trying to get the SUM between each date in the WHERE clause of the following query, not the cummulative sum, but the sum for the period in between the dates. For example: Query: SELECT ...
2
votes
2answers
21 views

Joining two Mysql VIEWs takes a very long time

I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of ...
1
vote
1answer
56 views

How to combine two queries?

I can't explain this very well except by spelling it out. I start a query on ID, and from there I can get TITLE, DATE, and REGNUMBER. The problem is I also want to get 'where,' which is also stored ...
2
votes
1answer
16 views

MySQL table indexes change location

Is it possible to move the MySQL Table indexes to another location rather than "datadir" ? for example a ssd drive or memory partition ?
2
votes
2answers
40 views

How can I simplify this UNION?

Here's the case - I have the following query: SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN ...
1
vote
1answer
26 views

MySQL no query cache hits

My database have been up and running for a while with query cache enabled, but there are 0 hits. I have tried to check the settings but I am not sure what the problem is. Using InnoDB. mysql> SHOW ...
1
vote
2answers
26 views

Outer Joins to find records with no matching associated entries

I'm trying to find all foo records which do not have any bar records that match a given criteria. create table foo ( id integer ); create table bar ( foo_id integer, name varchar(255) ); ...
0
votes
1answer
17 views

WordPress MySQL writes during “Flush Tables With Read Lock” are rejected instead of being queued

I'm running into an odd issue when setting MySQL into a brief read-only mode for backup. Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to ...
1
vote
0answers
16 views

Migration error: “Invalid date literal detected” when reading decimal

I'm attempting a migration from MSSQL to MySQL and keep coming across a "Invalid date literal detected" error when reading certain decimal columns. I'm running the following (all up-to-date as of ...
1
vote
1answer
37 views

Taking the difference in timestamps between successive entries

This is my first foray into DBA.stackexchange, so I'll say up front: this is a query optimization question. If this is not an appropriate question for DBA, let me know and I'll skulk away quietly... ...
3
votes
0answers
33 views
+50

Is this update, select combination query thread safe?

I have a table called seats, which has a schema like this id,taken For each user, I take a random untaken id and assign to that user, here for simplicity say, I make taken =1. The query I am ...
2
votes
3answers
55 views

Running two DELETE statements causes a syntax error

DELETE `member` FROM `member` WHERE `member`.`type_id` = 1; DELETE `subscriptions` FROM `subscriptions` WHERE `subscriptions`.`type_id` = 1; Table: subscriptions ...
0
votes
0answers
13 views

MySQL Remote Database Failover: Need Advice

I run a network of sites all on 1 server. To improve performance, I'm (finally) moving the MySQL database to a remote machine. I'd love anyone's advice on how to appropriately approach failover. ...
1
vote
0answers
22 views

Migrate from SQLite to MySQL (then back)

For various reasons, I must use a SQLite database with a local application. However, I wrote a Flask app that enables users to more easily classify items in the database, and it runs very slow on ...
0
votes
0answers
22 views

MySQL 5.6.19a InnoDB Concurrent Insert

I've looked through most of the threads on this topic but still had question I was hoping someone would be gracious enough to help with. I am seeing low CPU usage and write latency but transaction ...
1
vote
0answers
21 views

Need advice on optimizing database on a recurring basis

I have a search engine with approx 3.5 million rows in the searchable table. There are many suppliers that feed into the table each day, so primary keys are constantly being removed and added and the ...
3
votes
2answers
409 views

Can anybody tell me what's wrong with this query?

CREATE PROCEDURE Click( in ipaddress varchar(45)) BEGIN DECLARE ex int; SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress; IF ex = 0 THEN INSERT INTO clickactivity (IP, ...
0
votes
1answer
39 views

MySQL-How to find that value in a column is subset with all other values in same column multiple time

I have a table named as Team where table contain 3 column (ID,Team,Task). Its datatypes are (INT,VARCHAR(255),INT). Below you will find a sample table where I would like to perform the query on column ...
1
vote
1answer
42 views

Similar hardware, Same DBconfiguration and Database but different status variables result between Windows and Linux server

I have two database servers - one is Windows - 2008 and another is RHEL5 with similar hardware configuration. Both OS are 64 bit. MySQL 5.1.58 community edition is installed on Windows servers while ...
0
votes
2answers
45 views

Companies and addresses MySQL

I've got a database with 2 tables. companies addresses These are my tables. Adresses adress_id adress_adress adress_adressnumber adress_zipcode adress_city adress_postbus adress_country ...
0
votes
1answer
27 views

schema for mysql database with 30million rows

We am building a SaaS backend for restaurants. We integrate directly with POS, so each POS keeps sending customer orders that we store in a following table in mysql. CREATE TABLE name ( `id` ...
1
vote
0answers
24 views

Handling this multi-threading scenario in the database?

I am working on sample bus ticket booking application,in I am facing these two multi-threading scenarios. Scenario 1 1: Let's say there are two users, they both pick a particular seat and submit ...
0
votes
0answers
38 views

select sequential rows

How would I go about selecting a sequence of rows. Where every row in the sequence: Must only be 1 day apart ~ Thereby the whole sequence of rows must have sequential dates Must have a count higher ...
2
votes
3answers
51 views

Bulk Delete for Large Table in MySQL

I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month. If I do DELETE FROM NOTIFICATION WHERE CreatedAt ...
0
votes
0answers
17 views

Reset relay files on lagging slave after MySQL crash signal 11?

MySQL crashed on this slave server with signal 11: Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) Slave I/O: Replication event checksum ...
0
votes
2answers
32 views

MySQL any way to import a huge (32 GB) sql dump faster?

I have this huge 32 GB sql dump that I need to import into MySQL. I haven't had to import such a huge sql dump before. I did the usual: mysql -uroot dbname < dbname.sql It is taking too long. ...
1
vote
1answer
22 views

Is there a way to prevent special characters on schema objects?

I would like to prevent the creation of objects with spaces or special characters. Is there a way to allow only letters, numbers, and the underscore? Thanks!
0
votes
2answers
29 views

How to determine if open_table_cache is set properly?

Does anyone know what this really means? (from MySQL docs) You can determine whether your table cache is too small by checking the mysqld status variable Opened_tables, which indicates the ...
-1
votes
1answer
32 views

Get a teams Rank based their xp [on hold]

Table is called teams, need to get the following columns... name, wins, losses, percentage, level, xp WHERE console = 3 AND game = 23 AND laddertype = 1 I'm in need of a Rank column to be included ...
1
vote
1answer
21 views

CONCAT() between different alphabet echoes out '&' ampersand

I want to concatenate different alphabet strings and get a weird latin encoding with ampersands after the sixth greek character (I want to concatenate greek and english alphabet strings). Lets say I ...
0
votes
1answer
23 views

MySQL Circular replication with fail over

I have 3 MySQL servers which is running with circular replication. Server A version - 5.5 Server B version - 5.6.21 Server C version - 5.6.21 Master Server FOR Slave Server C ...
-1
votes
0answers
15 views

MySql Store procedures is better for large web application [on hold]

i make a large web application using php pear concept and MySql Stored procedures for better performance please tell i am right or not . and also comment about advantage and disadvantage of this ...
0
votes
2answers
49 views

How to use max function in such condition?

My query is select * from guestuser union select * from olduser ORDER BY userid DESC LIMIT 1; Is that correct query to find last userid from different table have different userid? can i change ...
0
votes
0answers
22 views

Validate MySQL/PostgreSQL backup without running server [on hold]

We have some MySQL/PostgreSQL servers that we already backed up daily. Now we want to validate those backup files to see if any corruption happens. The common way to do this task is just restore the ...
1
vote
3answers
31 views

Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB

I have a question in regards of what potential issues that might happen with a MySQL 5.1 DB that replicates from a MySQL 5.6 DB. I know the risks involved based on resources such as ...
0
votes
0answers
17 views

MySql backup for certain database contents

So i have a database containing custom functions and cleanup procedures and obviously content under tables which i want to back up using automating script which works but doesn't create a proper ...
1
vote
0answers
17 views

MySQL - Transactions and causality

I'm new to MySQL's LOCK and TRANSACTION and have a question about different isolation levels of a transaction: Given two transactions executing the same code (auto-commit is false): (1) get data of ...
0
votes
0answers
17 views

MySQL: migration huge tables between two databases

Hello guys (and sorry for my English). I have 2 DB's - DB1 (staging) and DB2 (production), each day some contend added/changed in some tables in DB1 and should be moved to DB2. Right now it works ...
0
votes
0answers
7 views

Is it proper to make a grand-parent key, a primary key, in its grand-child, in a multi-level identifying relationship?

I'm modeling a DB for a conference ticketing system. In this system attendees are members of an attendee group, which belong to a conference. These relationships are identifying, and therefore FKs ...
0
votes
1answer
18 views

Mysqldump without slow query

Situation: I currently monitor an instance of MySQL for slow queries. We have a mysql user dedicated to backups which performs a mysqldump, but the mysqldump gets logged as a slow query. Question: ...
0
votes
1answer
14 views

MongoDB Manual References vs DBREF

I've been researching MongoDB and trying to see if it would be a fit for my company's product catalog. I came across database references: http://docs.mongodb.org/manual/reference/database-references/ ...
0
votes
2answers
26 views

MongoDB/MySQL - auto increment vs object ID

I am in the process of evaluating MongoDB and writing some basic PHP applications around it. Typically in my PHP/MySQL applications, I would create an ID field and set it to auto-increment on each row ...