MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
1answer
5k views

Query performance with subquery and IN clause

I am trying to select a range of data for multiple devices (unique serial numbers) from a historical table and was wondering why there is such a big difference in time for the following queries: ...
1
vote
1answer
269 views

Conditional one to many queries in mysql

I have a simple patients table and lots of Lab tests table. Every patient can have many Lab tests. What i want to do is to only query the tests which are already entered for that specific patient and ...
2
votes
1answer
337 views

MySQL Trigger problem with the email address (cause contains the @ sign)

I'm trying to set a trigger in a table (users) to "copy" the email address of a new row to another table (prova). Here is the trigger: CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW ...
1
vote
1answer
161 views

MySQL detect duplicate human names [closed]

I've been tasked with identifying a solution to help identify possible duplicates when inserting new user records into a MySQL database. Does anyone know of any cheap, preferably free, solutions? The ...
2
votes
1answer
8k views

Multiple left joins or subquery?

I'm working on joining multiple tables together to extract some data related to user "runs", this includes basics like runID, username etc but also joins to the run times and favourites. I have the ...
7
votes
2answers
5k views

How to convert control characters in MySQL from latin1 to UTF-8?

While converting a database to UTF-8 I noticed a strange behavior regarding the control characters 0x80-0x9F. For example, 0x92 (right apostrophe) would not get converted to UTF-8 and truncate the ...
2
votes
2answers
492 views

Using all the CPU cores by NDBD and MySQL

We are running MariaDB which is a fork of MySQL with NDB cluster engine. The cluster includes one mysql node and two ndb data nodes. While benchmarking the cluster, we are trying to make a bottleneck ...
2
votes
1answer
631 views

Calculate percentage of false records to total records within a aggregate function

I'm looking for a solution to calculate a percentage rate of a boolean value: Model: Products 1 <-> n Components Components.essential is a boolean attribute What I want to calculate ...
1
vote
1answer
2k views

Write lock during mysqldump even using --lock-tables=false

When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this? As I am using MyISAM, does --single-transaction for InnoDB help? ...
3
votes
3answers
4k views

Can I specify a different disk for a specific MySQL table?

I use my home machine as my personal database for my hobby analytics project. This machine has an SSD for the main drive where MySQL stores its data. It also has a secondary hard drive that is ...
5
votes
2answers
5k views

Storing time with milliseconds in database

Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds). I was originally going to ...
15
votes
2answers
2k views

Primary key index with a DATETIME as first part of the compound key is never used

I have a problem with INDEXING a DATETIME (or even a date) as first part of my PRIMARY KEY. I use MySQL 5.5 Here are my two tables: -- This is my standard table with dateDim as a dateTime CREATE ...
3
votes
1answer
3k views

Rows with a variable number of columns?

I'm trying to design a database for a program that is storing and manipulating tweets off of Twitter. I'm using code to fragment a tweet into words, usernames, and hashtags, so I'm meeting ...
1
vote
1answer
5k views

How to enable MySQL general log?

I would like to enable the MySQL General Log to view all the (slow) queries. I'm using XAMPP as server and have no idea how to enable it. I can't find a good guide on the internet. Can someone help ...
2
votes
1answer
305 views

Has anyone ran into this replication breaking bug before?

I've been having this issue where replication was breaking on colliding primary keys. The annoying thing was these were auto increment generated. I think my issues been tracked down to ...
1
vote
1answer
260 views

What's wrong with this update RANK query?

It was updated only the first time I ran it, after that the users_tbl_rank stays the same. $sql = "SET @r = 0; UPDATE users SET users_tbl_rank = @r := (@r + 1) ORDER BY users_tbl_points DESC"; In ...
4
votes
2answers
3k views

MySql - How can I speed up this query

I have the following tables: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `last_name` varchar(255) COLLATE ...
0
votes
1answer
403 views

which DBMS should i go for? [closed]

