MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

4
votes
3answers
189 views

mysql implementation of ORDER command

Sorry for a silly question, I tried query with WHERE clause and ORDER at the end, which actually took very long time to implement, even thought WHERE clause was on indexed field. It looks like Mysql ...
4
votes
2answers
7k views

Is InnoDB Engine up to speed against Memory Engine?

I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows: SELECT P.col1, P.col2, ...
3
votes
1answer
735 views

Can you have a WHERE clause with a greater than operator on a composite key?

I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table : CREATE TABLE `bbcmapimp_pc_raw` ( `postcode` char(8) NOT NULL, ...
10
votes
1answer
6k views

Order by column should have index or not?

I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
1
vote
2answers
650 views

Mysql master-master permission replication issue

I came to know it's safe to replicate mysql database to have the users and permissions replicated, from a post in ServerFault is-it-ok-to-replicate-mysql-db. I have setup master-master replication ...
3
votes
3answers
3k views

Confirm that my.cnf file has loaded OK

Does anyone know if there is a way to confirm if any my.cnf file is being read by MySQL? I only have the one my.cnf on the machine (and there are no my.ini files) which I have in /etc/my.cnf. When I ...
1
vote
2answers
606 views

All columns of table should be indexed or not mysql database?

I am having one table with 4 columns. I that one is primary key and other three columns are references to columns of other tables. I am using join of these four tables for search filter. These joins ...
0
votes
4answers
144 views

Resetting MySQL Password [closed]

Please see the following link from Super User. I cannot get anything working: http://superuser.com/q/342609/93569 Update: This link has been dramatically changed! Please see the link.
3
votes
2answers
1k views

MySql replication with Raw Data and InnoDB tables

I want to start replication on DB that has many InnoDB tables and MyISAM tables. I did everything according to manual Creating a Data Snapshot Using Raw Data Files. Firstly, I tried copied all ...
1
vote
1answer
177 views

Appropriate Windows version for Mysql

I have a HP ProLiant DL380 G5 server and I would like to install mysql on it . My questions are: Which MySQL version should I install from this link for better performance, 32 bit or 64 bit? Which ...
3
votes
2answers
2k views

MySQL: Storing unique URLs

I am creating a table wich will contain user-provided URLs. I want those to be unique, so when the user gives me a URL I will first check if the URL exists and if so return the ID for the entry. If ...
3
votes
1answer
1k views

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777). When i tried to open the file ...
7
votes
1answer
1k views

recover a single mysql database on a busy master-slave replicated system

Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system. I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave ...
5
votes
1answer
5k views

Will mysqldump command also backup all the views that are present in DB or there is a special command for it?

I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it. ...
4
votes
1answer
612 views

How do I replicate Mysql DB server to new server?

Let me give you a preface by stating I am fairly new in Mysql environment. I am trying to replicate my production server to a new server (with better hardware configuration). I want to replicate ...
4
votes
2answers
175 views

MySQL: Is it safe to have a table with multiple millions of rows?

I have a very complex search query to write that will touch many tables, have conditions and joins. For efficiency reasons, I am strongly considering creating a "cache table" where I would maybe once ...
2
votes
2answers
2k views

Problem with InnoDB “per table” file sizes

I Have configured the mysql server with innodb per table. There was a table watchdog having approximate 19GB of watchdog.ibd file. I have truncated the table now it has zero record. But the ...
2
votes
1answer
6k views

How can I optimize my mysql setup to create my index faster?

I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package. The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored. I have an MyISAM like ...
4
votes
1answer
2k views

Mysql Memory table getting many locks

On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table. This table has the following activity: 1 SELECT per minute 1 DELETE per minute no UPDATE queries Lots of ...
2
votes
0answers
111 views

MySQL MEMORY table has high Lock Wait Ratio [duplicate]

