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
2answers
505 views

how to design a database's table to store mysql spatial data in a parent child relation?

I want to store locations and polygon data in a parent-child relationship. I am using spatial data in MySQL and Google maps for polygon. Examples: 1. World 1.1 US 1.2 China 1.3 Japan 1.4 South ...
0
votes
0answers
47 views

Conditional value lists in MySQL

apologies in advance if the language I use here is insufficiently specific. Please let me know if there's anything that I can clarify. Does MySQL provide a means of creating conditional value lists a ...
1
vote
1answer
100 views

MySQL: Find ID with only 2 attached IDs in subtable

I've got 2 tables. 1 containing my Threads and 1 containing the users attached to these threads. I want to find the ThreadID with 2 specific UserIDs attached, no more no less. This is what I've come ...
0
votes
2answers
228 views

Alter auto-increment of table with 16 million rows

Learned this the hard way. Need to alter a table's auto-increment with 16 million rows. Now, it's taking quite sometime. Any estimate how long it would take? Sorry.
0
votes
1answer
74 views

mysql hardware refresh + version upgrade

currently I am running MySQL 5.0.80 on windows server 2003 (x64), 64gb memory, I have a master and 2 slaves. It's a large 400 gb Database. I am setting up all new hardware (SAN + dedicated MySQL ...
0
votes
1answer
40 views

Does column size increase break SELECT *?

I searched a little bit on this topic before realizing it is somewhat tricky (for me, at least) to come up with an effective search phrase for it because it overlaps with other common topics. So here ...
2
votes
1answer
132 views

Wordpress blog where pages are almost-always served from cache: SSD or more RAM?

I am working on the design of a Wordpress blog which when finished will almost-always serve pages from disk cache and this is because all posts will be published in batch and then cached, with at ...
1
vote
1answer
675 views

Fetch data from same table using two group by clauses in mysql

I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month. In my table, user_type is ...
3
votes
2answers
154 views

How to optimize this slow SQL query

I have a slow SQL query on my Drupal website and I don't know how to optimize it. Here is the query that Views produces: http://pastebin.com/StAEiXbY SQL fiddle: http://sqlfiddle.com/#!2/20081/1/0 ...
1
vote
1answer
48 views

How to sort words in a column in mysql?

I have data as id action 1 a 1 b 2 b 2 a 3 c I wrote code as select id,group_concat(action SEPARATOR ' ') from table1; It ends up as id action 1 a b 2 b a 3 c Actually, ba and ab ...
0
votes
2answers
2k views

Restoring data from dumped sql file gives: mysql: unknown option '--innodb_file_per_table

I was trying to separate DB indexes from ibdata1 I took the steps in what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql I dumped data, dropped databases, and started mysql service. I got the ...
0
votes
0answers
288 views

Show single value of one column for multiple rows of other columns

I have a SQL query which displays my data results like this. date emp_id emp_name spent_hours 19-08-2013 1024 jack 10 19-08-2013 1024 jack 11 19-08-2013 1024 jack ...
1
vote
1answer
70 views

Should I add new column or new table

I have an existing table called exampletable1, now i have been given a new list of data called "medicines" it only have: male,female as its value. I'm having a problem whether to add another varchar ...
0
votes
0answers
20 views

MySQL - How many slaves can be handled by a master? [duplicate]

Can someone please put some highlights on this, How many slaves can be handled by Master. I have 100+slaves for a single master, how this process will work?
0
votes
0answers
74 views

hot/live sql backup

please excuse my ignorant question as I am still fairly new to database things so I have a mysql database and a part of it has file with average size of 10M I store them in their own directory and ...
0
votes
0answers
403 views

Copying a table with its structure, indexes, and associated triggers?

I have studied this post on Stack Overflow: Fastest way to copy a table in MySQL. But no answer is copying everything like the table's structure, data, indexes as well as associated triggers. Is it ...
0
votes
0answers
162 views

How do triggers work on partitioned tables?

