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
57 views

Migrating database with views trough phpMyAdmin causes error in second host

I'm trying to migrate a database which contains some views and I'm doing trough phpMyAdmin export feature. MySQL version on servers are: Server to migrate: Server version: 5.5.34-0ubuntu0.12.04.1 ...
1
vote
2answers
2k views

Foreign key constraint based on conditional column

I'm trying to figure out the best way to handle this situation: I have a table set up as follows: LEARNING CONTEXT ================ LearningContextID - INT - Surrogate Primary Key EntityID -...
2
votes
3answers
209 views

How to include Innodb_trx_id in MySQL slow log

Evaluating MySQL's slow query log with pt-query-digest from the Percona Toolkit, I need to group queries by transaction. As it seems, the easiest way to do this is to group by the Innodb_trx_id ...
2
votes
2answers
4k views

mysql: using comma-separated values

I have a simple mysql table which I use as a server booking system. A user can log in to a server, if he has a valid booking for that server. A sample data might look like this: +----------+----------...
2
votes
1answer
871 views

Partitioning MySQL vs PostgreSQL

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which ...
1
vote
3answers
10k views

How to get a list/result of best selling items

I'm trying to get the results of my best selling products using SQL. My table is order_items which has the following columns. id|product_id|qty As there is a QTY field I have to somehow count or ...
0
votes
2answers
902 views

Trigger to delete inserted record if it doesn't exist in another table

I have a question. I have two tables: zip_code (zip_id, zip_code, state) - this is a static table that stores areas I service tech_zips (tz_id, tech_id, zip) - this is where I store what zip codes ...
0
votes
1answer
524 views

How can I speed up a DateTime comparison query in MySQL?

I am performing the following query on a MySQL table with around a million records ( ~350MB ). It takes roughly 1.5 seconds every time. If I remove the OR last_updated > last_geocoded comparison, ...
0
votes
0answers
92 views

MySQL showing non-linear behavior for a simple insert

I've come across a symptom in MySQL which got me baffled. First, the time it takes to INSERT a certain amount of records changes non-linearily with the amount of records. Second, the time also depends ...
0
votes
2answers
7k views

How to force higher values of open_files_limit or table_open_cache?

I'm having a frustrating problem where setting my.cnf to values higher than I currently have them does not 'take'. This is MySQL 5.6.14 on MacOS X 10.8.3 I'm trying to address dropped connections ...
1
vote
1answer
12k views

Selecting multiple columns from multiple rows in one column (subquery?)

I'm working with MySQL 5.1.68. I have a situation where I'd like to select rows from another table into a column. I'd like to output this: id # name # citizen_name (multiple citizen names) 1 # ...
0
votes
1answer
350 views

MongoDB Storage Architecture (MySQL innodb_file_per_table equivalent to MongoDB)

I am new to MongoDB.I looked inside dbpath I found users.0 - 16.8MB users.1 - 33.6MB users.ns - 16.8MB Further when I dig deeper I found, The .0, .1 files are datafiles. Each datafile is ...
1
vote
0answers
194 views

problem installing mytop , need a up to date tools for monitoring mysql

so i'm trying to installl mytop to monitor the database i'm following this instruction : http://www.sohailriaz.com/how-to-install-mytop-a-top-clone-for-mysql/ when i try to install it with yum i get ...
0
votes
1answer
9k views

High average load due to the High mysql cpu usage

i've got 2 warnings about my average load being high in the past week and apparently it has to do something with the mysql my daily visit has been increased about 10-20 thousand (total of 180-150 ...
0
votes
1answer
1k views

migration from mysql 4.1.0 to mysql5.6.2

I have been trying to backup data with structure from my old legacy server to my local server for modifications. PhpMyAdmin timed out at the time of export, MySQL Workbench has performed connections ...
1
vote
1answer
2k views

Table Design - User Management

I have designed a database design for a simple User Management System, this system consist of the following things, User - A user may be a Vendor / Employee / Buyer. User - A user may have more than ...
0
votes
1answer
362 views

