All Questions
Tagged with temporary-tables mysql
75 questions
1
vote
3
answers
690
views
MySQL Temporary Tables and Connection Pools
This is more of a "please confirm/correct my understanding" question.
Background
I connect to MySQL using JDBC, the connection pool has around 250 connections, most of which seem ...
- 127
2
votes
0
answers
160
views
MySQL release memory allocated for a temp table
I'm using 5.6 MySQL database to insert tens of millions records between multiple tables.
Before the insertion I send all my data into a temp memory table, for that reason I have increased both ...
- 121
3
votes
1
answer
222
views
Valid for WITH clause to use existing table name?
Suppose I have a table Entry. Is it valid for a WITH clause to replace the Entry table for the duration of a query? I tried with SQLite and MySql and it seems to work, but I want to be sure this is ...
- 133
1
vote
1
answer
1k
views
MySQL indexing on 11 million rows
I have a development and production server, both with different amounts of RAM. Development machine has 8G, while production has 64G.
I'm importing a large table from development to production, but ...
- 111
3
votes
5
answers
11k
views
Temporary table is full
The table '/mnt/disks/data/tmp/#sql58de_a0f6_7' is full
The query is quite complex/big. Lots of joins in joins and unions, sorting, grouping, ...
1 out of 10 the result is returned. Otherwise it ...
- 143
1
vote
1
answer
27
views
Does MySQL support defining a table inside a session without appending it to the database?
As per stated in the title. Does MySQL support defining locally a table? (as a 'local variable' perhaps?)
I understand (according to 'Source') that MySQL has 'two types of variables': (1) local and (2)...
- 113
1
vote
2
answers
1k
views
Unknown table 'table' in field list on referencing temporary table inside a trigger
I am creating a trigger BEFORE INSERT that checks a record from another table to see if it exists, otherwise signal sqlstate. The trigger definition shown below
DELIMITER //
DROP TRIGGER IF EXISTS ...
- 13
0
votes
1
answer
2k
views
Create table using select statement Truncated incorrect datetime value:
I have looked up different answers on similar question before posting it here.
So i have a table with user id and date column
I would like to store the results of select query to a temporary table.
...
- 3
0
votes
0
answers
134
views
Why does UNION fail here?
I have a problem that I can't seem to figure out how to fix.
What I want to do is simple, select data and append a row of sums as the last row. But I keep getting the error
An error occurred in ...
- 165
1
vote
0
answers
107
views
How to avoid running multiple selects with the same subquery join?
I am using MySQL 5.7.26.
I have 12 SELECT queries which run the same subquery join; since the subquery in a join is very complex (can be more complex, it is a filter query in a web app) I want to ...
- 65
0
votes
1
answer
51
views
Query to filter a table based on a comparison applied to the same table?
Case & Problem:
Consider a corporate building with some rooms and departments that need authorization to enter.
Employees have to scan their badges to be authorized.
If the employee is inside (...
2
votes
2
answers
3k
views
MySQL - Can we use the same temporary table name in different stored procedure?
In our database, we generate statistics by periodically calling a large number of stored procedures in the event.
Considering there are two SP
auto_update_daily_users
auto_update_daily_orders
Is ...
- 176
0
votes
2
answers
2k
views
INNER JOIN is extremly slow on some rows and create temp table
The MySQL query
SELECT `p`.*,
`p.Author`.*
FROM `post` AS `p`
INNER JOIN `user` AS `p.Author` ON `p`.`userid` = `p.Author`.`userid`
WHERE ( ( `p`.`threadid` = 29780 )
AND ( `...
- 151
1
vote
2
answers
179
views
mysql - calculate traffic for tmp tables
I'm thinking of placing tmpdir to SSD - to increase speed of working with sorting data in tmp tables on disk. Our base creates a lot temporary tables on disk during sorting, merging, joining an so on. ...
- 223
0
votes
1
answer
26
views
Dropping rows based on the previous selection statmnet
I have a database with following tables: temp_1, temp_2.... and cached_tbl. Inside cached_tbl I have column table_name and in corresponding rows I store all the temp table names I mentioned above. SO ...
- 3
1
vote
0
answers
82
views
Why does the Select statement stalls when executed directly in sequence?
When executing the following in workbench or in code, the Drop table/Create Table works ok. The Select statement stalls when executed in one go.
If the table is MyIsam, it works ok. If executed [...
1
vote
1
answer
3k
views
mysql memory temp table creation SLOW
I'm trying to get to the bottom of what I'm thinking is a performance issue when creating in memory temporary tables. The underlying query I have looks to me to be pretty well optimized. The explain ...
- 159
2
votes
3
answers
2k
views
I ran MySQLTuner and wondered if anyone would go over the results with me, specifically query cache and temporary tables
Here are the results of MySQLTuner:
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5....
- 123
4
votes
1
answer
3k
views
How to put an execution time limit for MySQL Temporary table creation
We can use SET MAX_EXECUTION_TIME=10; statement to stop the execution of a SELECT statement, if it took more than 10 milliseconds to execute the query. But the same wont work for other statements like ...
- 163
3
votes
1
answer
2k
views
DELETE ... IN hangs after deleting half of a large table (even for deleting 10 rows)
I'm having trouble with a DELETE hanging, even for a small number of rows (10) in a large MariaDB InnoDB table.
Background
I had a large table (~400,000,000 rows) that needed to have rows archived ...
- 76
1
vote
1
answer
25
views
Find rows where column matches one of n strings [closed]
I am trying to find the best way to see if multiple email addresses already exist in my database. I have a table with member id's, emails, and other info, and I need to import members but to check if ...
- 143
2
votes
1
answer
7k
views
How to find MySQL creating temporary table in memory or disk?
I have a query, after running it if i run the show status in the same session like below
show status where Variable_name like '%Created_tmp_disk_tables%';
It got incremented by one value. So it ...
- 53
1
vote
2
answers
66
views
I need to use a permenent table instead of temporary but I still need the table's data to be private to the connection
I currently have an issue whereby an application which I maintain uses temporary tables. This is an issue as it's causing replication errors like found in this mariadb issue.
I'm trying to think of ...
- 304
0
votes
0
answers
519
views
Speed up creation of temp-table from select
I've got a query that runs in about 1.6 seconds and returns 150Kish rows. I have been building a memory engine based temp table from this query and this operation takes about 6.5 seconds. I've sifted ...
- 159
3
votes
2
answers
19k
views
Updating a table from a temporary table where clause error in MySql Community
I am trying to update an existing table named "residential_summary" from a temporary table called "a". I can query the temporary table a but when I try to use it in an update statement I get
Error ...
4
votes
1
answer
6k
views
Values of created_tmp_disk_tables increases
I noticed that the values of Created_tmp_disk_tables increased.
At a certain time, MYD files are created in /tmp directory and my site run very slowly.
I am forced to restart the mysql service to ...
1
vote
1
answer
1k
views
Mysql/InnoDB says it doesn't have access rights to create temp tables, but it does
For mysql Ver 14.14 Distrib 5.6.33, for Linux (x86_64) using EditLine wrapper
in my.cnf:
tmpdir = /var/mysql_tmpfs
in /etc/apparmor.d/usr.sbin.mysqld:
/var/mysql_tmpfs/ rw,
/var/...
- 121
0
votes
2
answers
2k
views
MySQL ran out of disk creating an index, now I can't reclaim my disk
I've run a command on my MySQL database to add an index to a large table containing 8 million records (ironically so I could start deleting some of them):
CREATE ...
- 101
1
vote
1
answer
10k
views
How to remove Using temporary?
Help me please, remove "Using temporary".
I tried many different options, it is impossible to get rid of. Or sorting disappears or "Using temporary" appears.
;(
Category table:
`...
- 11
3
votes
1
answer
1k
views
Incorrect key file for table error. How can I change location of MySQL's temp folder to another part of drive with more disk space?
I am using MySQL with Sequel Pro on a Mac Pro OSX Mavericks and am trying to run the code for a stored procedure in order to add two new columns to a table. The procedure does math operations on ...
- 33
1
vote
1
answer
76
views
MySQL Temporary Tables
I am facing Memory issue in our server. While debugging I came to know that, many number (millions) of temporary tables are created. Is there any way to identify which databases are creating this ...
- 2,300
2
votes
1
answer
2k
views
Removing the uses of MySQL temporary tables
We have a very huge PHP/MySQL application with a lot of legacy code, this application utilized MySQL for most of it's work. It does most of the processing on the database side using temporary tables, ...
- 123
0
votes
1
answer
1k
views
Mysql takes more tmp space than the table size
I have a mysql table with 2.8GB data+index size. I wanted to add a column to the table and it was failing due to insufficient space on /tmp. When I looked at the size available on /tmp it was 3.9GB.
...
- 21
4
votes
1
answer
11k
views
Slow CREATE TEMPORARY TABLE from SELECT in MySQL
I asked this on StackOverflow, but there are not many hints. So:
SHOW CREATE TABLE cashadv_txn:
CREATE TABLE `cashadv_txn` (
`txn_id` int(11) NOT NULL AUTO_INCREMENT,
`cashadv_id` int(11) NOT ...
- 165
3
votes
2
answers
108
views
Why tmp tables created when connected to one database
I am wondering why temp tables created while switching to database. When I login to the mysql server, I have queried current session temp tables as follows:
mysql> show status like '%tmp%';
+----...
- 419
2
votes
1
answer
1k
views
MySQL Filesort/Order By Slow
I've been struggling with a query for the past few weeks and it now just appears as gobbly-gook to me when I try to optimize. The query runs - at about 2.3 seconds, but I know there has to be a more ...
0
votes
2
answers
3k
views
How can i update my database from another database using temporary tables?
I want to update tables of my database using another big database every day with using temporary tables and without returning result. How can i update it?
This my temporary table :
use data_pro;
...
- 121
0
votes
1
answer
860
views
Get the latest record and count them with time interval
I have a table as below
id fk_sales_id status created_at
1 001 5 2015-02-17 16:45:44
2 001 8 2015-02-18 18:45:44
3 002 30 2015-02-20 16:45:44
...
- 101
1
vote
1
answer
2k
views
Can't see temp table from subquery
I have a query that selects data from tables and one temp table. This works fine, but there is a GROUP BY/ORDER BY combination that doesn't work well for me. Indeed, I need to order the results and ...
- 211
3
votes
1
answer
6k
views
Can't Delete Orphaned MySQL Temp Table
One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes).
ls -l *sql*
-rw-rw---- 1 mysql mysql 8570 Mar 13 12:05 #sql-ib32694.frm
-...
- 183
1
vote
3
answers
3k
views
How can delete an orphaned temporary table on Mysql Innodb?
I was issuing an alter table statement and suddenly the server crashed. Now I can not run any alter table command on that table, because it shows me error 1050, and says that a table named #sql1504d ...
2
votes
1
answer
84
views
Slow query on mid-size tables with good index coverage
I'm running a query that touches 2 moderately sized tables (1-5 million rows). EXPLAIN shows it touches only indexes and very few rows, however it takes very long to complete (~8 seconds). The only ...
- 153
2
votes
1
answer
2k
views
MySQL : Avoid Temporary/Filesort Caused by GROUP BY Clause in Views
I'm planning on creating a view using this SQL SELECT, but the explain for it shows it's using temporary and using filesort. I can't figure out what indices I need in order to fix this problem. Mostly,...
- 21
3
votes
1
answer
9k
views
Converting Heap to MyISAM issue
At work we have a problem that has now occurred more then once.
Problem is that query is in state of converting HEAP to MyISAM and it paralyzes our server( query in this state is not able to kill ...
-1
votes
1
answer
655
views
Statements in State "Copying to tmp table" disrupt performance [closed]
On a
MySQL database (version 5.1.73)
a Magento-Shop
executes a statement (A) that uses distinct, see
http://pastebin.com/A2sy45nF
and therefore is "Using temporary" according to "explain".
...
3
votes
1
answer
650
views
When is mariadb creating tmp tables?
I have found, that copying to tmp table is taking most of the time of a slow query, that I wanted to optimize. I watched the output of 'profiling' in a terminal. This happens on a pics table with 6000 ...
- 161
0
votes
0
answers
1k
views
Create separate temporary table/view and variables for every query
How can I create something similar to VIEW or TEMPORARY TABLE for every query separately?
I have 1 process, it creates and holds one connection (doesn't destroy connection after request, so doesn't ...
- 175
0
votes
2
answers
531
views
MySQL excessive tmp disk tables after Upgrade to 5.6
After upgrading two MySQL-Servers which run Master-Slave replication from Percona MySQL 5.5 to Percona MySQL 5.6 an excessive amount of temporary Tables gets created on the slave.
The Master seems to ...
- 101
0
votes
1
answer
739
views
Large NOT IN Statement vs Creating/Populating Temp Table
I have a large daily data feed which can contain up to around 5,000 ID's. I need to delete all pre-existing records in the DB that are not referenced in the new feed. Should I create a temporary table ...
- 113
2
votes
1
answer
1k
views
MySQL: does Index Merge Sort-Union access algorithm use temporary table?
My question is about index_merge optimization in MySQL.
I have a query with a set of range conditions connected with OR. EXPLAIN of the query shows that index_merge method is used and the Extra ...
- 141