I have partitioned a table on the basis of a column. This column can either have 0 or 1. So, only two partitions (p0, p1) have been created internally. On this table there are 3 triggers which execute ...
0
votes
0answers
158 views

Auto increment holes in primary key - any issues?

I have an application that is creating large holes in the auto incremented primary surrogate key to the table as it uses lots of INSERT ... ON DUPLICATE KEY UPDATE queries. I'm looking into changing ...
0
votes
1answer
164 views

Need to delete consecutive, unchanged records

I have a rather large MySQL db that is tracking pricing changes for a large number of products. Each entry has a SKU a datetime and a price. Sometimes, there is no change in the price from one time ...
-3
votes
1answer
219 views

MYSQL Combine two results into one

I have two mysql queries first query having results bookmark_id, is_fav, title and second query have results like this bookmark_id, is_deleted Sometimes both have same results or may have ...
1
vote
1answer
129 views

CASE conditions in SELECT statement [closed]

I have the following SELECT statement: SELECT expt_id, iso_stain, test_stain, stain_type CASE WHEN stain_type = 'Blue' THEN iso_stain = 'NA' CASE WHEN statin_type = 'Hematoxylin' THEN ...
0
votes
1answer
131 views

Set MySQL option to restore MySQL server after deleting ibdata1

ON an Ubuntu Server 12.10 machine, I had attempted to delete the ibdata1 file before reading Rolando's explanation about how exactly to do so. Specifically, I had not set innodb_fast_shutdown = 0 ...
1
vote
1answer
89 views

How Much Resources Does the BLACKHOLE Storage Engine Consume?

I'm going to add a MySQL Relay Slave which use BLACKHOLE Storage Engine. The relay slave will just filter some databases binlog to downstream slave. Our MySQL Master generates about 15G a day in ...
5
votes
1answer
733 views

Is it Possible to Recover Data from a BLACKHOLE table? [closed]

I had created table with engine BLACKHOLE basically the BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty ...
7
votes
2answers
14k views

MySQL performance problem using indexed datetime column

I tried to solve the following problem for about one hour now and still didn't get any further with it. Okay, I have a table (MyISAM): +---------+-------------+------+-----+-------------------+-----...
1
vote
0answers
329 views

One table for article comments, video comments and photo comments or multiple tables?

First DBA question. Greetings people. I hope I'm not duplicating a question but it's very hard finding database design related questions here (for multiple comment tables), as all 'design' questions ...
0
votes
0answers
232 views

Load data from file only for specific column based on other column value

I am familiar with basic usage of 'load data local infile' and importing specific columns only. What I want is however to import a single column from file based/related on data from another column. So ...
0
votes
1answer
57 views

Query a database for empty tables

In my system there are two tables called ‘faculty’ and ‘faculty_assets’. I need to delete faculty by faculty from the ‘faculty’ table. But system should allow deleting the faculty when only ...
1
vote
1answer
72 views

Count/Statistic for number of SHOW INDEX queries in MySQL/MariaDB 5.5 [closed]

We have a lot of SHOW INDEX FROM queries coming from our ORM layer. Usually there is a mechanism to prevent this but it doesn't seem to work at the moment. To better analyze the problem it would be ...
1
vote
1answer
812 views

Cartesian product of two tables not working as expected in MySQL but fine with SQlitee?

I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple table 1 (5 entries): (emp->{id,name,dep}) table ...
2
votes
1answer
299 views

Is there a difference between RANGE and LIST method for partitioning by consecutive integer values?

I have a table with a year column that has consecutive values -- 2000, 2001, and so on. It's a read-heavy MyISAM table, with 25+ million rows for every value of year and a comparable set inserted in ...
1
vote
1answer
75 views

How to put this particular constraint on this link table in MySQL? [duplicate]

I have the following three tables in MySQL: Store, Groups, StoreGroup. A store can belong to several groups. StoreGroup is the link table that records the groups a store belongs to. The Groups table ...
2
votes
1answer
2k views

