MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

7
votes
2answers
3k views

Is one-to-one relationship normalized?

Consider we have a large set of statistical data for a record; e.g. 20-30 INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table ...
5
votes
1answer
292 views

Query for data that is not there

I have this setup attendance table: ------------------------------------------ | member_id | attendance_date | event_id | ------------------------------------------ attendance indeces ...
4
votes
1answer
120 views

Problem joining two tables in MySQL

I'm new to MySQL. I'm having trouble in joining two tables: SELECT e.resourceName FROM resources_in_room r JOIN resource e ON r.resourceId=e.resourceId WHERE r.roomId=2; Table resources in room: ...
3
votes
3answers
592 views

Is it possible to pass parameters to pt-table-checksum using an options file?

We're using Percona's pt-table-checksum for MySQL replication integrity check. Is it possible to pass some or all of the options using some sort of configuration file? For instance, can we write the ...
0
votes
1answer
90 views

Table and query design options

We have a alert table which have deviceID as a foreign key. At present one alert just refers to one devive so things works smoothly with join. The problem start now when one alert now is to refer to ...
0
votes
1answer
191 views

What are the different connections of MySql?

I have a software(toad for mysql) to connect to my MySql database. We have to provide some data('username','password' etc) to this Software to connect database. There is a field known as 'connection ...
0
votes
1answer
208 views

Why we use low numbers for concurrent in MySQL benchmarks or HTTP_LOAD? [closed]

I've seen so many times that we use numbers like 5,6 or 10 at most. What is this concurrency? Doesn't it refer to number of simultaneous users in bench? EDIT I'm not talking about any specific ...
1
vote
2answers
491 views

Moving MySQL database from UniServer to Ubuntu Server?

I have set up Ubuntu Server 11.10, and plan to host my website on it. Until this point I was using UniServer to host it in a portable fashion. Using the built in MySQL. This is fine, and through ...
2
votes
1answer
270 views

Will mysql_secure_install script block master updates to slave?

I had setup two virtual mysql database servers in two oracle linux 6 vm's, which come with mysql server ver 5.1.47 Master named prod_master & slave named prod_slave1. I installed both the mysql ...
2
votes
1answer
6k views

Enabling InnoDB on an existing MySQL production server

We have a MySQL 5.1.59 server that has 30-40 existing MyISAM databases. I need to deploy an application that requires InnoDB databases. Can I just enable/configure InnoDB in /etc/my.cnf and restart ...
5
votes
2answers
1k views

Is there a way to hint to query optimizer to MySQL which constraints should be done first?

