Re: Correlated subquery help
isn't the where subquery would always return only one record if set of (k1,k2) is a primary key? I dont have 4.1+ installed to test on but if you remove 'limit 10' and run it don't you get the same list as if you ran 'select k1,k2,total_amt from Z' ? - Original Message - From: Rick Robinson [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Wednesday, December 01, 2004 6:08 PM Subject: Correlated subquery help Hi all- I'm using MySQL 4.1.7, trying to do a subquery that's apparently unsupported - I'm hoping someone can provide a quick alternative for me. I have a simple table Z with 3 columns, k1, k2, and total_amt, where k1 and k2 make up the primary key. I want to create a report that lists the the top 10 total_amt for each k1, k2. My original query was going to be of the form: sql select a.k1, a.k2, a.total_amt from Z a where a.total_amt in (select b.total_amt from Z b where b.k1 = a.k1 and b.k2 = a.k2 order by b.total_amt desc limit 10) order by a.k1, a.total_amt desc ; /sql But I get the dreaded error: ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Is there a better way to do this query? Thanks for your help. Regards, R -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A Select improvement
Hi, anyone has suggestions what changes to make to allow this query to run faster? SELECT domain FROM tbl_1 WHERE id 0 and id 2 domain = 12.221.190.111 AND score IS NOT Null AND data LIKE %param=search GROUP BY domain, data -- every one of those WHERE clauses makes the query very slow. for about 50 million records with 200-900 thousand matching records it takes about two minutes if I only have the straight domain = some string, then almost quadriples if I add the data Like pattern clause. It is very slow considering that the WHERE has to be ran many many times with different parameters here is table info : # Table: 'tbl_1' # CREATE TABLE `tbl_1` ( `id` int(10) unsigned NOT NULL auto_increment, `domain` varchar(50) NOT NULL default '', `data` varchar(200) default '', `score` int(11) default NULL, PRIMARY KEY (`id`), KEY `score` (`score`), KEY `domain` (`domain`), FULLTEXT KEY `data` (`data`) ) TYPE=MyISAM; heres query info (it remains the same if i remove some WHERE clauses except for rows count going up when i do: table| type | possible_keys | key| key_len | ref| rows | Extra | tbl_1|ref | score,domain | domain | 50 | const |1 | Using where; Using temporary; Using filesort | --- thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
I think he gave you the right answer. you can also use joins instead of where, but it is the same idea, not sure which one of the two will be faster though: select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups left join users as owner on owner.uid = groups.groupowner left join users as creator on groups.groupcreator = creator.uid - Original Message - From: Kris [EMAIL PROTECTED] To: Peter Valdemar Mørch [EMAIL PROTECTED] Cc: Kris zoob-at-doomstar.com |Lists| [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 7:08 PM Subject: Re: help with SQL (join?) query No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT on string
hi i have a recordset of about 4 mil records, SELECT * from rec where string_field=somestring takes very long time (30+ sec). string_field is indexed MUL. Is there way to make it faster?
Re: SELECT on string
i was mistaking before, query is more like : SELECT MyField, count(id) from MyRec where string_field=somestring group by field; and it's explain is : +-+--+---++-+++-+ | table | type | possible_keys | key| key_len | ref| rows | Extra | +-+--+---++-+++-+ | MyRec| ref | MyField | MyField | 21 | const | 151609 | Using where | +-+--+---++-+++-+ takes about minute and a half. - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Dan Sashko [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, November 10, 2004 6:01 PM Subject: Re: SELECT on string What does EXPLAIN SELECT * from rec where string_field='somestring'; say? Michael Dan Sashko wrote: hi i have a recordset of about 4 mil records, SELECT * from rec where string_field=somestring takes very long time (30+ sec). string_field is indexed MUL. Is there way to make it faster? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT where String
hello, I've a query that runs very slow: select name, count(id) where str_field = some string or (str_field string with nuber at end 1 and str_field string with nuber at end 9) group by name i have about 4mil records and the query takes about 3 minutes str_field, name are MUL indexes both varchar(100) and id is a pk. is there a way to speed up the query? (the and then seemed to be pretty fast but adding or str_field = 'some string' made it really slow).
Re: Slow ORDER BY query..
from mysql: With EXPLAIN SELECT ... ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column. . further : In MySQL 4.1 and up, a filesort optimization is used that records not only the sort key value and row position, but also the columns required for the query. This avoids reading the rows twice. so suggestion is to upgrade to 4.1+. This is most likely not a viable solution :) so further from their website: If you want to increase ORDER BY speed, first see whether you can get MySQL to use indexes rather than an extra sorting phase. If this is not possible, you can try the following strategies: a.. Increase the size of the sort_buffer_size variable. b.. Increase the size of the read_rnd_buffer_size variable. c.. Change tmpdir to point to a dedicated filesystem with lots of empty space. If you use MySQL 4.1 or later, this option accepts several paths that are used in round-robin fashion. Paths should be separated by colon characters (`:') on Unix and semicolon characters (`;') on Windows, NetWare, and OS/2. You can use this feature to spread the load across several directories. Note: The paths should be for directories in filesystems that are located on different physical disks, not different partitions of the same disk. also from their site, if I am not mistakin, they suggest to use GROUP BY the colum that will be sorted by (which forces the sort and might eliminate the rereading of the rows) with ORDER BY NULL at the end to eliminate the overhead of sorting (since it will be sorted in 'GROUP BY deletedate DESC') : SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 GROUP BY deletedate DESC ORDER BY NULL LIMIT 20 ; if your deletedate is not unique then mesh some already used key into it (ID or subcatID) to eliminate actual grouping: SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 GROUP BY deletedate DESC, ID ORDER BY NULL LIMIT 20 ; (note to that, you ID field, thought autoincremented, is not set to be unique, so unless you know it is unique for a fact then meshing it with deletedate might still produce undesirable grouping. In that case you might have to add some unique number in group by clause liek a current row counter, or worst case a random number). --- It might not speed up the query depending on the size of the WHERE results, but might not be as random on completion time. Curiouse if it does anything, let me know if you try. - Original Message - From: Aaron [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 25, 2004 4:19 PM Subject: Slow ORDER BY query.. Hi all , I am currently experiencing an issue with a query I would have thought to be somewhat straightforward. Perhaps someone could shed some light on what might be causing this? The below example was running by itself , not waiting for any other queries. It just took a bloody long time to run. The system load went to around 7 or so , however the CPU's were not taxed at all. Of curious note to me , is that it seems to be intermittently taking a long time. Upon restarting of the server and flushing the cache , some queries will take 1 second , some will take around 5 , and some will take ridiculously long times. I think that has to do perhaps with the amount of rows matching before the ORDER BY? I've included all the information I can think of below if anyone feels like having a look, It would be be greatly appreciated. Thanks! Aaron ~~ MySQL Version: ~~ MySQL 4.0.18 on RedHat Linux The Query mysql SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 ORDER BY deletedate DESC LIMIT 20 ; +-+ | ID | +-+ | 1653497 | | 1653498 | | 1653506 | | 1652861 | | 1652685 | | 1652784 | | 1651739 | | 1650276 | | 1650323 | | 1649569 | | 1649079 | | 1649228 | | 1649410 | | 1649411 | | 1648444 | | 1648543 | | 1648877 | | 1648897 | | 1648911 | | 1648308 | +-+ 20 rows in set (2 min 52.20 sec) Record Count: mysql SELECT count(1) FROM Offers_To_Buy ; +--+ | count(1) | +--+ | 461216 | +--+ 1 row in set (0.00 sec) Explain Output: mysql EXPLAIN SELECT ID FROM Offers_To_Buy WHERE subcatID = 336 ORDER BY deletedate DESC LIMIT 20 ; +---+--+--+-+-+---+--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra | +---+--+--+-+-+---+--+-+ | Offers_To_Buy | ref | subcategory,scdd | subcategory | 4 | const | 8562 | Using where; Using filesort | +---+--+--+-+-+---+--+-+ 1 row in set (0.00 sec) The Table: mysql describe Offers_To_Buy ;
SELECT earliest unique records
I have a table that contains a item_id field (non unique index) id field and a date field. How would you go about selecting rows from the table (single row for each item_id with the earliest date field for that item_id). If I use group by item_id the date field will be whatever the first date field it had, so ordering by it at that point will not yeald the intended results. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT earliest unique records
thank you, this does return the proper date field for the item_id, however the rest of the fields of the records are still from the first record in the table. How would you make it so that the entire row is the one that contains the earliest data field ? - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 12, 2004 2:47 PM Subject: RE: SELECT earliest unique records [snip] I have a table that contains a item_id field (non unique index) id field and a date field. How would you go about selecting rows from the table (single row for each item_id with the earliest date field for that item_id). If I use group by item_id the date field will be whatever the first date field it had, so ordering by it at that point will not yeald the intended results. [/snip] Use MAX(date field) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copy a record
hi, a quick question. is there a way to duplicate a record (without knowing fields in advance) in sql alone? the only thing is know is that `id` is a primary key (autonumber) something of this sort: INSERT INTO the_table SELECT * FROM the_table WHERE id=the_number this fails due duplicate entry for the primary key. Is there way to select * minus the id field? thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