I am interested in learning a DBMS. As a beginner which DBMS should i go for ? I know few DBMS by name like Oracle,Sql Server,My Sql . How are these DBMS different from each other ? As a beginner ...
1
vote
1answer
5k views

MySQL Replication Error

I am getting an Error on Slave as: could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error ...
18
votes
4answers
9k views

How should I design a relationship table for friendship?

If A is a friend of B, then should I store both values AB and BA, or one is enough? What are the advantages and disadvantages of both methods. Here is my observation: If I keep both then I have to ...
1
vote
1answer
90 views

Basic sql query problem [closed]

Here is the scenario: lets say I have a database named sqlprac that contains a table named dept. There are two ways to write a query: use sqlprac /*1*/ SELECT dept.deptno, dept.deptname, ...
2
votes
2answers
434 views

get column from too many tables in mysql

.I have too many tables X1,X2,...Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company. every table has structure something like this desc X1; ...
2
votes
1answer
1k views

Why is mysql logging to syslog?

I have installed Multiple MySQL instances and i have two configuration files one for each one is using default location of /etc/mysql/my.cnf. other server i have started like ...
3
votes
4answers
10k views

Optimized my.cnf for high-end and busy server

Curretly my main culprit of poor performance is MySQL and I'm not a DB savvy. So far, I've had little luck in optimizing mysql configs on my server, which has a Sandy Bridge processor with 16GB ram ...
2
votes
2answers
3k views

Mysql consumes a lot of memory

Here is my situation. I have an ubuntu server monitored by nagios. recently nagios sends alarms regarding the memory consumption on the server. I logged into the server and executed the top command ...
6
votes
1answer
2k views

what are the conditions under which mysql replication might break?

I am running mysql server in master/slave configuration mode. There are multiple slaves replicating from a master. They are working fine and their replication status is monitored. One of the slaves ...
2
votes
1answer
776 views

Understanding mysqltuner Recomendations w/ Unused Catalogs

I have a MySQL 5.0.77 instance running with three catalogs in it. Two of the catalogs contain all InnoDB tables and are actively used. The third is all MyISAM and is basically unused, and is only ...
3
votes
1answer
980 views

MySQL 5.5 InnoDB = OFF?

We run a server with MySQL 5.5. 95% of the tables are set to the InnoDB engine. However whenever I look at the my.ini file (using the MySQL Workbench) under mysqld section innodb is unchecked and OFF ...
1
vote
1answer
1k views

MySQL transfer ISO-8859-1 to UTF-8

How to transfer data from Database with latin1 character set, to database UTF-8. And on transferring convert data from latin1 to UTF-8 e.g., û->ū, ë->ė, þ->ž, á->į and etc.
6
votes
1answer
444 views

MySQL database design

I could do with some help designing a database for a website I'm working on. I should mention that I am a beginner and I have never had to design a database in the past. Ok, my implementation needs ...
2
votes
1answer
91 views

Is it dangerous to have duplicated entries in a table?

My table have many duplicated entries, is it dangerous if I don't fix this? My table is structured like this: Music_id | Music_title | Music_author | Music_link 1 Look at me now ...
1
vote
0answers
69 views

innner join troubles [closed]

Dang it's hard to send you well wishes here. I wish you all a good year before this post is deleted. Remember to love your fellow developers. I'd like to join using(love) where i=heartU group by ...
2
votes
1answer
3k views

MySQL : Is there a command similar to FLUSH STATUS to reset GLOBAL STATUS?

I'm trying to run some load tests on my MySQL server and check a bunch of relevant GLOBAL STATUS variables after each run. Is there an easy way to flush GLOBAL STATUS variables like the regular FLUSH ...
1
vote
1answer
498 views

MySQL Replication: Slave with Different Column From Master

I have a Master/Slave replication. I had to change the name of one of my columns in one of my tables from temp_col to sent_at, this column is not being used yet. I've issued an alter table on an ...
2
votes
2answers
512 views

