MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
46 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
33 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
18 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
38 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 ...
0
votes
2answers
38 views

What's the use of assigning foreign keys on MySQL [duplicate]

Aside from indexing the value of the foreign key, what is/are the advantage of assigning foreign keys to databases? I've seen a lot of databases that does not assign foreign keys, but is relational by ...
0
votes
0answers
28 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
0answers
24 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
40 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 ...
3
votes
2answers
62 views

mysql - is it possible to create a partition expression based on two keys / indexes?

if i have a table with, lets say 500 million rows, and among others there are two main individual indexes on that table. the table looks like: CREATE TABLE `t1` ( id_1 bigint unsigned not null, ...
0
votes
1answer
49 views

mysql dump a table to remote host

i want to dump a table from localhost host (with public ip), here is the command so far, the command will be run on localhost mysqldump --opt --host localhost --user=username --password=password ...
-3
votes
1answer
29 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 ...
0
votes
0answers
19 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?
1
vote
2answers
41 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 ...
1
vote
1answer
41 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 ...
1
vote
1answer
14 views

How do you remove reported non existing tables from mysql?

I was trying to export a mysqldump --all-databases and noted errors. A subsequent mysqlcheck --all-databases revealed several non existent tables like the following Error : Table ...
0
votes
1answer
90 views

MySQL 5.1 -> 5.6 problem, select distinct order by limit query stops using index

I'm testing a MySQL 5.1 -> 5.6 upgrade, and I've run into an issue. I have a query that hits a ~30 million row table, selecting distinct IDs with an order by/limit restriction. Sample query is ...
1
vote
1answer
30 views

How to select the line with the max(date)?

the data looks as following: id group date 1 a 2013-01-01 1 b 2014-01-01 2 a 2012-01-01 2 b 2013-01-01 My database is mysql, for each id, I need to select the line with ...
0
votes
1answer
42 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 ...
0
votes
1answer
33 views

CASE conditions in SELECT statement

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
27 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 ...
0
votes
1answer
19 views

How to access another Connect server table in my Local server in SQLyog 7.01?

In same server name using same database select * from area;(Working Good) Same Servername but Different Database select * from mysql.event(Working Good) Now i am connection TWO Different server ...
0
votes
0answers
8 views

Database for large dynamic network graph - best practise?

I am constructing a MySql database which is to contain data for a large network graph. There will be thousands of nodes and ten thousands of edges. The nodes and edges will be assigned a category. ...
0
votes
0answers
29 views

Previously inserted rows disappear at call of PHP script

I am developing a server-side portion of a sport manager application using MySQL (5.1.67), with InnoDB as storage engine, and PHP (5.3.3). When I try to execute a script containing any kind of query ...
0
votes
1answer
21 views

Cross tab query with concat field

I want to create a cross tab query for my table.but when i am using concat i am unable to get ouput succesfully.can any one help me with my query. SET @sql = NULL; SELECT ...
1
vote
1answer
28 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 ...
0
votes
1answer
22 views

How to restore data after MySQL Upgrade?

I upgraded from MySQL 5.5 to 5.6 by uninstalling 5.5 and then installing MySQL 5.6. Now that MySQL 5.6 is running, none of my DBs or data is present/visible in MySQL Workbench. I also see that I ...
1
vote
2answers
57 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
27 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
18 views

self join of single table to find time difference of adjacent rows of a single column which are not in sequence

I want to find the time difference between two adjacent rows of a single table which are not in sequence.i made a self join of table and tried but i did not achieve what i need .can anyone please help ...
0
votes
0answers
12 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
0answers
66 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 ...
3
votes
2answers
101 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 ...
0
votes
1answer
43 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
37 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 ...
0
votes
1answer
68 views

MySQL - Indexes deleted, reduced column size but table size increases

Here is the original table definition which contains 107547934 (~107.5M) rows CREATE TABLE `twitter_tweets` ( `id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ...
1
vote
1answer
19 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
28 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
34 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 ...
0
votes
0answers
11 views

database result encoding changed

In my web site the content from the database is suddenly appearing in the wrong encoding! When I restart mysql the encoding is fixed, but after while it is again changed to the wrong encoding and I ...
2
votes
3answers
139 views

MySQL Explain has different row count than slow query log

I have this entry in slow query log: # User@Host: user[host] @ [ip] # Thread_id: 1514428 Schema: db Last_errno: 0 Killed: 0 # Query_time: 2.795454 Lock_time: 0.000116 Rows_sent: 15 ...
0
votes
1answer
42 views

Update query in a trigger

I have a configuration table: Table: refdatecol - id int(11) startdatecol varchar(100) enddatecol varchar(100) datecolTable varchar(100) This table holds the user configured column names ...
3
votes
4answers
197 views

Best practice for a Table with private columns

I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ...
0
votes
1answer
28 views

Simple SELECT query doesn't return FALSE if no results

This is probably a simple fix, but I can't figure it out. This is the MySQL query I have: SELECT (`clockcards`.`date` IS NOT NULL) AS `clockedin` FROM `users` JOIN `clockcards` ON ...
0
votes
0answers
61 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 ...
5
votes
1answer
280 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 ...
0
votes
1answer
42 views

How do I count the number of different child id's belonging to a parent record?

For instance I have two tables in a music database. Table album and table track. Each track record has (amongst other things), an album_id and an artist_id Each album record has a unique album_id ...
0
votes
1answer
17 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
25 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
20 views

mysql replication on windows server

How to setup replication(Master/slave) in MySQL 5.6 while both servers are windows based? All the things i have fond are Linux based replication. kindly someone help who experienced replication with ...
1
vote
1answer
31 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 ...