All Questions
Tagged with temporary-tables mysql
75 questions
1
vote
0
answers
783
views
MySQL query hanging on copying to tmp table
I'm stuck with a query hanging on 'copying to tmp table' for about 25 seconds according to the profiling.
The query is used for forecasting the number of results after filtering by job_type / ...
- 11
0
votes
2
answers
2k
views
Is a temporary table a good solution for importing temporary data into a database
i am facing the following scenario. I have a student database in which exam results are stored. Every now and then i get a list with the names of certain students for which i need to find out how well ...
- 103
1
vote
1
answer
931
views
MySQL, temporary on disk table on primary key retrieval?
MySQL 5.5.24, Windows 7 64-bit.
I've InnoDB table with primary key defined on three columns:
CREATE TABLE `common_descriptions` (
`object_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`...
- 203
0
votes
1
answer
84
views
Optimize query to minimize tmp copying time
I have a query when Checking the profile i get this :
I read in order to optimize this i'll have to work on my joins .
I also added index on the fields that i'm grouping by (date_seance , ...
- 123
13
votes
3
answers
56k
views
How long will a temporary MEMORY table persist if I don't drop it (MySQL)
I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary ...
- 506
0
votes
1
answer
276
views
MySQL thread states when changing an in-memory temporary table to disk-based
In High Performance MySQL (3rd edition) they state that:
You can't tell whether a table was created in memory and then converted to on-disk or just created on-disk to begin with...
What I was ...
- 359
33
votes
3
answers
108k
views
MySQL creates temporary tables on disk. How do I stop it?
We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in ...
- 463
3
votes
2
answers
9k
views
changing TEMPORARY TABLE default collation
when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
- 663
1
vote
1
answer
6k
views
Can I remove #sql_ .MYD files?
I found some old files in the mysql datadir:
-rw-rw---- 1 mysql mysql 0 2012-10-09 09:39 #sql_263c_0.MYD
-rw-rw---- 1 mysql mysql 1.0K 2012-10-09 09:39 #sql_263c_0.MYI
-rw-rw---- 1 mysql ...
- 113
2
votes
3
answers
11k
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 ...
- 129
2
votes
2
answers
8k
views
What is the size of currently-existing tmp tables in innodb?
This is yet another "how to add a column to a gigantic table" question. The table on disk takes 2.5GB, and I have around 10GB of ram I can use. I have a feeling I can crank up the tmptablesize option ...
- 123
1
vote
0
answers
172
views
Why is this query creating 300+ temporary tables in Mysql?
SHOW global STATUS LIKE '%tmp%';
set @UID = 132;
set @param2 = 'cde';
set @param3 = 20130331;
set @param4 = 'C';
select p.id, p.column44, p.column42, ue.column35, u.id, p.column43, p.column45, ...
- 11
1
vote
0
answers
157
views
TMD auto create
I used mysql as part of a project and found it to be a very practical tool.
I have a question about mysql in regards to how it creates temporary table types TMD in /var/lib/mysql/. This partition ...
- 11
5
votes
1
answer
7k
views
Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
I've got a web application connected to a database running MySQL.
Here's the problem: a not insignificant number of queries create temporary tables and many of the tables involved have BLOB fields. ...
- 159
9
votes
4
answers
31k
views
Why does MySQL produce so many temporary MYD files?
On a Debian Linux server, hosting many PHP/MySQL websites (photo galleries), sometimes I have "many" files like /tmp/#sql_6405_58.MYD.
For example today :
[2012-12-15 15:18:11] /tmp/#sql_6405_6.MYD :...
plegall
8
votes
2
answers
10k
views
Is it bad to create many mysql temporary tables simultaneously?
I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better (...
- 4,531
7
votes
1
answer
53k
views
MySQL: Execute CREATE TEMPORARY TABLE SELECT just one time?
I want to create a temporary table with some select-data.
My (strange) problem is that I have to do the same query more than one time.
CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT NULL, ...
- 195
0
votes
1
answer
2k
views
Inserting query result to another table hangs on "Copying to temp table on disk" on MySQL [closed]
I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows.
The query ...
- 109
2
votes
1
answer
7k
views
How to handle temporary tables with mutliple connections?
In Is there a way to hint to query optimizer to MySQL which constraints should be done first? I created a subquery.
The subquery is often not optimized.
I think a better way is to create a temporary ...
- 1,355
2
votes
2
answers
5k
views
mysql 5.5 corrupt - missing temporary table
One of our mysql instances crashed and failed to start:
120422 10:04:13 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'!
...
- 175
6
votes
2
answers
7k
views
Repairing Myisam Table when there was no additional Disk space & Table corrupted
I had a table which MyIsam as storage Engine contains million rows. There is a purging task and need to remove 35 days old data. i measured up the rows in following manner :
mysql> select count(*)...
18
votes
2
answers
99k
views
"Copying to tmp table" extremely slow
This is my example of query:
SELECT
nickname,
CASE class_id
WHEN 1 THEN 'Druid'
WHEN 2 THEN 'Necromancer'
WHEN 3 THEN 'Mage'
WHEN 4 THEN 'Priest'
WHEN 5 THEN '...
- 181
7
votes
2
answers
11k
views
Prevent copying to temp table (sql)
I have two tables - comments and votes :
comments(id, text, user_id, page_id)
votes(id, value)
There are 2,000,000 rows in the votes table.
I created the following query:
SELECT SUM(votes.value),...
- 71
7
votes
1
answer
681
views
Dealing with temp tables when I have not control over db variables
I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically.
I am wondering if there ...
- 869
6
votes
3
answers
14k
views
Avoiding temporary tables while sorting by a column in a different table
So I have this MySQL InnoDB query here:
SELECT s.vid, s.body, s.timestamp, COUNT(v.id) AS votecnt
FROM stories s
LEFT JOIN votes v ON s.vid = v.vid
WHERE s.lv = 1
AND s.status = 1
GROUP BY s.vid
...
- 525