.@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 us
-Original Message-
> 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
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 |
++-+---+--+--
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 constant,
> From: 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. :)
That i
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
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 wr
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
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 tem
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
>> >
>> > Bec
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
- Original Message -
> From: "Joey L"
>
> 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 that] the tempf
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
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 ineffi
>> MySQLTuner 1.2.0 - Major Hayden
>> 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:
General S
>
> 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 operation
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 u
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 wrote:
> I have a very large table - approx 3 or 4 gig in size.
> When i initiate a process on my webpage - m
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
On Mon, Oct 24, 2011 at 10:52 AM, Joey L 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
> does not have to
20 matches
Mail list logo