Questions tagged [mysql]

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.

Filter by
Sorted by
Tagged with
20
votes
4answers
804 views

Is "CREATE INDEX` in MySQL a Linear Operation?

What I mean is the following: If creating an index on a table with n rows takes t time. Will creating an index on the same table with 1000*ntake approximately 1000*t time. What I'm trying to achieve ...
17
votes
4answers
35k views

How do I find out if a procedure or function exists in a mysql database?

How do I find out if a procedure or function exists in a mysql database? and is there any discovery option? like a show procedures; (e.g. like show tables;)
19
votes
2answers
7k views

Can I create index on a table in a MySQL database while in use

And if I can, any reason (performance/stability) why I shouldn't?
2
votes
2answers
511 views

Transferring concepts - users/groups/permissions of db objects from mysql to postgresql

This seemed like a better fit here than stackoverflow, so here goes: I've been using mysql for a while since on most of the cheap webhosts it is the provided database. However, now I've started ...
13
votes
5answers
4k views

Is it unwise to run replication on same physical server?

I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
5
votes
1answer
361 views

MySQL and Solid State Disks on EMC

Since all writes and sequential reads are cached by the EMC storage system, the best use for solid state disk is to store data that has a random READ pattern. On Oracle, that is the temp tablespace ...
7
votes
3answers
2k views

Can you “su -” in MySQL?

If I have root on a mysql database and I want to drop privileges to that of a normal user, without using their password, can I do it? if so how? think # su - username on unix. Basically, I'm just ...
3
votes
1answer
646 views

MySQL CREATE statement are really slow (Mac OS X / Macport install) [closed]

I recently noticed that recreating the database for the web application I am working on is taking a lot of time. I had just reinstalled Mysql from Macport... I am getting really lousy performances, ...
5
votes
1answer
1k views

Foreign Key violation - not sure why

I have the following two tables with a foreign key on status_id: mysql> describe usr_cookbook; +-----------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | ...
25
votes
8answers
22k views

Tool to generate large datasets of test data [closed]

Many times when trying to come up with an efficient database design the best course of action is to build two sample databases, fill them with data, and run some queries against them to see which one ...
18
votes
4answers
2k views

How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours?

is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to ...
9
votes
1answer
13k views

Error creating foreign key from MySQL Workbench

I'm trying to synchronize schema changes from MySQL Workbench to my database. I'm getting the following error when it tries to create a foreign key: Executing SQL script in server ERROR: Error 1005: ...
43
votes
6answers
24k views

Performance implications of MySQL VARCHAR sizes

Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure ...
19
votes
2answers
36k views

What is the difference between MySQL VARCHAR and TEXT data types?

After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types? I was reading the list of ...
21
votes
3answers
15k 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 ...
29
votes
1answer
50k views

INT(5) vs SMALLINT(5): numbers in parenthesis after the numeric type

In MySQL table definitions is there a different between INT(5) and SMALLINT(5)? Or do they both represent the same size?
13
votes
3answers
6k 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 ...
7
votes
1answer
2k 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 ...
6
votes
2answers
468 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
157 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.
8
votes
3answers
202 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 ...
96
votes
5answers
25k 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 ...
30
votes
3answers
7k 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 ...
6
votes
4answers
2k 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
372 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?
39
votes
5answers
66k 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
191 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.: ...
133
votes
13answers
244k 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
5k 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
2k 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 ...
4
votes
1answer
377 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 ...
28
votes
3answers
41k 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 ...
18
votes
6answers
335 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 ...
8
votes
3answers
317 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
2k 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 [closed]

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 ...
41
votes
2answers
18k 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 ...
24
votes
5answers
11k views

How to create a conditional index in MySQL?

How to create an index to filter a specific range or subset of the table in MySQL? AFAIK it's impossible to create directly but I think it's possible to simulate this feature. Example: I want to ...
12
votes
5answers
8k views

Are there any MySQL Benchmarking tools? [closed]

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 ...
23
votes
5answers
49k 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 ) ?
173
votes
7answers
202k 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 ...
38
votes
5answers
6k 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?
67
votes
5answers
10k views

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
242
votes
10answers
238k views

What are the main differences between InnoDB and MyISAM?

What are the main differences between InnoDB and MyISAM?
5
votes
3answers
2k 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 ...
3
votes
4answers
11k 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 ...
3
votes
3answers
14k 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
2k 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 ...
38
votes
8answers
191k 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...
10
votes
9answers
11k 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: ...