RE: order by in query
Hello i use this query: select i.item_id from orders o INNER JOIN item i ON i.nr=i.nr Should the line above not be ... ON i.nr = o.nr ? INNER JOIN user_cart u ON u.nr=i.nr where (i.count !=0 or i.count!=NULL) and i.isactive=1 and i.kolWo0 order by i.count DESC LIMIT 5 It works quickly without ORDER BY With ORDER BY, CPU goes to 100% and I have to wait. i.count has Btree Index Allways if I use order by is the query very slow also ORDER BY i.item_id is slow to. i.item_id, i.nr has Btree indexes. Mysql version 5.0.45-5 Any Ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by in query
2008/1/2, Edward Kay [EMAIL PROTECTED]: Hello i use this query: select i.item_id from orders o INNER JOIN item i ON i.nr=i.nr Should the line above not be ... ON i.nr = o.nr ? Autch. thank you! It works now. -- Best Regards Vlad Vorobiev http://www.mymir.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql and order in a query
* Paul DuBois At 18:03 +0200 4/6/02, David yahoo wrote: Is there any way to put a sort order different than the alpahbetical order in string. A thing like that order french,german ORDER BY ELT(language,'english','german','french'),Linkname ASC Maybe you meant FIELD(language...? From the manual: Changes in release 3.20.17 -- [...] * The `ELT()' function is renamed to `FIELD()'. The new `ELT()' function returns a value based on an index: `FIELD()' is the inverse of `ELT()' Example: `ELT(2,A,B,C)' returns `B'. `FIELD(B,A,B,C)' returns `2'. The language column is a string containing 'english', 'french' or 'german'. The order of the fields in the FIELD() should be the order of what you want as a result: 'french','german','english' will give you 'french' items first, 'english' items last. Beware that any item with a language not existing in the field list will get a zero value, and be sorted first. -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql and order in a query
I think of a little hack I can add in where clause something like : ELT(language,'english','german','french') != 0 to avoid language not in list even if there no normally :) I try this as soon as i can. thanks. * Paul DuBois At 18:03 +0200 4/6/02, David yahoo wrote: Is there any way to put a sort order different than the alpahbetical order in string. A thing like that order french,german ORDER BY ELT(language,'english','german','french'),Linkname ASC Maybe you meant FIELD(language...? From the manual: Changes in release 3.20.17 -- [...] * The `ELT()' function is renamed to `FIELD()'. The new `ELT()' function returns a value based on an index: `FIELD()' is the inverse of `ELT()' Example: `ELT(2,A,B,C)' returns `B'. `FIELD(B,A,B,C)' returns `2'. The language column is a string containing 'english', 'french' or 'german'. The order of the fields in the FIELD() should be the order of what you want as a result: 'french','german','english' will give you 'french' items first, 'english' items last. Beware that any item with a language not existing in the field list will get a zero value, and be sorted first. -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql and order in a query
At 18:28 +0200 4/6/02, Roger Baklund wrote: * Paul DuBois At 18:03 +0200 4/6/02, David yahoo wrote: Is there any way to put a sort order different than the alpahbetical order in string. A thing like that order french,german ORDER BY ELT(language,'english','german','french'),Linkname ASC Maybe you meant FIELD(language...? Right. Thanks. From the manual: Changes in release 3.20.17 -- [...] * The `ELT()' function is renamed to `FIELD()'. The new `ELT()' function returns a value based on an index: `FIELD()' is the inverse of `ELT()' Example: `ELT(2,A,B,C)' returns `B'. `FIELD(B,A,B,C)' returns `2'. The language column is a string containing 'english', 'french' or 'german'. The order of the fields in the FIELD() should be the order of what you want as a result: 'french','german','english' will give you 'french' items first, 'english' items last. Beware that any item with a language not existing in the field list will get a zero value, and be sorted first. -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
