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 using an auto-inc field. Something like:
select id from table where id>=myrandomnum limit 1
Here is the complete article:
http://www.greggdev.com/web/articles.php?id=6
Keep in mind that if there are a lot of holes in
your table where id is not sequential, then the
randomness will favor the number following the
missing ids. So if you had rows with id's from 1
to 1000, but the rows 100 to 199 were missing,
then the number 200 will appear 100x more often
than any other random number (assuming there were
no other missing numbers). So for this to be as
random as possible, then you should keep the
missing id's as small as possible. The other
solution is to renumber a non-autoinc column
every so often to make sure the numbers are truly
sequential and of course use a stored procedure
to find the next highest number when adding new rows.
Mike
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 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
>
Sorry - it is 30 gig file .
--
Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/
http://www.thelounge.net/signature.asc.what.htm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
