List:General Discussion« Previous MessageNext Message »
From:Luis Daniel Lucio Quiroz Date:May 24 2012 6:59am
Subject:Re: large temp files created by mysql
View as plain text  
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 |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    |
NULL | 32883093 |       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)

so you get the "rows" field

Step2:
select * from users, limit $r,1


What do you think? Is the only way i found what delays seconds not
minuts. USERS is a 19GB Table for me.

LD

2011/10/30 Jan Steinman <Jan@stripped>:
> 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, you can make this quite speedy.
>
> Still, it seems there should be a better way...
>
> On 30 Oct 11, at 18:51, Jan Steinman wrote:
>
>>> From: mos <mos99@stripped>
>>>
>>>
>>> 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 is absolutely incredible and counter-intuitive, and (as you say) extremely
> inefficient!
>>
>> This is used everywhere. Perhaps it is one of the biggest "anti-patterns" in SQL.
> I just checked two different SQL "cookbook" sites, and they both recommend ORDER BY
> RAND().
>>
>> I just googled around a bit, and found that putting RAND() in the WHERE clause is
> very efficient:
>>
>> SELECT id FROM table WHERE RAND() < 0.01 LIMIT 1
>>
>> The comparison constant can be optimized for the number of rows you have. The
> above returns the first record of 1% of the table. If you have a million rows, you might
> want to bump that to something like 100 parts per million or so.
>>
>> But really, folks, this is something so ubiquitous and so recommended, why can't
> the query optimizer look out for ORDER BY RAND() and simply skip the table sort and just
> grab some record? (Hopefully using something better than Knuth's LCRNG...)
>>
>> ----------------
>> Learning to think wholistically requires an overriding, or reversal, of much of
> the cultural heritage of the last few hundred years. -- David Holmgren
>> :::: Jan Steinman, EcoReality Co-op ::::
>>
>
> ----------------
> Within a few human generations, the low-energy patterns observable in natural
> landscapes will again form the basis of human system design after the richest deposits of
> fossil fuels and minerals are exhausted. -- David Holmgren
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
Thread
large temp files created by mysqlJoey L24 Oct
  • Re: large temp files created by mysqlJoey L24 Oct
    • Re: large temp files created by mysqlReindl Harald24 Oct
      • Re: large temp files created by mysqlmos24 Oct
        • Re: large temp files created by mysqlJoey L24 Oct
          • Re: large temp files created by mysqlReindl Harald24 Oct
        • Re: large temp files created by mysqlReindl Harald24 Oct
          • Re: large temp files created by mysqlmos24 Oct
            • Re: large temp files created by mysqlReindl Harald25 Oct
              • Re: large temp files created by mysqlmos25 Oct
                • Re: large temp files created by mysqlReindl Harald25 Oct
                  • Re: large temp files created by mysqlmos26 Oct
                    • Re: large temp files created by mysqlReindl Harald26 Oct
Re: large temp files created by mysqlAntony T Curtis24 Oct
  • Re: large temp files created by mysqlJoey L24 Oct
    • Re: large temp files created by mysqlJohan De Meersman24 Oct
Re: large temp files created by mysqlJan Steinman31 Oct
  • Re: large temp files created by mysqlJan Steinman31 Oct
    • Re: large temp files created by mysqlLuis Daniel Lucio Quiroz24 May
      • RE: large temp files created by mysqlRick James24 May
        • RE: large temp files created by mysqlLuis Daniel Lucio Quiroz24 May