MySQL consistent nonlocking reads vs. INSERT … SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...
3
votes
2answers
1k views

MySQL memory usage issues?

I have a MySQL instance running on my Ubuntu server that is eating up most of the memory on the machine. I currently have 2 GB of ram installed on the machine, and MySQL is using 1983 MB. Most of my ...
1
vote
1answer
207 views

MySQL Replication Lag Almost 23 Hours

I came into a new environment where the production DB was running on MySQL 4. Replication is usually about an hour behind but in the last few days it has been well over 24 hours behind. I'm not ...
0
votes
0answers
311 views

Finding the right MariaDB configuration

I’ve read several articles about how to configure MySQL/MariaDB, but now I’m kind of confused how to find the right values. Some articles suggest not choosing to high values, others recommend adapting ...
0
votes
1answer
86 views

MySQL grant privileges has no effect

I've executed the following queries: GRANT USAGE ON my_db.* TO my_user@127.0.0.1 IDENTIFIED BY 'pass'; GRANT ALL ON my_db.* TO my_user@127.0.0.1; GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO ...
-2
votes
1answer
55 views

using ubuntu 14.04 when i use this command getting the error

just installed mysql server using sudo apt-get install mysql server when i try to display the database in terminal getting this type of error SHOW DATABASE; ERROR 1064 (42000): You have an error in ...
0
votes
1answer
97 views

Repair table field: Import/append records from specific backup field if they are not null

One of my 3rd party applications that writes to my production MySQL database mydb has nulled SOME of the data in one of my fields myfield in table mytable. I'm trying to import only the NON null ...
0
votes
1answer
2k views

group by clause without aggregate function

I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and ...
2
votes
1answer
715 views

Why/when does creating a mysql trigger require SUPER privileges?

I'm attempting to parse and understand what the MySQL doc says about when SUPER privileges are required for creating triggers. They say: If binary logging is enabled, CREATE TRIGGER might require ...
1
vote
2answers
450 views

Creating a database with only MySQL command line?

The linux server on which I'm attempting to create a MySQL database has many difficult to work with permissions. My supervisor has tried to install MySQL Workbench on the server, but has been unable ...
2
votes
3answers
476 views

Will MySQL be effective when selecting a row from a table with billions of records? [duplicate]

Note: this is a hypothetical scenario Lets say that: I'm running MySQL, InnoDB engine, UTF-8 encoding I have a table with 50+ billions of rows The tables primary key is a BIGINT The table also has ...
3
votes
2answers
1k views

UPDATE query with JOIN taking forever

I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would ...
5
votes
1answer
14k views

mysql logging activity from specific user or ip

I have mysql server. The server is accessed by my application, and by external auditor (person using mysql workbench). The auditor has specific user and password and dedicated IP and it is granted ...
2
votes
1answer
65 views

Change column without interrupting service

We need to change the data type of a column in a large table (~1.7 GiB) from VARCHAR(1024) to VARCHAR(512) in order to be able to create a secondary index over two columns. We're using MySQL 5.6 with ...
2
votes
1answer
52 views

Is it possible to import a single mysql database directory?

I am somewhat new to mysql. I created some databases recently and, to back them up, I just copied the folders with those database names in the mysql data directory (i.e containing only db.opt, .frm, ...
0
votes
1answer
43 views

MySQL Insert with Select

Trying to insert data to a table with values from different tables. INSERT INTO class_teachers_section(class_ref, teachers_ref, section_ref) select (select class.id as class_ref from class ...
2
votes
2answers
410 views

Should I drop a foreign key if I use a soft delete?

"ON UPDATE" scenario never happens in my application. As soft delete is used, "ON DELETE" cascade and restrict scenario never happen as well now. Then, should I just drop foreign key because I have ...
3
votes
1answer
836 views

A question on MySQL SELECT Algorithm/Function

Let's say I have 100,000 rows in a table, and each of the rows has a boolean value. Only ten of these rows have a value of 1, the rest are 0. How would MySQL perform a SELECT on only the rows with a ...