Re: large temp files created by mysql
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 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 >> >> >> >> but this is TOTALLY braindead if "id" is a primary-KEY with auto-increment >> > >> > It all depends on how many holes you have in the sequence and how random you want the selections to be. If there >> > are no holes then it will work. You need of course to get the first and last id and generate "myrandomnum" within >> > that range. If there are a lot of holes in the sequence then build another table with the columns bin and an >> > autoinc column and pick one of those rows randomly. Regenerate the table once an hour or once a day. >> > >> > Either way it is going to be a LOT FASTER than sorting the entire table >> >> and why in the world is with the query above the WHOLE table >> copied in a temp-table while fecth the whole id-list in a >> php-array and take a random one is more than 1000 times faster? >> >> the implementation if "order by rand()" is totally braindead > > 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 tables. but there is mo need to do this with the whole table if the only requested field is the primary key Sure but if the table has 100 million rows and you want 1 random id, that means sorting 100 million id's from the index to disk. This is still grossly inefficient. It may work fine on tables with a couple thousand rows, but not for million row tables. That's why the two methods I suggested don't use sorting. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
import xml data to multiple tables in mysql
Hi, I have some xml data following the following DTD. It seems that it is better to import the xml file into multiple table, for example, author table, journal table. I read the mysql manual section on "load xml", but I don't see how to load xml in multiple table and how to design the schema automatically from DTD. Does anybody have some suggestions? http://www.nlm.nih.gov/databases/dtd/nlmmedlinecitationset_110101.dtd -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Within-group aggregate query help please - customers and latest subscription row
2011/10/24 16:31 -0700, Daevid Vincent WHERE cs.customer_id = 7 GROUP BY customer_id Well, the latter line is now redundant. How will you make the '7' into a parameter? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mail System Error - Returned Mail
$èÅwú÷EóþpE²Ë¡WDè
m×ÆkD®
pK̹ΨÛÔ°Õe$ÒöÀÐ1"O3øPË{GP¾ÏRB,Rcó1Op»-Ô¦µ|ñËðÆõ&Thã¯QvìâioZS¸¶èrù5.p®¯}*«ÌC¨a3¢¿ÉÝ!ü»¶LÞ}«!ô
Ìì#Ð)'¥LkFL'p:Y§/¤%àµdÆR¼Q©[¬8þ±$(ùD_Ø
8&K¤F¯k~·¦úÀbÌ?§ä(,g]è©ï'
Ī
ö0GÞçk´Òme'È¢ô𣸠¨ù#ö
4µ]c½c`u'§¸ì¶%99{hÔÜèhÞ8Ú~C4Ñ9ÍiAV(±eÛÏGÃýO^òîÁ3Æ
¾QÚ~½ÅÎvÄ)þÙî]üPgÏbÅôtMz´Ûø}iWÜ«U~X-k\TbS<µxFúå7ô
ÿþS m a l l B u s i n e s s S e r v e r h a s r e m o v e d
p o t e n t i a l l y u n s a f e e - m a i l a t t a c h m e n t ( s )
f r o m t h i s m e s s a g e :
e b n . s c r
B e c a u s e c o m p u t e r v i r u s e s a r e c o m m o n l y
s p r e a d t h r o u g h f i l e s a t t a c h e d t o e - m a i l
m e s s a g e s , c e r t a i n t y p e s o f f i l e s w i l l
n o t b e d e l i v e r e d t o y o u r m a i l b o x . F o r
m o r e i n f o r m a t i o n , c o n t a c t t h e p e r s o n
r e s p o n s i b l e f o r y o u r n e t w o r k .
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: large temp files created by mysql
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; >> >> >> 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 >> >> >> >> but this is TOTALLY braindead if "id" is a primary-KEY with auto-increment >> > >> > It all depends on how many holes you have in the sequence and how random >> > you want the selections to be. If there >> > are no holes then it will work. You need of course to get the first and >> > last id and generate "myrandomnum" within >> > that range. If there are a lot of holes in the sequence then build another >> > table with the columns bin and an >> > autoinc column and pick one of those rows randomly. Regenerate the table >> > once an hour or once a day. >> > >> > Either way it is going to be a LOT FASTER than sorting the entire table >> >> and why in the world is with the query above the WHOLE table >> copied in a temp-table while fecth the whole id-list in a >> php-array and take a random one is more than 1000 times faster? >> >> the implementation if "order by rand()" is totally braindead > > 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 tables. but there is mo need to do this with the whole table if the only requested field is the primary key signature.asc Description: OpenPGP digital signature
