I got a solution maybe
step 1:
mysql explain select * from users;
++-+---+--+---+--+-+--+--+---+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
-
From: Luis Daniel Lucio Quiroz [mailto:luis.daniel.lu...@gmail.com]
Sent: Thursday, May 24, 2012 12:00 AM
To: Jan Steinman
Cc: mysql@lists.mysql.com
Subject: Re: large temp files created by mysql
I got a solution maybe
step 1:
mysql explain select * from users
Subject: Re: large temp files created by mysql
I got a solution maybe
step 1:
mysql explain select * from users;
++-+---+--+---+--+-+---
---+--+---+
| id | select_type | table | type | possible_keys | key | key_len |
ref
From: mos mo...@fastmail.fm
At 10:34 AM 10/24/2011, you wrote:
select id from table order by rand() limit 1;
is doing as example a dumb temporary table with the full size
Because it has to sort the entire table, then it returns the one row. This of
course is extremely inefficient. :)
Actually, having tried that, you still need the ORDER BY RAND() in there.
Otherwise, I keep getting the same record over and over. But it surely cuts way
down on the number of rows that need to be sorted.
So if your table size is fairly stable, and you pick a good number for the
WHERE
Am 26.10.2011 05:14, schrieb mos:
It is not braindead. You told MySQL to sort by rand() which is a
non-indexed column.
It needs to assign a value to each row of the result set (all ids of the
table) and sort
it to get the lowest random number. This is very inefficient for large
Am 25.10.2011 05:45, schrieb mos:
At 05:31 PM 10/24/2011, Reindl Harald wrote:
Am 24.10.2011 23:31, schrieb mos:
At 11:32 AM 10/24/2011, Reindl Harald wrote:
Am 24.10.2011 18:02, schrieb mos:
At 10:34 AM 10/24/2011, you wrote:
select id from table order by rand() limit 1;
At 03:32 AM 10/25/2011, you wrote:
Am 25.10.2011 05:45, schrieb mos:
At 05:31 PM 10/24/2011, Reindl Harald wrote:
Am 24.10.2011 23:31, schrieb mos:
At 11:32 AM 10/24/2011, Reindl Harald wrote:
Am 24.10.2011 18:02, schrieb mos:
At 10:34 AM 10/24/2011, you wrote:
select id from
I have a very large table - approx 3 or 4 gig in size.
When i initiate a process on my webpage - mysql starts to create a
temporary table in /tmp directory.
Is there a way i can create this file/table ahead of time so mysql
does not have to create it ?
thanks
mjh
--
MySQL General Mailing List
On Mon, Oct 24, 2011 at 10:52 AM, Joey L mjh2...@gmail.com wrote:
I have a very large table - approx 3 or 4 gig in size.
When i initiate a process on my webpage - mysql starts to create a
temporary table in /tmp directory.
Is there a way i can create this file/table ahead of time so mysql
Something you're doing is creating a very large temporary table as
part of handling it's query. Usual culprit would be something doing a
full table join combined with an order by or group by which would
typically cause MySQL to need to create a temp table.
You should do EXPLAINs on your
select id from table order by rand() limit 1;
is doing as example a dumb temporary table with the full size
Am 24.10.2011 17:03, schrieb Joey L:
On Mon, Oct 24, 2011 at 10:52 AM, Joey L mjh2...@gmail.com wrote:
I have a very large table - approx 3 or 4 gig in size.
When i initiate a process
At 10:34 AM 10/24/2011, you wrote:
select id from table order by rand() limit 1;
is doing as example a dumb temporary table with the full size
Because it has to sort the entire table, then it
returns the one row. This of course is extremely inefficient. :)
You need to choose a random row by
You should do EXPLAINs on your slow-running queries to find out which ones
are likely... If it is generating a 30gig file, I'd expect it must be a very
slow query.
I know why the files are being created - but is there anything i can
do on the server to accomodate for large file operations ?
MySQLTuner 1.2.0 - Major Hayden ma...@mhtx.net
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
Am 24.10.2011 18:02, schrieb mos:
At 10:34 AM 10/24/2011, you wrote:
select id from table order by rand() limit 1;
is doing as example a dumb temporary table with the full size
Because it has to sort the entire table, then it returns the one row. This of
course is extremely inefficient.
Am 24.10.2011 18:27, schrieb Joey L:
Performance Metrics -
[--] Up for: 10s (2K q [237.300 qps], 591 conn, TX: 947K, RX: 261K)
[!!] Key buffer size / total MyISAM indexes: 2.0G/12.0G
[!!] Key buffer hit rate: 89.9% (5K cached / 524
- Original Message -
From: Joey L mjh2...@gmail.com
I know why the files are being created - but is there anything i can
do on the server to accomodate for large file operations ?
*We* don't, however :-) If you'd care to enlighten us as to the particular job
and why [you believe
At 11:32 AM 10/24/2011, Reindl Harald wrote:
Am 24.10.2011 18:02, schrieb mos:
At 10:34 AM 10/24/2011, you wrote:
select id from table order by rand() limit 1;
is doing as example a dumb temporary table with the full size
Because it has to sort the entire table, then it returns the one
Am 24.10.2011 23:31, schrieb mos:
At 11:32 AM 10/24/2011, Reindl Harald wrote:
Am 24.10.2011 18:02, schrieb mos:
At 10:34 AM 10/24/2011, you wrote:
select id from table order by rand() limit 1;
is doing as example a dumb temporary table with the full size
Because it has to sort
At 05:31 PM 10/24/2011, Reindl Harald wrote:
Am 24.10.2011 23:31, schrieb mos:
At 11:32 AM 10/24/2011, Reindl Harald wrote:
Am 24.10.2011 18:02, schrieb mos:
At 10:34 AM 10/24/2011, you wrote:
select id from table order by rand() limit 1;
is doing as example a dumb temporary table
21 matches
Mail list logo