This is my current query: SELECT BusinessID as ID, 111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from ( SELECT * FROM ...
0
votes
1answer
3k views

Converting Python's date format to a MySQL

First of all I hope this is the right place to ask. I have a Python script which parses data from a few links. In the parsers I parse also the date of the last modified article. This is the output ...
3
votes
2answers
1k views

How does compound index help speed up query?

Say we have a bunch of column in mysql table. Latitude Longitude Say we make an index (Latitude and Longitude) Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort ...
3
votes
3answers
1k views

Unique combination key MySQL

Lets say I have a table with 3 fields: id1 id2 mapTypeId What I want is a primary/unique key that is any combination of id1 and id2. For example if I have this record already in the database: ...
17
votes
1answer
5k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
4
votes
2answers
380 views

fast bulk incrementing in MySQL

I have one big table foobar describing a many-to-many-relation and containing millions of foo's, millions of bar's and every bar having several hundereds of foo's -> billions of rows. CREATE TABLE ...
2
votes
1answer
1k views

How to handle temporary tables with mutliple connections?

In Is there a way to hint to query optimizer to mysql which contraints should be done first? I created a subquery. The subquery is often not optimized. I think a better way is to create a temporary ...
2
votes
2answers
8k views

Reset MySQL root password in LAMPP server on Ubuntu

I forgot the password I changed for the root user of MySQL. I'm logged in as root user and when I do # /opt/lampp/lampp security it gives result as XAMPP: Quick security check... XAMPP: Your XAMPP ...
2
votes
1answer
84 views

MySQL query took 98 seconds to run but EXPLAIN looks okay

I have a query that took 98 seconds to execute this morning, but I can't recreate the problem now, and the EXPLAIN output isn't giving me any clues. Am I missing something? Here's the SQL: SELECT ...
2
votes
1answer
125 views

why/how does the number of matched columns influences the way of excecuting a query

Imagine the following situation: Table A uses MyISAM and contains 4 fields (text) with a combined FULLTEXT-index. FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4 Table B uses InnoDB and ...
3
votes
1answer
155 views

Cannot retrieve values from LEFT JOIN (2 tables)

Here it is my query: $query_products = " SELECT p.Id , p.product_sku , p.product_name , p.hide_product , p.new_style_image , p.ext , p.product_thumb_image , ...
9
votes
1answer
239 views

Power went out - Did query finish?

Is there a way to check and see if a query finished? I ran 3 very long running update queries (+/- 25 hours each) as I went out the door for vacation last week. Unfortunately, somewhere during the ...
3
votes
2answers
829 views

Database Design (Stores + Products)

I currently have 2 tables in my DB, stores and products. Each product has it's own ID 1-2000, and each store has its own id 1-200. My question on DB design, is how do i keep track of which products ...
3
votes
1answer
5k views

How can I insert smiley faces into MySQL ( 😊 )

I'm on MySQL 5.5.21, and trying to insert the '\xF0\x9F\x98\x8A' smiley face character. But for the life of me, I can't figure out how to do it. According to various forums which I've been reading, ...
0
votes
1answer
666 views

MYSQL dump and problems character encoding

I have a 2 gb old database sql backup file. The problem is that text is encoded in UTF-8. Actually this isnt the real problem. This database contains greek and english characters. The problem is ...
1
vote
1answer
888 views

MySQL Workbench: Custom fields for Relationships

I have to manage an existing DB, created "manually" without mysql-wb. I want to draw the printable ER diagram. When I create a relationship, workbench creates its own external key fields. How can I ...
1
vote
1answer
4k views

many 'unauthenticated user', 'connecting host', in show full processlist (Mysql 5.1.5)

I have a mysql slave on AWS/EC2 that receives peak CPU randomally. I run show full processlits and I get many (few 100s) of processes like this: '1141944', 'unauthenticated user', 'connecting host', ...
8
votes
2answers
180 views

Optimization problem: compound clustered keys, flag conditions and index-merge

Three tables: product: with columns: ( a, g, ...a_lot_more... ) a: PK, clustered g: bit-column main: with columns: ( c, f, a, b, ...a_lot_more... ) c: PK, clustered f: bit-column (a, b): UQ ...
0
votes
2answers
224 views

MySQL Statement not displaying some values

I have a MySQL Statement that runs perfectly but does not display some of the values from the table that it is supposed to display. My table has got a column called 'token' which has values that are ...
3
votes
2answers
3k views

Mysql load from infile stuck waiting on hard drive

I've got a windows 7 64 bit machine that I'm using for some load testing of a mysql db. My program uses sqlalchemy to connect and run several load from infile statements on said database. These bulk ...
3
votes
2answers
926 views

Are these MySQL queries efficient?

It took me quite a while to learn how to deal with timestamps in MySQL and I think I accomplished my goals, but I want to double-check two things: 1) Are these doing what I expect 2) Are there ...
1
vote
2answers
146 views

Is this an appropriate development environment MySQL backup strategy?

I'm a web developer and I use primarily my own laptop at work. We use an SVN repository for our project codebases, and deploy our changes to an internal development server for testing. Our system ...
2
votes
1answer
6k views

MySQL Workbench asking for password

I am trying to set MySQL server on my Windows 7 64 bit machine. I have downloaded MySQL server and MySQLWorkbench. I have started mysql service by command C:\> net start mysql as suggested ...
2
votes
1answer
2k views

