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
0answers
4 views

Mysql: secondary key that's 'parallel' to primary--do joins wherein the resulting primary keys are contiguous perform better?

Say I have a table ( person_id INT AUTO_INCREMENT, birthday_timestamp BIGINT, country, state, ..., PRIMARY KEY (person_id), KEY (birthday_timestamp) ) where people are added the instant ...
0
votes
0answers
7 views

Mysql query larger than tmpfs (ramdisk)

I recently set up a ramdisk tmpfs on our mysql server and the performance improvement has been quite noticeable. I've allocated 24 out of 32 GB's of ram for the ramdisk. My only concern is what ...
-1
votes
0answers
15 views

Server settings with 36GB handling app+data

I'm setting up a server to handle a multi-site configuration on a beta application for a partner in a new business venture. Trying to assess sane values for MySQL. Currently I have set: ...
1
vote
0answers
8 views

Access denied when trying to use Mysqlimport Utility (using Root Credentials)

We are trying to import large CSV files through Mysqlimport as described in This & This article. But even though I am using root credentials I keep getting the following error: mysqlimport: ...
0
votes
0answers
22 views

MySQL Return average for every unique id per day

I have a table: Unique ID, DeviceID, Temperature, Epoch And 3 sensors (DeviceID's) sending temperature every minute to the database. I would like to select to return AVG(Temperature) for every ...
0
votes
0answers
14 views

Does use of remote servers increase overhead?

In considering the use of remote databases/servers, to what extent does their use increase overhead in terms of time as well as CPU usage and communication costs? Is there documentation on this issue? ...
2
votes
2answers
62 views

Need help improving sql query performance

I have this table: CREATE TABLE `property_ads_history` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `property_ad_id` int(10) unsigned NOT NULL, `advertiser_type` varchar(255) COLLATE ...
0
votes
0answers
12 views

Result consisted of more than one row in procedure

i confused with this i try not using cursor and same get error so i back using cursor but before this day my query success and can generate but this get error please help me i already look other ...
1
vote
1answer
18 views

Is the SQL syntax for InnoDB gauranteed to be the same between MySQL, MariaDB and Percona?

I develop an application that uses MySQL and explicitly uses InnoDB table engine. In a few cases we have clients using this application with MariaDB or Percona. I understand that MariaDB and Percona ...
1
vote
1answer
24 views

When to disable MySQL Query Cache

I have a high volume MariaDB 10 server thousands of databases of with over a million tables. Currently we are only hitting around 20% query cache hits. We are already employing Memcache and other ...
1
vote
1answer
26 views

ON DELETE CASCADE doesn't work even storage table is InnoDb

I tear my hair with the ON DELETE CASCADE Clause. In fact I have two tables I'm on linux and I have installed lamp with phpmyadmin. That's the tables : CREATE TABLE a( Idatable bigint(20) ...
0
votes
3answers
22 views

Any script to auto test tune and benchmark mysql config?

I'm looking or willing to do a script to benchmark mysql config variations for a specific system. A script that would change certain mysql config values and do a sysbench, save results, change ...
1
vote
1answer
21 views

Redundant MySql Configuration

I'm trying to figure out the best way to configure MySql so that I can have redundancy - so for example - I would have two servers, both through the same host, but at different datacenters in the ...
2
votes
1answer
47 views

Can I avoid a join for my select procedures?

Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory): delimiter // CREATE PROCEDURE mysql.selectTables (_first_name ...
0
votes
1answer
14 views

Can I load a CSV into a stored procedure that inserts into multiple tables?

OK, an example is often easier to follow, so here is my procedure: delimiter // CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, ...
-6
votes
1answer
27 views

SQL Simple Question [on hold]

This looks correct to me, however I receive a table where figures exceed $40.0?
2
votes
0answers
29 views

How do I guarantee inventory numbers are unique across all tables?

I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...
1
vote
1answer
24 views

(SOLVED) Mysql bug decimal comparison

I'm currently writing unit tests for a stored procedure that calculates the amount of taxes and the tax rate based on the country/province the customer lives in. Somehow, MySQL considers that ...
0
votes
4answers
43 views

How can I use MySQL variables in subqueries?

I'm trying to run the following query, but it keeps getting "reduced" and failing. Cannot get MySQL to provide any information as to why. Here's the query: SELECT * FROM animals WHERE name IN(SELECT ...
1
vote
1answer
13 views

Managing ibtmp1 File Growth on MySQL 5.7

We recently upgraded to MySQL 5.7, and now we're into production we're finding the ibtmp1 file is growing on a few servers. Mostly it's not a problem, but we have had a couple of scary moments where ...
0
votes
1answer
50 views

Simple SQL question [on hold]

I'm try to resolve this but I don't understand? My query Select FirstName, City FROM Customer WHERE City NOT IN ('Paris') Error Your answer does not contain the expression <>
0
votes
1answer
55 views

MySQL - NOT IN … vs LEFT JOIN

I am trying to understand in terms of performance which approach is better: Query 1: SELECT * FROM table1 WHERE col1 NOT IN(SELECT col1 FROM table2) Query 2: SELECT * FROM table1 LEFT JOIN table2 ...
-1
votes
1answer
43 views

Why and how to index a MySQL database [on hold]

How to do indexing and what is the use? Please explain in such way so I can use it practically.
3
votes
3answers
30 views

Call to default values (MySQL) in a procedure

For the sake of an experiment, we're working with the following procedure (same DB, multiple tables): delimiter // CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name ...
-1
votes
0answers
24 views

Error: MySQL shutdown unexpectedly [on hold]

I have installed Joomla on the XAMPP server. When I first installed, it started and worked fine. But after the next time I tried running it, it gave that "404 Not found" on the browser. The error on ...
3
votes
2answers
52 views

Is it a good approach to duplicate data in a database?

I have a program that needs to store traceroutes (among other data). Here is a diagram that depicts the business scenario: My use case right now is to store the traceroutes using the following ...
0
votes
0answers
29 views

MySql - Unique key based on many-to-many pivot table [on hold]

I have to manage artists and albums tables. | artists | | albums | | album_artist | +--------------+ +--------------+ +--------------+ | id | | id | | id ...
0
votes
2answers
31 views

Stored Procedure Is Slower Than Query Due to Collation

I'm running two servers with MySql installed. I'm having an issue with a slow stored procedure (approximately 3 secs vs 0.1 seconds), solely on my production server, that is very quick on development ...
3
votes
0answers
27 views

MySQL queries waiting for other queries to finish

We have 30-40 different projects in Python and PHP that update, insert and select more than 1 million rows of data in MySQL DB every day. Currently we use InnoDB Engine for our tables. The problem: ...
0
votes
2answers
23 views

Good reason to convert LONGTEXT column to smaller width if field is smaller?

We have a database that uses a LONGTEXT type for a column that does not need that much space. The maximum row size currently has about 6500 characters and that's with a few million inserts so it's ...
-1
votes
0answers
23 views

Financial transactions model with fixed releases (monthly) [on hold]

I need a financial transactions structure that accepts fixed transactions. These releases should repeat every month without a daterminate date to end. But, if you want to delete a transaction in a ...
0
votes
1answer
11 views

MySql crashes in VPS

I have a VPS in GoDaddy, which is crashing MySql service at frequent times. Here is the log file that may help to help me. 160425 09:24:53 mysqld_safe mysqld from pid file ...
1
vote
2answers
77 views

How can I remove 'Using temporary ' and speed up my query

Hi I was working for hours to speed up this query using indexes, but its useless.. im tyring to optimize by eliminating 'Using temporary' and using only indexes. I wonder if there is another ...
0
votes
0answers
10 views

Connecting to MySQL VM remotely (Azure Resource Manager)

I have a VM setup in Azure (running Windows Server 2012 R2) with MySQL 5.7 installed. I am trying to access this using MySQL Workbench - which when running on the server works perfectly. However, ...
0
votes
0answers
17 views

slave auto update - MySQL Replication [on hold]

i have server replication (Master - Slave) i delete data on Slave, after one period slave will auto update data like data on Master... Help me
0
votes
2answers
21 views

Execute query using daily timestamp (mysql event)

I was trying to find a way for give users a daily "point" per online day. So my plan was setup a MySQL event to run this query: UPDATE users SET points=points+1 WHERE timestamp > "X" (Where X ...
0
votes
1answer
28 views

Get distinct count from row and display in sepearte column

I am trying to get count of section type for each section id and course id My sections table ╔════╤════════════╤═════════╗ ║ id │ course_id │ name ║ ╠════╪════════════╪═════════╣ ║ 1 │ 1 ...
0
votes
2answers
31 views

MySQL encryption for 'database at rest' on cloud services

We are migrating our system to a cloud environment (AWS), and have been given new client requirements for having our MySQL data-at-rest encrypted. Currently it is unclear how far this should go. It ...
1
vote
1answer
46 views

MySQL INSERT … SELECT Performance

I know that this query will lock table2: UPDATE table1... SELECT .. FROM table2 How about this query ? INSERT INTO table1... SELECT .. FROM table2 Does this query also create a lock on table2?
0
votes
1answer
79 views

Need help improving query performance

I have this simplified version of my database: http://sqlfiddle.com/#!9/4b903/2/0 I have a million rows in history and my problem is the query is so slow. It would take more or less 2 minutes. ...
1
vote
1answer
53 views

Insert all dates from a month into a table

I'm confused. How to get all dates from a month and insert them into a table in a database? I want to do this with a trigger so when I make a period, ex April 01-04-2016 / 30-04-2016, then in table ...
0
votes
0answers
34 views

which approach gives better performance ? using while or foreach in sql data [on hold]

I have a question regarding performance , our code fetch data from remote server and do calculation and generatete csv file and then zip all the csv ; it took too much time. can you throw some ...
3
votes
1answer
30 views

does merging tables always increases performance?

Consider we have data from visitors in a web site. And you are trying to create a database design for data mining. There are some common fields which users have such as browser,ip, etc... . But there ...
1
vote
1answer
31 views

mysql getting column data from one table to line up correctly with column data from another table

I need the query to produce this: person_id last_name first_name region_idregion name year month amount_sold 1 barnum phineas 1 maricopa 2016 1 800000 1 barnum phineas 1 maricopa 2016 ...
0
votes
1answer
20 views

How to return null values from a joined table if the matching row is missing?

Here is what I need to achieve: person_id last_name first_name region_id region name 1 barnum phineas 1 maricopa 2 loman ...
0
votes
0answers
12 views

Beginner explanation on MySQL cluster? [duplicate]

I am a beginner on MySQL and so far I have a web application with only one MySQL server running. I want to add more MySQL servers and started to read about MySQL clustering, but have a problem with ...
1
vote
1answer
74 views

Why using where in indexed field is slow?

we have a big table. the table structure is : CREATE TABLE `visit_logs` ( `id` int(10) NOT NULL AUTO_INCREMENT, `user_id` int(10) NOT NULL, `ref_id` int(10) NOT NULL DEFAULT '0', `link_id` ...
0
votes
1answer
54 views

MySQL Performance Tuning for MyISAM

I need to performance tune a mysql server and need help. I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are ...
0
votes
1answer
13 views

How can I disable xb_doublewrite?

Getting error while service restart. [ERROR] InnoDB: A parallel doublewrite file /test/mysql/xb_doublewrite found on startup.
0
votes
1answer
20 views

MySQL - A Count with INNER JOINS - that multiplies the result by 8

The following SELECT with INNER JOINS works, but does not count it right. I have a tests table: Tests TestNumber (int primary key) TestComplete (int 0 = not complete, 1 = complete) ...other stuff ...