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
1
vote
1answer
565 views

Precomputing expensive aggregations

I'm looking for a tool that will let me precompute expensive aggregations some time after rows have been inserted into a MySQL database. For example, if I often run a SUM() on a column, I'd like to ...
74
votes
4answers
105k views

What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data: create table t ( k int, v int, index k(k) ) engine=memory; insert into t (k, v) values (10, 1), (10, 2), (10, 3); When ...
8
votes
3answers
6k views

How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
1
vote
1answer
1k views

How can I get the maxium number of concurrent connections to MySQL over a specific time interval?

I have a monitoring app that checks MySQL's Threads_connected and Max_used_connections variables every X seconds. Neither of these variables tells me what I want to know: The highest number of ...
3
votes
1answer
176 views

Extreme storage beasts

Intro I have derived this question from my previous question Single Drive vs Multiple Drives After getting a very detailed answer from a very helpful member RolandoMySQLDBA and learning the fact that ...
7
votes
2answers
2k views

Single Drive vs Multiple Drives

Generally bottleneck of RDBMS (I am a MySQL user) performance is disk access. SSD provides great performance compared with conventional spindle drives. Question : Is it possible to improve ...
3
votes
3answers
2k views

Converting CHAR to BIT

I have a column with char(16) data type and it was mistakenly set to char(16) actually it should be bit(1) because it contains only three distinct values as follows: 16 characters long string 10 ...
5
votes
2answers
2k views

Trying to get multiple mysql instances running on Ubuntu dev box

I'm trying to get multiple instances of mysql running on a dev box for some testing. This guy is running ubuntu so the setup's a little different than our production setups. I'd like to find the ...
0
votes
1answer
130 views

How should I model this data?

I have a need to model the following requirement for a web application (PHP/MySQL5): A user can compete in a distance-based challenge which consists of entering progress along a defined round-the-...
17
votes
3answers
17k views

Best of MyISAM and InnoDB

Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
1
vote
1answer
513 views

Is it possible to fetch or update table data by table field type?

Is it possible to fetch or update table data by field type? If possible please tell me with a sample code.
1
vote
2answers
322 views

Table partitioning

Recently I have been looking to optimize my database and its size is 7GB. I have an option to partition big table. But partitioning would just divide the big table into small ones while remaining on ...
4
votes
2answers
3k views

Database design for dynamic Meta fields of an entity that shall be queried intensively

I'm designing a database structure for a system at which an entity can have a dynamic set of user-defined meta attributes. The meta fields (e.g.: popularity, conversion etc.) and skill values (int, ...
1
vote
2answers
1k views

MySQL: How does AES_ENCRYPT turn the password into a key?

I am trying to figure out how to create the best secure password for AES_ENCRYPT. Specifically, how long should the password be, and how it translates into the 128 bit key length that AES typically ...
3
votes
3answers
771 views

Dynamically changeable database

We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear ...
38
votes
2answers
59k views

Multi cores and MySQL Performance

Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
4
votes
2answers
5k views

How to convert mysql database charset to utf-8

I have a vb forum with a huge amount of data I need a way to convert this forum database tables charset with thier data from latin1_swedish_ci to utf8-general-ci How to do this ?
13
votes
1answer
11k views

Replicate tables to different database name

Our QA environment has all it's database names suffixed with "test". For example dbname1 in production would have a counterpart dbname1test in QA. (This is largely to help protect against prod/qa ...
4
votes
2answers
375 views

What is the consensus on using NOT NULL constraints on all text-based fields in a table?

Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying. I'm curious what the feelings are with using something like NOT NULL ...
2
votes
1answer
145 views

MySQL Index Questions

