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

1
vote
3answers
2k views

Low Cardinality of Index performing better than High Cardinality?

For testing purposes I have 2 Tables TableA and TableB in an mysql-InnoDB, both with the same columns id bigint not null auto_increment, depot smallint not null, custno bigint not null, range_from ...
0
votes
1answer
2k views

How to set up simple database replication using WAMP server?

Just need a little help here about replication. I have an intermediate knowledge in Mysql. And I can't find any simple solutions on web how to establish a database replication in Mysql. All I need to ...
2
votes
1answer
3k views

Large INSERT INTO SELECT [..] FROM gradually gets slower

I've written a program that does a large INSERT in batches of 100,000 and shows its progress. The source table contains 2.5GB of data: CREATE TABLE wikt.text ( old_id INT(10) UNSIGNED NOT NULL ...
0
votes
1answer
357 views

Making the tables with db generalization [closed]

Can anyone tell me what will be the best way to make the tables for the following diagram: I was thinking about this: videos ------ - id - title music_videos ------------ - id - video_id ...
1
vote
2answers
151 views

Duplicates while doing multiple joins

I have 4 MySQL tables 1. companies - {id*, company} 2. employees - {id*, fname, lname, id_company*} 3. technologies - {id*, technology} 4. company_technologies - {id_technology*, id_company*} Field ...
1
vote
1answer
309 views

Precautions when designing database schema if tables are going to have billons of rows

I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have ...
0
votes
0answers
84 views

MySQL Multiple threads inserting *SAME* record a Bad Thing?

Okay, as I've noted in other questions, I'm not a sophisticated database user even though I took my first course in relational algebra from ted Codd. I've now inherited an application that ...
1
vote
1answer
1k views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
1
vote
1answer
338 views

what is the structure of a Mysql multi-column index? [closed]

For example: CREATE TABLE test( col_1 INT, col_2 INT, col_3 VARCHAR(11), col_4 VARCHAR(11) )ENGINE = INNODB; CREATE INDEX mul ON test(col_2,col_3,col_4); Sorry,my English is not good,I mean ...
0
votes
0answers
574 views

Random MySQL Cannot Connect to Master Errors

We installed MySQL 5.6.14 Community Edition on a Windows Server 2012 VM about a month ago. We have a web VM on the same box that utilizes the database using the latest MySQL Connectors for .Net and ...
0
votes
1answer
267 views

Mysql list data from left join query

I have 2 table and i want to pull data from both of them. This is sample for my table structure: Table tbl_a = id, time, note, customer Table tbl_b = id, time, name, surname, note This is my mysql ...
1
vote
1answer
617 views

Stored procedure and prepared statements