MySQL Full Text index issue

I Have a table articles with following data mysql> select * from articles; +----+-----------------------+------------------------------------------+ | id | title | body ...
0
votes
1answer
196 views

What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been ...
3
votes
2answers
5k views

How to make CREATE INDEX in InnoDB table faster

I have platform RHEL 5 with 8G RAM and MySQL version 14.12 distrib 5.0.86. I want to create an index in an InnoDB table, but it takes a long time: Time | State | Info 2291 | copy to tmp ...
1
vote
2answers
485 views

Is there a tool or library to extract data from Mysql database files without having the database engine available?

I have a bunch of *.frm, *.myd and *.myi files, which are a MySQL database store. I need to extract the data kept in these files, but I do not have a MySQL database engine at my disposal. Is there a ...
2
votes
2answers
245 views

How much will computer hardware affect the speed of a “load data local infile” query?

I have a VBA program that separates and saves an excel file into 48 different .csv files, then loads those files into 48 separate tables. I use "load data local infile" to accomplish this, and it has ...
11
votes
5answers
1k views

Infrastructure for Highly Concurrent, High Write DB

My requirements are: 3000 Connections 70-85% Write vs Read Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of ...
2
votes
1answer
141 views

Why cp1256 collation is not 1 byte in MySQL?

I've read an article that cp1256 collation takes 1 byte for Arabic characters, but when I created an index on the column that was cp1256 with type of varchar(900), only 767 characters picked up for ...
4
votes
3answers
238 views

How to design this simple DB?

How to design this simple DB? I need to add some things to a DB I use to keep track of servers. Table looks something like this. Matrix | Name | Description | Etc | Etc | ...
1
vote
2answers
575 views

MySQL Sharding tables/servers or other methods to reduce table sizes?

I'm currently working with a table exceeding 12 million rows (at about 3 GB when exported with mysqldump) and am curious how huge a table can realistically get without any serious performance impact. ...
1
vote
5answers
740 views

MySQL Replication monitoring [closed]

Im looking for a tool that could help me and my team monitor our MySQL Replication. I would love for a tool that could alert me if Seconds_Behind_Master passes a threshold Slave_SQL_running is ...
0
votes
1answer
1k views

I want to count all the rows for todays / yesterdays date based on a DATETIME - mysql+php

I have database "db2" with table "menjava" In table menjava have "id", "author" and "date_submitted" field id - auto_increment author - int(11) date_submitted - datetime I want to count all the ...
1
vote
2answers
1k views

Why a copy of my.ini of other MySQL installation not working on my PC

I have two MySQL installations on two different PCs not connected to each other. I made some changes in the my.ini file of PC 1 and tried using this file on PC 2, but MySQL service fails to start. Why ...
2
votes
1answer
98 views

How to insert fields to different tables at once?

5 Main Tables: TEMP, PAYMENT, CUSTOMER, PURCHASE, and PRODUCT. TEMP: TempID, Email, Name, Product1, Product2, Date, Amount PAYMENT: PaymentID, Status, Amount, Date CUSTOMER: CustomerID, Email, ...
1
vote
1answer
1k views

How to set up triggers in MySQL for copying a field

I have an in-house application that I've put together, and I'm VERY new to SQL in general. I have a table that contains most of our data. Let's pretend it looks like this. Matrix |Name | ...
3
votes
1answer
170 views

Help configuring MySQL install for remote connections / root accounts understanding

I installed mysql/phpmyadmin/apache on a linux box for internal network use only. I don't have the strongest understanding of MySQL. Hostname: server.domain.lan IP: 192.168.0.54 After I installed ...
1
vote
1answer
375 views

View metadata refresh in MySQL

Like other RDBMS, view meta data is stored at view creation time. From CREATE VIEW in the MySQL docs The view definition is “frozen” at creation time, so changes to the underlying tables ...