I have 2 MyISAM tables: table: search_hit with primary key: search_hit_id, and foreign key: search_id (150 million rows and growing with constant inserts) table: search with primary key: search_id (...
8
votes
3answers
7k views

Deleting all duplicates

I am trying to delete all the duplicates but keeping single record only (shorter id). Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones. ...
3
votes
4answers
9k views

How do you encrypt data while performing a mysqldump?

My OS is windows 7. How can I encrypt data while performing a mysqldump? From the command prompt, can I take a mysqldump with encrypted varchar and texts values? Please help me. Thanks
4
votes
1answer
229 views

Strictly only RAM matters?

I have to work on a project which would have a extra large table with billions of records but only two fields one is id and another is text. And id would be set to BIGINT and PK then if 8+2 (extra 2 ...
7
votes
4answers
7k views

Benefits of table level locking

What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table. Edit Its ...
0
votes
1answer
990 views

How to avoid table crash?

Couple of days back I had a table with 7GB of data and it was showing 63MB of overhead in phpMyAdmin and I mistakenly started its optimization and after working of more than 40 mins it corrupted the ...
4
votes
3answers
4k views

When do hash indexes become reasonable?

MySQL natively doesn't support hash indexes. So, making a pseudo hash column and creating an index on a hash column needs some thinking. And it seems to be widely understood that if the text field is ...
1
vote
1answer
740 views

Simple select statement not returning correct data

I am trying to return data, where I want the item name (items.name), vendor name (vendors.name), url of the price (prices.url), and the price (prices.price) from 3 different tables (items,vendors and ...
3
votes
1answer
17k views

Equivalent functions in MySQL that exist in SQL Server

In an EXIT HANDLER block, what are the MySQL equivalents of OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) ERROR_MESSAGE() Edit What I want to do. In SQL Server I'd have CREATE PROC .....
1
vote
2answers
2k views

What is com_set_option?

As the title says, I'm wondering what is this command and why am I seeing a large amount of this query on my database? It's currently 13.30% of my total queries. I've tried googling this but there's ...
1
vote
2answers
321 views

Adding a new slave slowing down previous replicas?

I have a master I was going to start slaving to a new server to do some tests on. This master is currently being slaved to 3 other boxes. After bringing the new slave online it became clear it ...
1
vote
3answers
2k views

MySQL: Trying to update to 5.5

I've followed this instructions, but after mysqld --skip-grant-tables --user=mysql, I'm getting this: 110913 20:29:18 [Warning] Can't create test file /var/lib/mysql/my-laptop.lower-test 110913 20:29:...
4
votes
1answer
2k views

Determining max database and table size supported and present size

One of my MySQL databases has been running on a single PC from the last four years. Here is my setup: MySQL 5.5.14 Windows XP on NTFS partition All of my tables are using InnoDB engine Here are two ...
6
votes
3answers
3k views

Is there a need to keep refreshing the indexes from time-to-time?

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL. I have created a fresh database and the tables, at present, have no records. I have created ...
3
votes
1answer
2k views

How do you gain access to embedded MySQL database via localhost command line?

We run an application (Windows 2003 server) and would like to have database connectivity -- contacted the company and they do not have any documentation on how to do so. A quick glance at the ...
135
votes
5answers
197k views

Possible to make MySQL use more than one core?

I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help Setup The servers are quite hefty with an OLAP/...
8
votes
2answers
14k views

MySQL Auto_increment going 2 by 2

I installed MySQL Workbench the other day, accessed my company's database and made myself a table to work with. So far so good. The problem is, I noticed my auto_increment is incrementing 2 by 2. For ...
7
votes
2answers
23k views

Internal reason for killing process taking up long time in mysql

I copied a big table's structure with (it is an InnoDB table btw) CREATE TABLE tempTbl LIKE realTbl Then I changed an index, and filled it up so I could run some test. Filling it was done using: ...
2
votes
2answers
441 views

Database Transfer from PostgreSQL to MySQL

Is there any method to tranfer the data and schema from a postgresql database to mysql?
9
votes
2answers
7k views

Is this a normal set of MySQL privileges?

Using phpmyadmin on a local test wamp setup, and well as pretty much the title states, there are 3 users marked as user = ANY, password = NO, such as: USER | HOST | Password | Global Priv | Grant -...
3
votes
1answer
877 views

InnoDB has a transaction running, but “SHOW PROCESSLIST;” shows nothing

I have a replicated database setup, mostly Innodb databases/tables. The query that I believe caused the issue ran in about 20 minutes on the master database. This is where it gets weird. Show ...
10
votes
2answers
6k views

Does a view need its own foreign key constraints?

Disclaimer: I'm a programmer, not a DBA, so bear with me... I have a view that I use to just map 2 entities together. I have to do a join between a few different tables to get that: CREATE OR ...
6
votes
4answers
4k views

Does putting single quotation marks around numeric constants really protect from SQL injection in MySQL?

The manual seems to suggest that using quotes around numbers is sufficient to protect from SQL injection. According to section 5.3.1. General Security Guidelines of the MySQL 5.1 Reference Manual: ...
11
votes
1answer
10k views

Rebooting a MySQL Replication Slave

For the first time since it was set up I need to reboot a read-only MySQL replication slave. I found this article about downing a slave for maintenance (albeit he's just describing stopping the mysql ...
0
votes
1answer
169 views

Add parent_id dynamically based on another field

I have a version table using a field to track order: id, owner_id, order, version 5, 1, 1, 1.1 6, 1, 4, 1.2 7, 1, 7, 1.2.1 8, 1, 10, 1.3 I want to ALTER TABLE version ADD COLUMN parent_id and have ...
7
votes
5answers
16k views

How to find dependencies on a table in mysql 5.0?

I am trying to drop a table in MySQL but its complaining about "Cannot delete or update a parent row: a foreign key constraint fails"... Questions Is there a way to find out all the table ...
5
votes
2answers
10k views

MySQL, foreign key, can't create table error 150

UPDATE: Sorry, it was because i forgot to add UNSIGNED attribute to the lang_id column. Original: I'm trying to create a table with foreign key in MySQL. I get this error: #1005 - Can't create table ...
1
vote
1answer
681 views

MySQL CPU Usage

To remove duplicate rows in a table I did the following (about 200k rows) table1: id_a int id_b int val_a int val_b int pk (id_a, id_b), index id_a, index id_b Algorithm: created table2 as select ...
3
votes
4answers
6k views

MySQL : Conditional ORDER BY to only one column

Hello and thanks for taking time to read this question. I am using MySQL, and I want to sort results using ORDER BY to one specific column, but the results must be ordered according an specific ...
2
votes
2answers
4k views

Corrupt InnoDB tables after every mysql restart. What's wrong?

Recently I changed a few settings in my mysql configuration. I did the following: - stop mysql - save the changed settings - delete all ibdata and ib_logfiles - start mysql Normally this works fine ...
2
votes
2answers
2k views

Insert Into MySQL over linked server when a medium text is the last column breaks Int fields

I have a table with ID INT(16) StateName VARCHAR(50) Details MEDIUMTEXT When I do an insert over the linked server connection: Insert Into MySQL...tblState (ID, StateName, Details) Values (1,'...

1
360 361
362
363 364
369