MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

13
votes
3answers
4k views

Is it common practice to mix InnoDB and MyISAM tables on same server?

I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
12
votes
1answer
14k views

INT(5) vs SMALLINT(5)

In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?
8
votes
3answers
3k views

Dynamic SQL in MySQL stored routines

According to the restrictions on stored routines and triggers, dynamic sql cannot be used (restriction lifted for stored procedures in version 5.0.13 and later). Why is this limitation in place? And ...
6
votes
1answer
1k views

What are some important MySQL status variables to keep an eye on? [closed]

I'm a fairly new DBA with a programming background. As such, I like to build tools to manage tasks to help me familiarize myself with the intricacies of a technology. In this case, I am managing ...
4
votes
2answers
389 views

different login credentials based on user

I run a PHP symfony-framework web app for my company and it uses a single database login for all connections, whether the user is an admin or a client. I know that higher security systems often use ...
1
vote
1answer
128 views

version controll application/system for mysql databases [duplicate]

Possible Duplicate: Keeping track of schema changes Hello, How do you keep your mysql database schema under version control.
7
votes
3answers
192 views

Is there a performance gain by handling data with stored procedures versus feeding it into functions after retrieval?

Title sums it up. I'm reasonably well off with PHP, and comfortable getting it to do what I need it to with values pulled from MySQL, however in the interest of expanding my knowledge I've been ...
46
votes
4answers
5k views

Storing vs calculating aggregate values

Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly? For example, suppose I have widgets which users can rate (see schema ...
20
votes
3answers
3k views

Character vs Integer primary keys

I'm designing a database with multiple lookup tables containing possible attributes of the main entities. I'm thinking of using a 4 or 5-character key to identify these lookup values rather than an ...
5
votes
4answers
620 views

Is there a way to measure replication lag in MySQL with a resolution less than 1 second?

Is there a way by which you can measure replication lag in MySQL with a resolution of less than 1 second? That is, can replication lag be measured on the microsecond or millisecond level?
5
votes
1answer
206 views

What are the pros and cons of using the Sphinx Storage Engine?

What are the pros/cons of using the Sphinx Storage Engine in MySQL (doc) over the normal api?
19
votes
5answers
28k views

How do you tune MySQL for a heavy InnoDB workload?

Assuming a production OLTP system with predominantly InnoDB tables What are the common symptoms of a mistuned/misconfigured system? What configuration parameters do you most commonly change from ...
4
votes
2answers
163 views

How to create multiple entries on index based on the fields of one row?

I never found a good way to index multiple fields of one row as entries of an index or simulate this feature on MySQL. The problem arises when you have fields working as tags or similar concept. Ex.: ...
54
votes
14answers
97k views

How can I move a database from one server to another?

How can I move MySQL tables from one physical server to another? Such as this exact scenario: I have a MySQL server that uses innodb table and is about 20GB size. I want to move it to a new server, ...
7
votes
4answers
2k views

For a MySQL query, how do you determine physical and logical I/O?

I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index ...
7
votes
1answer
1k views

What's the maximum number of tables that can be referenced in a query in MySQL?

In the days of SQL Server 2000 and 2005, the maximum number of tables in a query was 256. Does MySQL have a similar limitation? I know that such query of such size means that either there's ...
3
votes
1answer
293 views

Using CoreData on a client-side application

This may be a bit more of an SO question, but... If I'm writing a client-side application for OS X that's going to sync with an online MySQL database, is there any difference on if I have CD use ...
12
votes
1answer
17k views

How can I change the default storage engine in phpmyadmin?

I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
14
votes
6answers
276 views

Securing DB passwords

Looking at the structure of most PHP/MySQL-based websites I've seen, it appears that it's not terribly difficult to discern the database password if you dig a bit, as there's invariably a setup or ...
6
votes
3answers
258 views

What does the output of a JOIN statement look like?

I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use. Let's say I have 2 tables: CREATE TABLE Cities ( id INT UNSIGNED PRIMARY ...
8
votes
3answers
1k views

MySQL LDAP authentication

Is LDAP authentication and authorization in MySQL possible? What are the requirements (MySQL version, third-party software etc.)? How it's organized? Thanks.
7
votes
6answers
1k views

Alternative Database Tools for Web Admin

I currently use phpmyadmin for most of my database administration. It works well enough, but I'm interested in other tools that might work more quickly/efficiently/etc... What are the best and/or ...
30
votes
2answers
7k views

NULL or NOT NULL by default?

In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls? Or doesn't it matter? I know in some DBMSs they ...
14
votes
4answers
2k views

How to create a conditional index on MySQL?

How to create an index to filter a specific range? AFAIK it's impossible to create directly but I think it's possible simulate this feature. Example: I wanna create an index for NAME column just to ...
10
votes
4answers
4k views

Are there any MySQL Benchmarking tools?

I've heard a long time ago that there is this tool that helps you tweek mysql settings for better performance but i can't seam to find it. I am aware that I can use ab for apache to simulate high ...
10
votes
5answers
4k views

Where can I find the mysql slow log?

Just as the title says, where can I see it ? Are there any config options for it ( like how many ms would determine if a query is slow or not ) ?
67
votes
6answers
54k views

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
27
votes
5answers
4k views

When is the right time to use MariaDB instead of MySQL, and Why?

What we earn and what we lost with this migration? What should I expect as drawbacks after the migration? Is it really unnecessary to change the applications in any situation?
55
votes
5answers
4k views

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
66
votes
8answers
50k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
4
votes
2answers
750 views

With MySQL Replication, what level of resilience is possible?

I've found lots of good material on setting up MySQL replication, but not much on what to do in the event of failures. Understanding the degree of resilience I can achieve is fundamental to designing ...
0
votes
4answers
2k views

How to do a mysqldump with a use database in the dump

So the command I normally use to get a database sent to another server is: mysqldump -u user -p --add-drop-tables database *file* But I always have to go into the file and add a line at the top: use ...
2
votes
2answers
4k views

In MySQL Workbench, insert a new column at the “top” of a table?

I must keep a table's columns as specified in a specification that changes quite often. In MySQL Workbench, adding a new columns after all the others is easy, but I can't find a way to insert a new ...
2
votes
1answer
1k views

MySQL : “FLUSH TABLES WITH READ LOCK” started automatically

I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being ...
24
votes
6answers
61k views

MySQL DB import/export command line in Windows

How to export / import a database in MySQL through the command line? MySQL system configuration, WampServer server installed. OS: Windows The advice for my local host, not an live hosting...
4
votes
8answers
7k views

Load-balanced MySQL cluster without load balancer

I'm looking to create a load-balanced MySQL cluster, but without the actual load-balancer, in order not to add another point of failure or complexity. What I was thinking was to have the following: ...
27
votes
9answers
9k views

Is there a tool like Microsoft's “SQL Server Profiler” for MySQL?

While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler. For ...
30
votes
7answers
35k views

How do I completely disable MySQL replication

I am running dual master MySQL replication and now want to move to a single database with no replication. How do I completely disable replication on both databases?