All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

0
votes
1answer
585 views

Mysql - Problem speed of access on query (Low latency query sometime)

My web application is real time site that need a speed of access on every query (SELECT, UPDATE, INSERT) that must less than 200ms Because one of my php script task is query (SELECT, UPDATE, INSERT) ...
0
votes
1answer
448 views

MySQL Slave Lagging Almost Daily -Ver 5.5.17 ( Master & Slave )

I am observing this lag in a continuous manner. Investigating in the bin log events, i found the following information. Slave executing the relay Logs slowly. 1. Changing the Innodb File Format ...
3
votes
3answers
743 views

Mysql 5.5 strange behavior with group by with limit based of INDEX vs INDEX FOR GROUP BY

* Update * This bug has been fixed in 5.5.27, 5.6.7 and 5.7.0 I feel like I've found a bug in mysql, but haven't been able to track it down. Any idea what's going on here? This was working just ...
3
votes
1answer
792 views

How this simple Stored Procedure ended up in Deadlock

Thanks for all and this forum is really helpful. After few minutes of system running we get “Try restarting the transaction.” And its end up in deadlock. We use MySQL 5.5.28 and the Java code ...
3
votes
2answers
342 views

Are there implicit indexes in InnoDB like MyISAM?

If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys: (a) (a, b) (a, b, c, id) Is this valid for ...
1
vote
1answer
82 views

Mysql performance for a nullable unique id column in a large table that references a record in another table

We have a large table of contacts (8 million records) that we promote to with mailings. When these people register we store them in a different members table. But we want to go back and update the ...
1
vote
1answer
817 views

MySQL my.cnf won't take any effect

Update 7 As DerekDowney suggested, I tried to use [mysqld] init_connect=SET NAMES 'utf8' But I could not get the desired result. My client side java programme still says both session and global ...
1
vote
1answer
274 views

Unique index on 2 columns in mysql

I have one table in mysql named 'UserFriends' where I am updating my websites user's friends details. here is the schema of the table (UserFriends) id int, Userid int, friendid int, createdate ...
0
votes
1answer
206 views

how to use mysql to delete only the first ten table records?

I'm currently using php and mysql together. What I want to try is if I have ten "stable" records for the database and in the same .php file I have some text boxes so I can type in then what I typed ...
5
votes
1answer
501 views

How to write this self join based on three columns

Hello there I have a following table ------------------------------------------ | id | language | parentid | no_daughter | ------------------------------------------ | 1 | 1 | 0 | ...
0
votes
0answers
61 views

InnoDB best index practises

I have an InnoDB table with around ~3.7m rows in it. Here are my current indexes: ...
1
vote
1answer
578 views

Best way to query huge data

First let me describe my case: I need to store around 200extra detail of each user. I am using wordpress. Now there is a default function to save data associated with user. But i am not sure what ...
5
votes
1answer
899 views

SQL Server / MySQL to DTD (+XML)

Is there any way I can at least export a table schema from MSSQL or MySQL to a DTD file? Also the same for the data of the tables to XML. I am really not getting how to write DTDs, so I badly need a ...
1
vote
0answers
119 views

MySQL Partitions

I've done a lot of work with MySQL but generally on a much smaller scale than this, so please excuse my ignorance. We've got an InnoDB table which is currently storing ~3.5 million rows, which I know ...
4
votes
2answers
6k views

SELECT TOP in MySQL

How can I do this in MySQL? SELECT TOP 50 PERCENT * FROM table What's the simplest way to do it without running to separate queries (if possible)?
2
votes
2answers
594 views

MySQL export user with semi-colon “;” on the end

I am trying to set up automatic export of user accounts from one server to import into another. I am running the following command; $ mysql -u root -p -B -s -e "show grants for 'myuser'@'localhost';" ...
1
vote
0answers
147 views

TMD auto create

I used mysql as part of a project and found it to be a very practical tool. I have a question about mysql in regards to how it creates temporary table types TMD in /var/lib/mysql/. This partition ...
2
votes
2answers
9k views

How to continuously keep two databases in sync

Me and a friend are developing a range of programs which uses a basic currency called "points" each user can gain and spend points for free. The problem we face is MySQL we can connect to the ...
3
votes
1answer
452 views

Can I use mysql to serve 100's of millions of small image files?

I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the ...
0
votes
3answers
2k views

MySQL - What exactly does smallint(2) permit? [duplicate]

Sorry for the really simple question, but I can't figure it out. When I set up my schema, I set up my primary key to use a field of type smallint(2). The thought behind it was that the field should ...
6
votes
2answers
2k views

Why does MyISAM support FULLTEXT Searching and InnoDB does not?

I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
1
vote
1answer
26 views

Reserving dates with quantities, table schema wrong?

Okay I have a table like so. ID Productname Quantity Image QuantityOut LastDateReserved 1 productOne 5 'image.jpg' 3 2013-03-15 2 ...
0
votes
1answer
163 views

New Database vs New Table for User Registrations

Here's an example, there is this application that completes a task (mostly data-enrty stuff). Users can sign-up on a website to begin using the application straight away. At the moment, I was looking ...
0
votes
2answers
346 views

Unique indexed column in where clause and mysql execution after finding first row

Suppose in a table there are 2 columns: login and password login is indexed as unique index, password not indexed. query: SELECT * FROM mytable WHERE login = 'Jhon' query: SELECT * FROM mytable ...
2
votes
2answers
334 views

Tinyint structure is confusing