Possible Duplicate: Mysql Memory table getting many locks on my site I log every pageview (date,ip,referrer,page,etc) in a simple mysql table. This table gets very little selects (3 per ...
0
votes
1answer
208 views

MYSQL - 'Select distinct' - different results on two exact versions of Mysql, databases synced

Same versions of Mysql and the OS (Ubuntu), Databases synced fully. Even the replica gives different results. One mysql instance is showing 11 results (from a Select distinct statement) and all the ...
2
votes
2answers
836 views

mysql user creation with % to host , can only log on from remote host

Using MySQL v5.1.52 After creating a mysql user with the following commands: create user 'user1'@'%' identified by 'pass1'; grant select on db1.* to 'user1'; flush privileges; Then doing a 'mysql ...
1
vote
3answers
425 views

Should I split a single table with an AUTO_INCREMENT PRIMARY KEY into two tables that can be joined on that PRIMARY KEY?

I have a table that has 46 fields with an AUTO_INCREMENT PRIMARY KEY. It's hard to look at a table that has this many columns, so I frequently use a SELECT statement to look at 34 of the fields that ...
0
votes
0answers
424 views

MySql shell command not replicated to slave - “LOAD DATA INFILE” [duplicate]

I have a shell script that looks something like that: #!/bin/bash mysql -udb_user -pdb_password -e "LOAD DATA LOCAL INFILE '/srv/some_file.csv' INTO TABLE db_name.db_table FIELDS TERMINATED BY ...
0
votes
1answer
372 views

InnoDB table vs MEMORY table for a mysql environment with many inserts

I run my sites all on InnoDB tables which is working really well so far. Now I like to know what is going on in real-time on my sites, so I store each pageview (page, referrer, IP, hostname, etc) in ...
4
votes
2answers
2k views

Separate tables or partition one huge table?

I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
1
vote
1answer
759 views

MySQL not reading my.cnf in MAMP

Running MAMP (not PRO) 2.0.5, MySQL 5.5.9, locally on OS X and I cannot get MySQL to read a my.cnf file. I've tried throwing it in the following directories with no results: /Applications/MAMP/conf ...
1
vote
1answer
1k views

MySQL - MySqlDump uses \n instead of null

Using Windows with MySQL 5.1. I am running a script that uses mysqldump to backup a database. The mysqldump syntax used is: mysqldump --user=root --port=3306 --password=topsecret some_database ...
7
votes
2answers
4k views

Performance of a Trigger vs Stored Procedure in MySQL

A post here on DBA.StackExchange (What are the best practices for triggers to maintain a revision number on records?) has spawned an interesting question (at least, interesting to me) regarding ...
4
votes
2answers
2k views

How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB. I basically did this: set sql_log_bin = 0; set sql_mode = 'STRICT_ALL_TABLES'; ALTER TABLE ...
0
votes
1answer
185 views

Why mysqld server service gets aborted when i start mysql client service? [closed]

I am installing mysql-5.5.7rc from source code. After installation when i start mysqld service is being run but as soon as i run mysql client service server service gets aborted.. And also ...
3
votes
2answers
2k views

What is the right time to use an ENUM versus a normalized types table in MySQL? [duplicate]

Possible Duplicate: Advantages and Disadvantages to using ENUM vs Integer types? Considering MySQL only... I'm curious to know what the community thinks the proper use-case for an ENUM ...
1
vote
2answers
1k views

Join three tables

I have those three tables SELECT occuId as 'Occupation Id',occuDscr as 'Occupation Description' FROM occupation_field; +---------------+------------------------+ | Occupation Id | Occupation ...
1
vote
1answer
799 views

Mysql Impossible WHERE and enum

I'm using enum as an alternative to check constraint which is still not supported by mysql. Could you clarify why optimizer doesn't take into account allowed values for enum ? For example, CREATE ...
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
270 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
338 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
162 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
9k 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 ...
3
votes
2answers
497 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
663 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
4k 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
6k 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
307 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
262 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 ...