MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
4answers
5k views

Benefits of BTREE in MySQL

What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage? Does BTREE provide easier iteration in increasing order ? What kind of queries would ...
4
votes
1answer
327 views

How badly does innodb fragment in the face of somewhat out-of-order insertions?

Let's say there's a composite primary key on (field1, field2) Also, let be X a theoretical set of n numbers Inserts look like this: INSERT INTO table(field1, field2) VALUES([a number picked at ...
4
votes
1answer
3k views

Innodb, MySQL 5.5.28 - Segmentation Signal 11 faults, on high load. .. my.cnf file included

We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28 . Doing random imports to large InnoDB tables, over 50+ gigs, randomly after a few hours of ...
3
votes
1answer
476 views

Does the Int type primary key insert order affect query performance in MySQL?

Table t has an Int type primary key id without "Auto Increment" CREATE TABLE `t` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ); Use the following three ways to ...
2
votes
1answer
3k views

I have to update all the rows of a Column in a table. Here is my query

So My tables are People: People_name(VARCHAR 100), People_id (INT, PRIMARY KEY AUTO INCREMENT) Tracks: Track_id(INT PRIMARY KEY), People_id_references (int ,Foreign key references ...
4
votes
2answers
12k views

What happened to my-*.cnf in MySQL 5.6.8?

Up to MySQL 5.6.7, the official RPM was shipping with these files: /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-large.cnf ...
1
vote
1answer
94 views

MySQL 5 on Mac 10.5: “problem connecting to the database”

I have worked with MySQL for years but I am stuck on a seemingly trivial problem. I installed MySQL 5.0.96 on Mac 10.5, created a database, created a user with privileges on everything, and problem: ...
1
vote
1answer
1k views

How to write signal function in MySQL that can be called from Triggers and Stored Functions?

In my Database I have a table: Employee with recursive association, an employee can be boss of other employee. The Table Description: mysql> DESC Employee; ...
1
vote
1answer
182 views

MySQL performance tuning

I would like to optimize my MySQL DB. I noticed some important points as follows: Enabling Query cache. Using memory storage. (I know pluses and minuses of this. Being I am having static tables, I ...
1
vote
2answers
751 views

MySQL: many Sleep processes

I've queried SHOW processlist as root after a server restert, and got a long list of Sleep threads: +-----+-------------+-----------------+---------+---------+------+-------+------------------+ | Id ...
1
vote
1answer
883 views

High MySQL CPU usage, queries seem fine, not running ntp

I've had about 48 hours of extremely high CPU usage on my main MySQL database for no apparent reason. I realize this question is quite similar to the one posed here: High CPU usage from MySQL with no ...
0
votes
1answer
799 views

Too many records on database and how to handle it? MySQL is it good to clone?

I am doing a social network and came up with the following concern regarding database storage. Let's put facebook as an example, imagine 2,000 photos per user, just 1,000 users, it would equal to ...
1
vote
1answer
593 views

compare/sync databases from different computers

I have a server online and I have installed MySQL and database A server on it. I also have mysql installed on my local computer and have a copy of database A on it. I want to compare the contents ...
3
votes
1answer
527 views

Complex SQL Query

I'm trying to formulate a single query to return a nice list of a host's interfaces and the interfaces attributes from the Cacti MySQL DB. Here are the tables I am referencing: host_snmp_cache: ...
0
votes
1answer
89 views

Two slaves using the same server-id

I have a MySQL master with two slaves. Due to poor configuration both slaves had the same server-id. My questions are: Is the slaves' data fully synced with the master or did they miss transactions ...
1
vote
1answer
285 views

Database inserts/updates slow only in Production

We have a int/test/staging/production Mysql database setup. A Java application(using Hibernate) that periodically generates up to 200,000 updates/inserts (mainly updates by 20 to 1) completes in 10 ...
0
votes
1answer
40 views

Not able to create a new table new_table from t1 by comparing primary key values of another table t2 in MYSQL

I am trying to create a new table new_table from t1 by comparing primary key values of another table t2 ie, 1) created table new_table 2) gave the query insert into new_table select a.* ...
-1
votes
1answer
584 views

When to use MySQL handler syntax?

http://dev.mysql.com/doc/refman/5.5/en/handler.html Seems to be very useful for PK lookups, but I haven't seen much information regarding this. What's the pros and cons of the handler syntax vs. ...
2
votes
1answer
406 views

Restriction on self referencing on insert in MYSQL?

How to restrict insert on adding self referencing rows in a recursive relation table (a table contains foreign key points itself). mysql> SELECT * FROM Employee; ...
0
votes
0answers
47 views

mysql select query optimization [duplicate]

Possible Duplicate: mysql select query optimization I have two tables, testa and testb. CREATE TABLE `testa` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) DEFAULT NULL, ...
-3
votes
0answers
35 views

Doubt :: my sql performance tuning [duplicate]

