MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

4
votes
2answers
200 views

Concurrency question?

I have a MySql (5.5) database with a MS Access frontend. If two users are viewing the same record at the same time how can I make each user aware of each other? For example how would I go about ...
2
votes
1answer
274 views

MySQL Update: why would rows matched change based on the set clause?

I ran an update query joining across a huge number of tables that updated one field, and it matched 29k some-odd rows. Running the same query with only an addition in the Set clause increased the ...
0
votes
1answer
134 views

Derived value not calculating as expected using combination of FK and Triggers (MySQL)

I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints. There are 3 tables involved in this arrangement responses ------------------------ ...
4
votes
1answer
2k views

What are user connections - when are the created and destroyed?

We've been getting an intermittent error on out site: Warning: mysql_connect() [function.mysql-connect]: User '557574_prod' has exceeded the 'max_user_connections' resource (current value: 10) ...
14
votes
3answers
19k views

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
4
votes
3answers
618 views

Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?

Is it possible to create an InnoDB table without a Primary Key? Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that ...
6
votes
3answers
7k views

How do I remove backslashes from records in MySQL

Long story short, I have backslashes in my data due to PHP magic quotes. I have straightened out what was wrong there, but have some records now which have unnecessary backslashes. 1 | This is an ...
5
votes
1answer
440 views

Dealing with temp tables when I have not control over db variables

I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically. I am wondering if there ...
6
votes
4answers
484 views

Purpose of IDs in a User Table Database

In a table, such as a user table, where there will only be 1 row with a for any particular user, why have an ID field, let alone as the primary key?
5
votes
4answers
4k views

MySQL: Index when joining to tables not being used (Performance optimizing question)

I need to optimize the following query: SELECT /* [things omitted] */ articles.blogpost_id, articles.id AS articleid FROM blogposts JOIN articles ON articles.blogpost_id = blogposts.id WHERE ...
4
votes
4answers
447 views

How much will using a varchar(15) PK affect my tables performance?

I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. ...
20
votes
4answers
6k views

Why does MySQL not have hash indices on MyISAM or InnoDB?

I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
7
votes
3answers
3k views

Does making a field unique make it indexed?

