MySQL sorts Norwegian/Scandinavian Characters wrong
Hi, When I try to sort the alphabet, the three characters only used for the norwegian language, Æ, Ø and Å, are sorted wrong. They should be sorted in the order ÆØÅ, but they're sorted ÅÆØ. I read some place that I could change the character-set to danish, and that that would solve the issue (as danish and norwegian as the same alphabet), but it didn't. Anyone know how to do this? Best regards, Haakon Nilsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why are my sorts so slow?
Bruce, Bruce Leidl schrieb: > > I'm having a problem with some very slow queries that spend a very long > time in the 'Sorting result' state and I'm wondering how sorts are > implemented in mysql and what I can do to optimize these types of queries. > > The query looks something like this: > > SELECT col1,col2,col3 from table1 where col1 = 0 ORDER BY col2 DESC > LIMIT 10; > > Both col1 and col2 have individual indexes (idx_1, idx_2) try a concatenated index "col1_col2" the first part of the index can be used for the WHERE part, the second (hopefully) for the SORT part of your query. Regards, Frank. > > Running an EXPLAIN on the query gives me the following information: > > table: table1 > type: ref > possible_keys: idx_1 > key:idx_1 > key_len:4 > ref:const > rows: 10269 > extra: where used; Using filesort > > I understand that this is retrieving a lot of rows which is slow, but > after the rows have been retrieved shouldn't the sort execute very > quickly using the index on the sorted column? I notice that the EXPLAIN > makes no mention at all of the index on the column being sorted. What > is a filesort and what are the different ways that mysql can sort a > result set? > > thanks, > > --brl > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why are my sorts so slow?
MySQL can use indexes to sort, but not when your WHERE clause contains a column other than the one being sorted on. I think the MySQL manual has a good section on what 'filesort' means and when MySQL uses it. Basically it's selecting all the rows that meet your WHERE clause, then running a quicksort algorithm over the results. One thing you can do to speed up this kind of query is reducing the number of rows it has to sort before it can select the last 10, by using a more restrictive WHERE. For example, I had a query very much like this that displayed the last 50 records to be put in the database sorted by time. Since I knew more than 50 records a day came in, I added a 'WHERE Time >' clause to only select records from the last 24 hours. This made a huge difference because it was only selecting a couple hundred records instead of a million or so before doing the sort and throwing away all but the last 50. I don't know if that was clear or not, but what I'm trying to say is, if you can limit the amount of data that matches the WHERE that you're going to be throwing out anyway, do it. :) > -Original Message- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > Sorts don't use indexes, for the most part, only the search > part does. > Someone correct me if I'm wrong on that, I think I recall > reading it in > SQL for Smarties. > On Friday, June 13, 2003, at 02:08 PM, Bruce Leidl wrote: > > > > > I'm having a problem with some very slow queries that spend a very > > long time in the 'Sorting result' state and I'm wondering how sorts > > are implemented in mysql and what I can do to optimize > these types of > > queries. > > > > The query looks something like this: > > > > SELECT col1,col2,col3 from table1 where col1 = 0 ORDER BY col2 DESC > > LIMIT 10; > > > > Both col1 and col2 have individual indexes (idx_1, idx_2) > > > > Running an EXPLAIN on the query gives me the following information: > > > > table: table1 > > type: ref > > possible_keys: idx_1 > > key:idx_1 > > key_len:4 > > ref:const > > rows: 10269 > > extra: where used; Using filesort > > > > I understand that this is retrieving a lot of rows which is > slow, but > > after the rows have been retrieved shouldn't the sort execute very > > quickly using the index on the sorted column? I notice that the > > EXPLAIN makes no mention at all of the index on the column being > > sorted. What is a filesort and what are the different ways > that mysql > > can sort a result set? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why are my sorts so slow?
Sorts don't use indexes, for the most part, only the search part does. Someone correct me if I'm wrong on that, I think I recall reading it in SQL for Smarties. The index is on the entire database, not on the subset that you have selected. If you know that your query is going to return most of the database, you may want to use HAVING instead of WHERE. Then the index can be used on the sort and the filter will be applied after the sort. Keep in mind that the index won't be used on the filter then. On Friday, June 13, 2003, at 02:08 PM, Bruce Leidl wrote: I'm having a problem with some very slow queries that spend a very long time in the 'Sorting result' state and I'm wondering how sorts are implemented in mysql and what I can do to optimize these types of queries. The query looks something like this: SELECT col1,col2,col3 from table1 where col1 = 0 ORDER BY col2 DESC LIMIT 10; Both col1 and col2 have individual indexes (idx_1, idx_2) Running an EXPLAIN on the query gives me the following information: table: table1 type: ref possible_keys: idx_1 key:idx_1 key_len:4 ref:const rows: 10269 extra: where used; Using filesort I understand that this is retrieving a lot of rows which is slow, but after the rows have been retrieved shouldn't the sort execute very quickly using the index on the sorted column? I notice that the EXPLAIN makes no mention at all of the index on the column being sorted. What is a filesort and what are the different ways that mysql can sort a result set? thanks, --brl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why are my sorts so slow?
I'm having a problem with some very slow queries that spend a very long time in the 'Sorting result' state and I'm wondering how sorts are implemented in mysql and what I can do to optimize these types of queries. The query looks something like this: SELECT col1,col2,col3 from table1 where col1 = 0 ORDER BY col2 DESC LIMIT 10; Both col1 and col2 have individual indexes (idx_1, idx_2) Running an EXPLAIN on the query gives me the following information: table: table1 type: ref possible_keys: idx_1 key:idx_1 key_len:4 ref:const rows: 10269 extra: where used; Using filesort I understand that this is retrieving a lot of rows which is slow, but after the rows have been retrieved shouldn't the sort execute very quickly using the index on the sorted column? I notice that the EXPLAIN makes no mention at all of the index on the column being sorted. What is a filesort and what are the different ways that mysql can sort a result set? thanks, --brl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorts
Hi. Please start a new thread next time, instead of replying to an existing one with a different topic. On Mon, Apr 29, 2002 at 08:47:22AM +0100, [EMAIL PROTECTED] wrote: > Hi List, > > If I run the sql query SHOW STATUS how can I tell how many queries > required sorting? I don't think there is a easy way. > Here are some of the variables SHOW STATUS returns > > Questions 6623371 > Sort_merge_passes 357 > Sort_range 29213 > Sort_rows 11271509 > Sort_scan 231944 > > I want to try and calculate the percentage of sorts that required > more than one pass!! I don't think that sort_merge_passes is increased with every sort. I think there are sorts which don't need a merging pass. Bye, Benjamin. -- [EMAIL PROTECTED] - 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
Sorts
Hi List, If I run the sql query SHOW STATUS how can I tell how many queries required sorting? Here are some of the variables SHOW STATUS returns Questions 6623371 Sort_merge_passes 357 Sort_range 29213 Sort_rows 11271509 Sort_scan 231944 I want to try and calculate the percentage of sorts that required more than one pass!! Any help would be welcome. Thanks Peter Kelly - 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