Type of Foreign Keys for a Dimensional Model Fact Table?

I've been reading about dimensional models, and in particular fact tables, but I haven't really seen one in action (i.e. the actual content), just diagrams of the table structures and column names ...
0
votes
1answer
883 views

MySQL: all stored procedures deleted?

Started seeing some strange errors from dependent processes this morning. After some digging it appears that the file /mysql/proc.MYD is set to zero length. The file /mysql/db.MYD appears to be ...
0
votes
1answer
1k views

Calculating time spent on query cache lock

How can I calculate the total time my MySQL database is spending Waiting for query cache lock ? I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the ...
0
votes
0answers
516 views

Stored procedure to replace a string in an entire database

I am trying to create a procedure to change a string in an entire database... after a lot of searching I have come up with this procedure, but it gives an error. The procedure is: BEGIN declare t, c ...
1
vote
1answer
1k views

How does locking work in INSERT..ON DUPLICATE KEY UPDATE statements?

I am encountering deadlocks while trying to update multiple rows (a batch) using INSERT..ON DUPLICATE KEY UPDATE. Does MySQL lock all the rows in one batch or does it lock only the row it is updating ...
2
votes
1answer
93 views

Table Relationship cardinality

I am designing a database for my application, I got issue in implementing the following logic. A user may be a Vendor and also a Buyer. I have created a table for USER and created an another table ...
0
votes
1answer
975 views

mysql crashed, after start again, the new data lost, only old records of some day ago

I got a server restart, the mysql server seems crashed. After reboot, mysql database filled of old data for bug tracker. Do you know how to fix this? mysql error log as below 131027 15:29:19 [Note]...
2
votes
1answer
8k views

Not able to login on MySQL in AWS RDS using root

I am trying to connect my MySQL RDS from EC2. Under security groups I have made sure permissions are in place. $ mysql -h xxx.xyz.ap-southeast-1.rds.amazonaws.com -P 3306 -p Enter password: I am ...
3
votes
1answer
1k views

Mysql slow select from a view

I would appreciate an approach with this problem, I have a lottery system in mysql, this system receives a lot of transaction per second.. a lot!. I need to check the total amount for each the number ...
0
votes
2answers
161 views

MySQL: Does the “length” setting affect table size in bytes?

Regarding setting the length of allowable characters for a field (11) in the example: Does this number affect the size of the table? For example, I want to create a char type column and I only ...
2
votes
2answers
3k views

Find nearest values from MySQL Table

I want to fetch nearest rates for a given value from below table. mysql> select * from rates; +------+----------+----------+ | ID | Type | Rate | +------+---------------------+ | 1 | ...
1
vote
1answer
656 views

Using coalesce on an index column

Does doing a COALESCE on an indexed columns in a WHERE clause e.g. WHERE COALESCE(table1.indexed_column, '9999-01-01') > table2.other_date_column affect indexing in a bad way? I think that it ...
0
votes
0answers
219 views

mysqldump & upload to mysql speed impact due to SSD and innoDb?

I am testing an AWS Medium Instance with CentOS (AWS AMI) and comparing it with Hetzner's EX40-SSD (http://www.hetzner.de/hosting/produkte_rootserver/ex40ssd). Software setup on both is comparable( ...
0
votes
1answer
109 views

MySQL High I/O. Wrong configuration?

Since I switched from MyISAM to InnoDB I have a high I/O. The writes is a flat line, it seems there is a limit for them. This is my current mysql configuration: [mysqld] bind-address = 127.0.0.1 ...
0
votes
1answer
138 views

How write mysql query corresponding this tables

This is my database tables I want to get this result: How can I write select query to get that result?
0
votes
3answers
10k views

How much RAM is required for 5000 concurrent users MySQL

Going by this : When should I think about upgrading our RDS MySQL instance based on memory usage? for max_connections to be set at 5000 concurrent users,how much should be the RAM capacity be ? If ...
3
votes
2answers
2k views

MySQL - Add index to low cardinality text column?

