Tagged Questions
1
vote
2answers
42 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 ...
1
vote
0answers
45 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, ...
1
vote
0answers
115 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 ...
1
vote
1answer
99 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. ...
1
vote
3answers
787 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 ...
3
votes
2answers
476 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 ...
2
votes
1answer
3k 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 ...
0
votes
0answers
278 views
Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL
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 ...
1
vote
1answer
328 views
How to handle temporary tables with mutliple connections?
In Is there a way to hint to query optimizer to mysql which contraints should be done first? I created a subquery.
The subquery is often not optimized.
I think a better way is to create a temporary ...
2
votes
2answers
897 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'!
...
2
votes
1answer
1k 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 ...
5
votes
1answer
342 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 ...