If I make a unique constraint on a field, do I also need to make an index on that field in order to get a scalable insert time? Or is this done for me (even if the index it uses isn't publicly ...
3
votes
1answer
168 views

Mysql constraint problem

I'm using MySQL5.5 and for this question I have 3 simple tables t1, t2 & t3 designed like this: +------------+ +------------+ +------------+ | t1 | | t2 | | t3 | ...
1
vote
1answer
169 views

What is the 'repair code' in MySQL?

OK I was adding an index to a table, and I checked on progress using show processlist and under state it output Repair by sorting. From the MySQL docs: Repair by sorting The repair code is ...
5
votes
2answers
259 views

Database replication with ability to revert to previous states

I want to setup a development database such that it'll initially be a mirror of the database used in production. At fixed points during the day (maybe midnight or something) and/or manually, I want ...
3
votes
1answer
2k views

Amazon SimpleDB or Amazon RDS?

I'm at a deciding point in my project of whether to implement the database using SimpleDB or Amazon RDS. The database needs to be accessed through an iPhone app and as well through a website. Here are ...
5
votes
1answer
718 views

Does MySQL still handle indexes in this way?

Dropping a duplicate index in MySQL was taking rather long, so while I was waiting I searched about it & found this post from 2006, talking about how MySQL handles ADD and DROP index. If a ...
6
votes
5answers
2k views

From a set of values, how do I find the values not stored in a table's column?

I have a table which will potentially store hundreds of thousands of integers desc id_key_table; +----------------+--------------+------+-----+---------+-------+ | Field | Type | ...
3
votes
1answer
88 views

Pure (My)SQL transformations

I'm trying to promote the usage of a database versioning system (http://code.google.com/p/flyway/) on the project I'm currently working on - and to that end when we make changes to the database, we're ...
0
votes
1answer
889 views

Entity Relationship Model for linked tables

Certainly a noobish question, but I'll ask it anyway. Perhaps someone else will be thankful. ^^ I am getting warmed up with MySQL and I try to understand the concepts of creating well structured ...
4
votes
1answer
297 views

How do I use the POSIX character classes upper and lower to search text in MySQL?

I would like to search a body of text to find out when a specific word has been upper cased. MySQL has a REGEXP function with the POSIX character class [[:upper:]], but for the life of me I can't get ...
3
votes
1answer
3k views

How do I make MySQL auto increment in random order?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
1
vote
1answer
409 views

Can I export a MySQL schema to latex? [closed]

I have asked this question on SO before, but did not get a good answer. I would like to be able to export a MySQL schema to a set of Latex tables. phpMyAdmin does this, but it is buggy and does not ...
1
vote
1answer
184 views

MySQL queries in SQL-Server?

How can we best use our existing MySQL queries with SQLserver? Alternately, what type of changes do we need when we are converting our database server from MySQL to SQLServer?
3
votes
2answers
392 views

What are the key points to avoid table locks?

MySQL is using at my company and most of the database administration works on trying to solve the table locks. What are the key points to avoid table locks(I mean at database side and even at software ...
2
votes
1answer
981 views

No results for match…against in boolean mode search with fulltext index (MySQL)

We have a column with a fulltext index, and are matching 'asp.net' against the column in boolean mode. We know there are records where this column contains the value 'asp.net', but the query is ...
1
vote
2answers
572 views

Sizing MySQL Database Servers for Drupal

What are some guidelines to follow when it comes to sizing MySQL Servers? CPUs, RAM, Disk Space, Clock Speeds, etc? We're going to go with either the community edition or the enterprise edition, and ...
3
votes
1answer
67 views

Database appearing in one tool but not another

Can anyone think of a reason why a mySQL database would be accessible from one tool (phpMyAdmin, logged in as db_user) but not from another (Sequel Pro, logged in as db_user). The user is the same in ...
2
votes
2answers
338 views

How to get a user's friends' names?

I have a users table and friends table. The user with ID 1 has 4 friends (with IDs 2,3,4,13). I want to get names and surnames of users 2,3,4 and 13. I can to it with standard queries but it will slow ...
1
vote
1answer
358 views

Use order from another select clause

So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one. I have this statement to get the vid column order ...
4
votes
2answers
684 views

How to retrieve granted privileges without access to the database “mysql”?

As a MySQL user without access to the mysql database, is it possible to retrieve my privileges?
7
votes
4answers
4k views

How do I do a complex GROUP BY in MySQL?

I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
4
votes
2answers
565 views

More table or more database for better performance?

Lets say 1 website need 10 database table to work and i want a database pool that 40.000+ website will be inside. Which option is better for server performance? 1) 400 databases each have 1000 ...
5
votes
1answer
5k views

MySQL high availability, failover and replication with Latency

We are in the process of implementing a new CMS (Drupal 6.x) that runs on MySQL. We have two data centers -- primary and secondary -- with known latency between them. We're unsure which version of ...
4
votes
4answers
954 views

Is tuning the innodb_buffer_pool_size important on Solaris ZFS?

We're running a moderate size (350GB) database with some fairly large tables (a few hundred million rows, 50GB) on a reasonably large server (2 x quad-core Xeons, 24GB RAM, 2.5" 10k disks in RAID10), ...
1
vote
1answer
2k views

One MySQL user from 2 hosts not on same subnet

I've googled around for this with no luck. I have a master database with one slave. The perms for the slave are set and it runs fine. I'd like to add another slave using a clone of the original ...
6
votes
2answers
11k views

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...
4
votes
1answer
2k views

nest SELECT in INSERT

I'm trying to do something like: INSERT INTO stories (vid, ip, body, timestamp) VALUES ( (SELECT vid+1 FROM stories ORDER BY vid DESC LIMIT 1), INET_ATON('127.0.0.1'), 'test', ...
15
votes
6answers
6k views

Storing IP address

I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column? Should I support IPv6 as well? If so, what is the ...
7
votes
1answer
203 views

Are relations slower than a big, inefficient table?

I've been asked in my job to violate the first normal form (repeating groups across columns, using empty / null values) several times, "for the sake of computer processing power". In a nutshell, a ...
2
votes
2answers
542 views

Is there a way to have MySQL GROUP BY utilize trailing whitespace?

I recently had an issue come up where I noticed that MySQL would ignore trailing whitespace when issuing a GROUP BY. Here's the table: CREATE TABLE `myTable` ( `id` int(10) unsigned NOT NULL, `foo` ...
5
votes
3answers
879 views

MySQL client believes they're in a transaction, gets KILLed, wreaks havoc

Open up two mysql command-line clients and connect to your database. In client #1, enter START TRANSACTION; In client #2, use SHOW PROCESSLIST, then KILL [n]; where n is the id for client #1's ...
0
votes
2answers
483 views

How wrong is my web app modeling schema?

So, I'm about to write a quite simple web application, I am currently working actively on the database schema, and I need help to avoid major drawbacks. I've been programming (php) for a few years ...
3
votes
2answers
2k views

MySQL 5.5.9 Wont Start

I installed mysql version 5.5.9 on my mac and I tried to start it using this command : sudo /usr/local/mysql/support-files/mysql.server start mysql didn't start with this command. I checked the ...
2
votes
3answers
236 views

multi-table mysql query

I am trying to make a multi-table query that I am not quite sure how to do properly. I have User, Message, Thread, and Project. A User is associated with Message/Thread/Project as either the Creator ...
2
votes
1answer
685 views

Query between two related tables

I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are ...
2
votes
1answer
265 views

databases/tables with nonconventional names

Disclaimer: not an administrator How does one deal with non-kosher names such as 'data?base' ? I'm always one to ensure the cleanliness of my databases, but after finally installing phpmyadmin to ...
0
votes
1answer
202 views

How to design databases? [closed]

Describe the process in steps, please. What tools do you use for it?
2
votes
1answer
569 views

Question on Design for User + User Groups + User Types + Subscriptions Model

I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, ...