I'm working on project where an enum column is being converted into a text column (I cannot change this). The cardinality of the column is low (7 unique values). Would I gain a performance increase by ...
2
votes
1answer
475 views

How to repair all indexes in a MySQL database?

My database got corrupted, and now the php app is throwing this error: Invalid query: Can't find FULLTEXT index matching the column list The error is not very useful, since it doesn't say what ...
1
vote
2answers
4k views

Can I time out my scripted “FLUSH TABLES WITH READ LOCK;”?

I have a single Enterprise Linux 6 system running MySQL 5.5. I am attempting to use LVM to capture a weekly filesystem snapshot. However, as many of us have experienced, "FLUSH TABLES WITH READ LOCK;"...
0
votes
0answers
157 views

Populate the columns of one table with the records of another

I have one table, FRUIT, describing some measurements that I make: ID Tag Amount Units 1 A 75 Apples 2 B 23 Bananas I would like another table, HISTORY, that tracks the ...
0
votes
1answer
42 views

How to create new column with value from other table

I have a table called 'menus': menu_id | sort | page_id | parent_page_id ---------|------|---------|---------------- 1 | 0 | 1 | NULL 1 | 1 | 2 | 1 1 | 2 | ...
0
votes
1answer
80 views

Optimize query with similar fields

I have the below schema, fields owner_id,billing_id,tech_id,admin_id on carts table are pointing to contacts table. As you can see from the query result I have to JOIN for each field .Is there a way ...
0
votes
1answer
2k views

MYSQL How do I query to show data in separate columns from one field?

sorry if my question is worded poorly, my SQL vocabulary is very poor. So basically, suppose I had a table as so: CREATE TABLE MYTABLE { item_id int AUTO_INCREMENT PRIMARY KEY, item_name ...
4
votes
2answers
4k views

Does using LIMIT improve the performance and is it noticeable?

I want to understand the following. Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by. Letting aside any optimizations to the query itself e.g....
12
votes
2answers
2k views

Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
0
votes
2answers
131 views

Trigger calls and application code

When a trigger is set on a table does the application code wait for the original statement and the trigger to execute before resuming or just the original statement? Consider application code to be ...
0
votes
1answer
2k views

Optimize MySQL Self JOIN Query

I have a query which is running in 15+ seconds SELECT t1.`ST_StockCode`, t2.`SM_StockCode`, t2.`ST_ItemSize` FROM `stocks` AS t1, `stocks` AS t2 WHERE t1.`...
1
vote
1answer
185 views

re-enabling a table for mysql replication

We were able to setup mysql master-slave replication with the following version on both master/slave: mysqld Ver 5.5.28-29.1-log for Linux on x86_64 (Percona Server (GPL), Release 29.1) One day, ...
1
vote
1answer
9k views

mysql trigger to insert/update date column from datetime column

probably simple question but can't get it to work: I have a table with two datetime columns. In order to set up a date index and have simpler queries, i have added two date columns next to them. Now,...
0
votes
0answers
285 views

In mySQL possible to change auto-increment ids that have huge gaps and if so how?

I am importing a 50 table relational database (I'm not a dba) with 250,000+ records in most of the tables, yet the auto increment is off in most tables, some for example showing the last ID at 5 ...
0
votes
1answer
304 views

How to make slave is in sync with master in tungsten replication?

we are using Tungsten master to slave replication in MySQL. we have configured this setup very long back. from last week onwards slave is in lagging with master around 5 to 10 binary files. we have ...
1
vote
0answers
117 views

can this mysql query be optimized further?

I have this query select counterparty_id, max(id) max_id from client_administrations where counterparty_type = 1 and effective_on <= '2013-10-22' group by counterparty_id When I ...
0
votes
1answer
58 views

Build a simple query and relationship table

This is my first post here. Thanks for having me! I have two questions for a table I'm building into my mysql database. Question 1: I have a need to input an artist name and the result/select be a ...
2
votes
3answers
9k views

Database design to track student attendance

I was having a doubt regarding the design of a database table to track students' attendance. Currently, my table students have atleast 4000 students. Creating a database for attendance to track their ...