onion2k wrote:Do you have an index on the table?
YES, index is set and auto incrementing 'id' column.
right now, my solution is just this:
Code: Select all
SELECT *
FROM `000_data_01`
WHERE `data_used` =0 AND
`id` >=0 AND
`id` <=99999
ORDER BY RAND( )
LIMIT 10
But this only will do random between zero and 100,000, what about the other 2.4 million?
When I set it to:
Code: Select all
SELECT *
FROM `000_data_01`
WHERE `data_used` =0 AND
`id` >=0 AND
`id` <=999999
ORDER BY RAND( )
LIMIT 10
note, the extra nine, that's zero to one million, I was getting the mysql error 28 again, and I was watching my server's specs, "df -h" particularly, and i saw that before and after the script was running /tmp has just about 2 gigs free space, only 36 megs used. WHILE running the zero to one million, it was showing /tmp usage was 99% of 2 gb. LoL ~!!!
so my idea now is to get the total number of rows, and using php i can break $x= 2.5 million, into 25 range increments of 100k each, and then use a random variable to randomly select one of those 25 increment ranges and then use that with sql.
It's hardly what I'd like to do as it's quite ecentric and too much extra coding, I just don't understand why mysql can't handle it?
I guess mysql can't return 2 million results into ORDER BY RAND() and then LIMIT 10.
If someone else wants to help me with this that'd be great!
Just use this is sql on a large table, you need a few million rows of course!
Code: Select all
SELECT *
FROM `000_data_01`
WHERE `data_used` =0 AND
`id` >=0 AND
`id` <=25000000
ORDER BY RAND( )
LIMIT 10
On to my next matter, i need to know how to get the total num rows of the table, #1, and then I also need to get the NAMES of all the tables in the database...
Thanks in advance, hopefully I can get this coded today.