Set-up MySQL Server on Windows 7 64-bit

I am trying to install MySQL Server on my Windows-7 64-bit machine. I have downloaded MySQL Server Community Edition and MySQL Workbench. I am trying to start a new Server Instance from Workbench. I ...
3
votes
2answers
61 views

Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table?

I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have ...
1
vote
1answer
2k views

Why I cannot let EXPLAIN update statements?

I can imagine that it's actually more important to explain SELECT statements, but I wonder why there's no possibility to use EXPLAIN on UPDATEs/INSERTs. I may use a WHERE-statement in an UPDATE where ...
1
vote
1answer
415 views

How to match on a longer word using MySQL Full Text Search?

I have 2 tables : Company and category (category table has tags field, I wrote keywords for each category, I used comma(,) as delimiter) When I search something, it will first search in the company ...
3
votes
2answers
2k views

More than 20 columns in database table MySQL

I have an object, that describes by 40 properties, is it good idea to create table with 40 columns? I think, that I can't break this properties into the other table (such as table_properties_1 and ...
10
votes
2answers
5k views

PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component

We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
5
votes
2answers
237 views

Is there a reason/benefit I am not aware of for this kind setup?

I now manage a MySQL database that was designed and created by another. I am confused about a certain setup that the original developer created.. He did not use auto increments for table IDs. ...
1
vote
2answers
1k views

Warnings: Why are statements with limit clause unsafe even if there isn't a limit?

I'm a bit irritated because of some warnings in mysql errorlog. Example 1: [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement ...
4
votes
2answers
29k views

“Copying to tmp table” extremely slow

This is my example of query: SELECT nickname, CASE class_id WHEN 1 THEN 'Druid' WHEN 2 THEN 'Necromancer' WHEN 3 THEN 'Mage' WHEN 4 THEN 'Priest' WHEN 5 THEN ...
2
votes
1answer
487 views

Design problem with auto-increment fields

I've made this design similar to my problem, it's not exact but it close enough to understand my problem: http://i42.tinypic.com/bhbp5.jpg Some cellular client frequently send their GPS position and ...
2
votes
1answer
2k views

Derby slows down after 1.2 million records

I'm using a Derby database, mass insertions slow down to 1/4 to 1/6 the speed once there are 1.2 million records (about 6 GB database size, one major table, other tables are tiny). Is that normal? Is ...
1
vote
2answers
255 views

Mysql slows down temporarily after large file copying

We have a MySQL 5.0 server on a Centos box. 32GB memory. We have only one harddrive, 374GB capacity, 50GB available. All our tables are MyISAM. When we copy a large file (2GB-3GB) within the hard ...
4
votes
1answer
2k views

Can SHOW TABLE STATUS query performance on MySQL be improved - Amazon RDS

I have a fairly large database on the Amazon Relational Database Service (RDS) running on MySQL 5.1.57. There are 900+ tables, so that running the "SHOW TABLE STATUS" query can take up to 30 seconds. ...
2
votes
1answer
2k views

Extract some columns data from one table and insert into multiple tables

I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables tbl_1: id, name, lastname tbl_2: id, ...
4
votes
1answer
682 views

Mysql crashed once with an “Assertion failure” this morning, how worried should I be? best way to diagnose this?

So for the first time our production system produced the below assertion failure. The system came up without issues after mysqld automatically restarted itself and rolled back some transactions.. ...
1
vote
1answer
143 views

Best solution for search scenario

I have the database structure: | Tag | | Article_Tag | | Article | --------------- --------------- ----------------- id tag_id ...
2
votes
2answers
2k views

Deleting Entries from Table not reducing my DB file

Iam usind INNODB Engine with innodb_table_per_file , so every table has it's own .ibd file instead of ibdata1 file. When i Entered 512 Record in my users table its size get increased to 200kb from 96 ...