Possible Duplicate: MySQL performance tuning I would like to optimize my MySQL DB and I identified some important points as follows: enabling query cache, using memory storage (I know ...
0
votes
0answers
301 views

Custom doctrine default character set [closed]

In my.ini mysql file, i have this : [mysqld] collation-server = utf8_general_ci character-set-server = utf8 When i create my database with "doctrine:database:create", my collation db is in ...
-1
votes
1answer
226 views

Exporting .txt file (tab-delimited) to mysql

Is there any ways that I can replace my exported .txt file in mysql database table with an updated .txt file without making duplicates? Step 1: after creating table in my database in mysql, I ...
3
votes
2answers
189 views

Is it always bad to use SELECT * FROM?

It is widely recommended NOT to use SELECT *. I understand this, as it is not a good idea to bring the entire row into memory, when only a few cells are needed. Is it still bad to use SELECT * when ...
1
vote
1answer
499 views

My SQL server showing different result with same query execution at different time

When I execute below query from MySQL server, it shows me different output for each time even though database is stable and not used by anyone except me. SELECT TABLE_NAME , table_rows FROM ...
3
votes
1answer
178 views

Referential Integrity in schema, cascading the renaming

In phpMyAdmin, while renaming a column/table which is referenced by some foreign key, I get an error. I can not rename a column until I remove all its relations. There should be facilitated integrity ...
4
votes
1answer
162 views

How can I improve this DB query? it takes too much time

The query is this: $today = $date->getTimestamp(); SELECT t.*, u.screen_name, u.profile_image_url FROM table AS t LEFT JOIN users AS u ON t.user_id=u.id_str WHERE ...
4
votes
1answer
507 views

How to Update same table on deletion in MYSQL?

In my Database I have a table: Employee with recursive association, an employee can be boss of other employee. Following is query I used to create table: CREATE TABLE IF NOT EXISTS `Employee` ...
0
votes
2answers
335 views

Need advises for a new MySQL architecture

I need to deploy a MySQL-based application with the following requirements and would love to receive some guidance (any) as to how I could set things up: Scale of deployment 5 servers with a ...
1
vote
1answer
188 views

Why does a MySQL MEMORY-backed table take so much more space than on disk?

I have a single InnoDB table on disk, with ~1,107,423 rows and taking up 45.6 MiB disk space. The structure is: messageid int(11) signature varchar(255) When I create a MEMORY-backed version of ...
4
votes
1answer
80 views

Selecting Without Repititions

I have table with observations of objects moving along edges in a graph, this table has the following form: PK | TIMESTAMP | object_id | from_id | to_id where object_id is the id of some object and ...
2
votes
1answer
143 views

Ranking System with Different Categories

I want to create a system where the user can be ranked by several different factors. For example, the user competes in the following categories Wins Points I need to know what the position of the ...
2
votes
1answer
680 views

Update ranking on table

I have two tables: mysql> desc rank ; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
4
votes
1answer
477 views

I have multiple ibdata files : What should I do with them?

I have 3 different ibdata files on disk. What is the difference between them? Can I remove some of them? The goal is to migrate the files from C: to E:... I'm on MySQL 5.0.x... Directory of C:\MySQL ...
0
votes
2answers
67 views

View for a MANY_MANY_MANY relationship

Let's say I have 3 main tables: category, tag, url category and tag have a MANY_MANY relationship (category_tag). tag and url have a MANY_MANY relationship (url_tag). I would like for a given ...
2
votes
1answer
631 views

inner join on PK with extra criteria slow despite indices?

Given the two tables below I am struggling to understand: why is the third query slow even though first two queries are fast what exactly is EXPLAIN saying can I do anything to significantly speed ...
11
votes
2answers
11k views

Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?

I have a unique compound key like fr(fromid,toid) in the table, when I run the query with explain I get the following result: Impossible WHERE noticed after reading const tables` The query I ran: ...
-1
votes
2answers
292 views

How to update an SQL column using a known perm link while appending a value from another column within the same table? [closed]

Larger view of the issue is here: http://i1268.photobucket.com/albums/jj572/LinuxOwner/question.png
6
votes
2answers
5k views

How to correctly implement optimistic locking in MySQL

How does one correctly implement optimistic locking in MySQL? Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but ...
0
votes
0answers
36 views

How does one correctly implement optimistic locking in MySQL? [duplicate]

Possible Duplicate: How to correctly implement optimistic locking in MySQL Our team has deduced that we must do #4 below when doing optimistic locking (all data is read and update using a ...
2
votes
2answers
117 views

HELP with SQL WHERE syntax

I have to pull out a range of entries of a database, all based around two pieces of data. In itself thats fine. An example of the data is: Record ID Tags 1 1423 Day, Sun, Warm 2 ...
1
vote
0answers
115 views

How to create multi volume file backup using symantec backup.exec

I am using symantec backup .exec for backup and restore in mysql server . Now I am in need to take larger database backup in tape .But it exeeds the tape storage and I am trying to create a multi ...
0
votes
1answer
151 views

SQL Table Indexing - Database Overloaded With Queries

I'm a bit of a newbie to SQL who has been thrown in on the deep end with a server that gets about 2 million queries an hour. I'm trying to optimise our database and I noticed this in our database ...
2
votes
2answers
178 views

measure the traffic throughput on mysql table

In order to split mysql database into two different databases on different hosts. I would like to know few things: what is the size of the tables. what is the traffic throughput to those tables. ...
3
votes
4answers
4k views

mysql : inner join takes 3 minutes

I had a similar question open on SO for postgres - now having the same issue with mysql.. I have two tables - Table A : 1MM rows, AsOfDate, Id, BId (foreign key to table B) Table B : 50k rows, Id, ...
4
votes
1answer
161 views

Do isolation levels only apply to SELECTS and not UPDATES? [closed]

Scenario that might demonstrate different behavior for SELECTS depending on the isolation level: 1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete 2) 0:02 Thread B ...
1
vote
1answer
2k views

How to know the current log sequence number in 5.0.X

I would like to test the optimal value for iblog files on my mysql which is 5.0.77 I found the below procedure in mysqlperformance blog: ...
3
votes
2answers
28k views

Select column names whose entries are not null

I would like to have a list of those columns of a table that have at least one not-NULL data entries in them. In other words, I would like to get the column names for which the following returns at ...
1
vote
2answers
2k views

Cache all mysql table

Maybe it sound's like i'm an idiot... But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been ...
1
vote
1answer
1k views

SQL query to get the syntax of an existing table

Is there any SQL query to get the syntax of an existing table in my MySql database? Intent: Iam in the early stages of working with databases and I am using the MySql workbench for creating tables ...