I have 2 stored procedures: CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500)) BEGIN IF (param_CRITERIA <> "") THEN SET @QUERY1 = concat('SELECT user_name,count(*) as count ...
1
vote
1answer
1k views

Duplicate entry in mysql slave

I have a mysql master-slave replication setup. when I checked "show slave status", I got the result as "Slave_Sql_running:No" and Last error as "Duplicate entry 'x' on query." But when I tried to ...
1
vote
1answer
306 views

Okcupid.com (can find matches without accessing a database!?) [closed]

I'm doing a research project on a popular dating site called: OkCupid I would like to talk about how the database is used. After reading this explanation from the Co-Founder of the site, I became very ...
1
vote
1answer
249 views

Why are the passwords in MySQL (5.x) not salted?

I'm working with a legacy application that is connected to a MySQL database (5.x). When I created two different users for the MySQL database with the same password, the hash of those passwords are the ...
2
votes
1answer
260 views

Can MySQL use an index to calculate SUM?

The following query is run in a MySQL database (all InnoDB tables, Percona 5.5), and is slow for some parameter values (i.e. for some large customers). It returns the total size of all files uploaded ...
3
votes
2answers
2k views

moving to AWS RDS (InnoDB vs MyISAM

I am in the process of moving my servers from stand alone hosting to AWS using RDS (MySQL) for database usage. One of the first things RDS states is that in order to use their backup, and snapshot ...
0
votes
0answers
88 views

Select with IN list and date comparison not picking up index

explain select sum(principal_received) principal_received, sum(interest_received) interest_received, sum(advance_received) advance_received, ...
0
votes
1answer
449 views

Backing up MySQL w/MyISAM on Amazon RDS

So, for various good reasons, we're maintaining a mySQL database using a MyISAM engine on Amazon RDS. I've just taken over this application, and I'm concerned we're not getting good backups, since ...
1
vote
1answer
1k views

Is it better to put filters on JOIN clauses instead of WHERE clauses

MySQL 5.5.8: Question 1: Is there any difference in the following statements and if so, which is preferred performance wise? Can this even be answered without knowing the row counts of the tables ...
0
votes
0answers
820 views

MySQL query conditional return using TRIM()

I am trying to SELECT from a table, but I need to trim from leading and trailing string for comparison. For example, My table/data is very simple CREATE TABLE `lcr` ( `id` int(11) NOT NULL ...
0
votes
1answer
458 views

MySQL Master Slave Replication and backup

I would like to implement the following backup solution as highlgihted on the mysql site: To use replication as a backup solution, replicate data from the master to a slave, and then back up the ...
0
votes
0answers
473 views

zero rows fetched, selected, or processed Mysql

Help me to solve this problem error call cd3(@VAR) 0 row(s) affected, 1 warning(s): 1329 No data - zero rows fetched, selected, or processed 0.000 sec delimiter ;; create procedure cd3(out var ...
0
votes
0answers
120 views

MySQL server crashed. [closed]

Help! I managed to crash MySQL last night. I am on a Mac using the native version that came with Mountain Lion. I was upgrading from 5.5 to 5.6. I have followed instructions in this forum to delete ...
0
votes
2answers
2k views

reset mysql root password on ubuntu

In short: my goal is to reset mysql root password on ubuntu. Background: I set up a ubuntu desktop and installed LAMP stack last week. I then went to do something else, and just got back to carry on ...
0
votes
3answers
3k views

Creating a global temp table in MySQL

I am working on a MySQL stored procedure. I need to create a temp table and I want to access this temp table whenever I execute this stored procedure. I know we can't access the temp table in ...
1
vote
1answer
103 views

DROP TABLE on a huge InnoDB table

I have an innodb table with over 140 million rows taking 26GB. I would like to drop this table however this is taking way too long, probably days. How can I speed up this query? processor: Intel® ...
0
votes
1answer
1k views

Datetime Difference in MySQL when format is Varchar

I have two columns in a table: STOP and START, of type varchar. I want to have the difference between both columns, and I'm using : SELECT DATEDIFF(stop, start) AS difference from T1; It is ...
1
vote
2answers
4k views

What is the difference between NULL and \N while loading data from a csv using load data local infile

I have a table which has four fields create table test_hc1(cola int,colb int,colc varchar(20),cold int); Now, I am trying to load following data 1,2,\N,4 and the third field in table is coming ...
0
votes
2answers
109 views

Where/how does innodb store tables in files?

I read somewhere that InnoDB can store all the tables in a single file or in separate files. Is this a configuration option? What is the default setting? A file for all tables or a file per table? ...
0
votes
0answers
19 views

Mysql craches ! InnoDB [duplicate]

The full mysql error log, the VPS craches almost every day! I need to add more info but I can't so this is the only info I have : 131117 20:08:47 [Note] Plugin 'FEDERATED' is disabled. 131117 ...
0
votes
1answer
566 views

Update , Select and Join using 3 tables

There are 3 tables: Label (Label_ID,Label_Name), Label_ID is Primary Key. Tracks (Prefix, Label_ID), Prefix is Primary Key,Label_ID is empty column Top100 (Prefix,Label) No keys in this table. My ...
1
vote
1answer
717 views

counter table in mysql

i was going through the book: High performance MySql 2nd edition. In chapter 4 it says: An application that keeps counts in a table can run into concurrency problems when updating the counters. ...
1
vote
1answer
912 views

Mysql update different fileds set depend on condition

I have two tables "a" : CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT, `i1` int(11) DEFAULT NULL, `i2` int(11) DEFAULT NULL, `ib` int(11) DEFAULT NULL, KEY `id` (`id`) ) ...
1
vote
1answer
80 views

When is the index actually created?

I have noticed that even on huge tables with millions of records when I do a CREATE INDEX the index is created immediately (I get the console back in nsecs). Why is this? Doesn't it create the B-Tree ...
0
votes
2answers
187 views

high traffic mysql hosts

I'm wondering if any of you sql buffs out there have any suggestions for a high speed mySQL server. I need something that can handle roughly 33 updates and 33 queries per second, per user. and for ...
0
votes
1answer
174 views

is keeping idle / unused mysql databases bad?

Is there any harm like eating up resources etc. Or is mySQL engineered to just work for the used ones and keep others at hand. If so, why is there such thing like taking a db offline? Should I do ...
0
votes
2answers
11k views

How to set Innodb Buffer Pool Size [duplicate]

I have a database using innodb completely. Its now having a innodb_buffer_pool_size at 128MB. Which I know is very less for my user. But no matter what I do it won't change. I have added a value of ...
4
votes
3answers
11k views

mysql replication successful but slave not replicating

I have created a mysql master-slave configuration and things look fine . the " show master status;" on slave doesnt show any error . this is the output Slave_IO_State: Waiting for master to send ...
2
votes
3answers
426 views

Does MySQL 5.6 support fan-in replication?

I've known for quite awhile that Continuent's Tungsten Replicator supports fan-in replication (example). By fan-in replication I mean combining multiple datasources into a single instance (like a ...
0
votes
0answers
170 views

SQL queries are very slow

I have noticed that my queries are getting slower. Some update queries take about 48-50 seconds. Here's how it looks: [root@db-1 ~]# tail /var/lib/mysql/mysql-slow.log # Time: 131113 17:09:48 # ...
2
votes
1answer
334 views

Implications of a second index on a timestamp field in a sequential table

I have a MySQL table of a few gigabytes with roughly ~100M rows. I store data sequentially, such that the timestamps increase as the ID also increases. Because ordering and filtering by the timestamp ...
0
votes
2answers
2k views

MySQL getting result using 3 Tables and case statements

I have 3 tables named person,person1, person2. All the 3 table has 2 columns name and phno. Now my requirement is, if i give any particular phno it has to show either yes or no for the corresponding ...
0
votes
1answer
29 views

content types of insertion

I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?
0
votes
1answer
804 views

How to copy a table in a production server without WRITE lock and without writing to a file

I'm trying to copy a table in a production table. When I'm performing on InnoDB tables: INSERT INTO new_table (SELECT * FROM table) I have a write lock on table. Is it possible to avoid this lock? ...
1
vote
1answer
254 views

XOR binary values greater than 64bit

I have a binary blob holding the results of a SHA256 hash that I XOR with a script generated SHA256 hash. For testing I am using the values usertokens and methodtoken for the test hashes. hash1: ...
2
votes
1answer
804 views

MySQL InnoDB issue not using indexes correctly [Percona 5.6]

I just installed Percona 5.6 on my new CentOS 6.4 server. It's a fast machine 32 core xenon, 72GB ram, 8x SAS RAID 10 setup. So far so good My old server is a bit less powerful, and was running ...
1
vote
2answers
291 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
1
vote
0answers
57 views

Help with Mysql with 100K+ Table

Hi im trying to develop a database design in which its gonna have 100K+ tables residing in it. My major concern here is will Mysql be able to hold that much amount of tables with no performance ...
0
votes
1answer
759 views

MySQL: Error while reverse engineering database from a sql file

I'm trying to reverse engineer a DB in MySql with a sql file generated from an OpenOffice database. while trying to import and run the sql file, I get the following error: ERROR: Line 1: syntax ...