I am confused to see this tinyint(3) structure. If tinyint can store only 1 byte then what is the purpose of giving the size in brackets? Is there something special with this parameter in MySQL?
0
votes
1answer
219 views

Is using the alpha version of Percona 5.6 safe? [closed]

I'm really sorry if my question is kind of very basic, but is something that is really making me thinking a lot about it for a long time. As Percona has a lot of lines of optimizing codes, I run it ...
1
vote
2answers
4k views

Does MySQL Replication hamper the performance of my DB?

I am not exactly a 'Qualified' DBA, but yes i am in charge of my own DB which we use in our social app. I have recently implemented Master/Slave Replication on my Database for obvious reasons. What ...
3
votes
4answers
1k views

Stress Test to Saturate Memory for MySQL InnoDB

I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of ...
1
vote
3answers
232 views

MySQL Permissions

A web application that we're working on creates a new database for each user that signs up. With this, I'm setting up a database user for each of these new databases and wonder what the best ...
2
votes
1answer
5k views

Two insert statements inside trigger - Duplicate entry '521-2' for key 'PRIMARY'

I'm trying to create an insert trigger so that when a new user is inserted into users table (via site, httpd and php), part of this information is inserted in another table that manages access to a ...
1
vote
1answer
128 views

Single entry point for mysql cluster

I have a mysql cluster setup and working. When connecting to it I specify one of the mysql nodes (mysqld) in my connection string. However, if this node goes down it doesn't automatically fail over to ...
3
votes
4answers
2k views

Optimize Query with Derived Table

I have the following query select ps.id, ps.title , IFNULL(s.likes,0) as num_likes , IFNULL(s.comments,0) as num_comments , IFNULL(s.ratings,0) as num_ratings , IFNULL(s.views,0) as num_views , ...
0
votes
2answers
231 views

Find the average number of n:m connections of two tables?

I want to know to how many lists a user is assigned in average. User ==== id name Lists_Users =========== user_id list_id List ==== id title Is there a good way of receiving this number via ...
1
vote
2answers
93 views

Database and query optimizacion

I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records ...
1
vote
1answer
1k views

Segmentation fault (core dumped) with self-complied mysql-5.1.9-beta.tar.gz

I am compiling mysql-5.1.9-beta.tar.gz source code under Ubuntu 12.04. My steps are following: tar -xzf mysql-5.1.9-beta.tar.gz cd /home/gaoyu/mysql-5.1.9-beta ./configure ...
9
votes
1answer
5k views

How can I determine the size of a dump file prior to dumping?

How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?
1
vote
2answers
129 views

What settings should I use in my.cnf

We have about 5-6 different internal webapps (staff use only) that we will be transferring across to a new virtual server, however I need to setup our my.cnf settings first and I am lost (I am web ...
1
vote
1answer
69 views

INNODB real-time locks?

How can I view real-time locks in innodb when information_schema.innodb_locks is not available?
3
votes
2answers
658 views

MySQL index on column comparison

How do you go about setting an index to be used for a column comparison like WHERE col1 > col2? col1 and col2 are of DATETIME type.
2
votes
1answer
744 views

MySQL monitor prompt does not return for slave

I'm trying to fix a replication error (or an ongoing failure to replicate) between our master and slave MySQL databases. The procedure I have asks me to use MySQL Monitor to stop the slave. stop ...
0
votes
0answers
85 views

Checking of one specific element of one array is in another array?

Suppose I have two arrays. One looking like (3, 9, 10, 39), and one looking like (4, 10, 9, 48, 391, 2). How would I (through pure MySQL) check if one of the elements in the first array is inside the ...
-4
votes
1answer
731 views

where can I get mysql-5.1.7.tar.gz or mysql-5.1.9.tar.gz? [closed]

I need mysql-5.1.7.tar.gz or mysql-5.1.9.tar.gz, but I can not find them anywhere, who can give me a link to find them, thank you very much!!!
-1
votes
1answer
346 views

How can I move 5 GB data from one database to another faster?

I'm using MySQL as database with Windows server. I'm having 5 GB of data in database. Now I need to change my server and the question is that how can I transfer complete 5 GB data to one mysql ...
1
vote
1answer
134 views

big db for managing user files and settings

Hi im about to start a project that will hopefully eventually hit milion+ users which is some kind of a site managment, problem is how to set the db in mysql, some people suggest way 1 and others way ...
0
votes
2answers
5k views

how to set a row's value from a certain row's value?

I have a MySQL table named "activities" with fields id(int), tstamp(timestamp), amount(decimal) balance(decimal). Decimal fields hold money values. id tstamp amount balance ...
1
vote
1answer
261 views

LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?

I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE. But we have 1GB each day we want to update in a batch process using ...
1
vote
1answer
835 views

Does MySQL limit the number of users (not connections)

I have always believed that you can create as many users as you like. I have been creating a user per app with limited database/table access for only what that app requires. Recently I was told there ...
3
votes
1answer
2k views

Selecting minimum value using a subquery

I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating. We have a table for prices ...
1
vote
1answer
5k views

MySQL 5.6 OS X 10.8: how tell if my.cnf read?

With mysqld running, I expect to see that /etc/my.cnf has been used by at least one of the commands ps aux | grep mysql | grep -v grep mysqladmin variables | grep defaults-file showing the value ...
1
vote
1answer
43 views

Synchronize creation of rows between tables

Lets say I have two tables, posts(id_pk,title) and posts_content(post_id_pk,blob). Whenever I create a row in posts, I want a complimentary row to be created in posts_content, with the same id. If I ...