Re: How to optimize ugly order by?
On 24-Mar-2004 Henrik Schröder wrote: > No, I did not choose the sort order. No, I can not change the values > used. > Yes, it has to be this sort order. :-) > Best would be to use ENUM( '1','2','0') ... But otherwise -- (membershiptype % 2) DESC, membershiptype DESC -- or -- ELT(membershiptype + 1, '2', '0', '1') ASC -- or -- MAKE_SET(membershiptype, 'a','b') DESC Regards -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
Can you do: SELECT IF(membershiptype = 0, 3, membershiptype) AS s1 FROM... ORDER BY s1 Shouldn't that work? Basically if mtype is 0 return 3 otherwise return the membership type... and sort by that field. lemme know if that works or if I'm dead wrong ;) Henrik Schröder wrote: Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with the calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
Henrik Schröder wrote:
Hi all,
I have a table of users which contains a lot of information, and also a
column called membershiptype which can either be 0, 1, or 2. This table is
used a lot in various searches, and all results should be ordered by
membership type, but the order should not be 0-1-2, but instead 1-2-0.
Currently, this is achieved like this:
SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> 2) ASC,
login ASC
...which is rather ugly, and forces MySQL to create a temp table with the
calculated expressions and then re-sort the result using these. Since this
query is used a lot, it would be nice if I could get rid of this. I'm
completely stumped. Any ideas?
No, I did not choose the sort order. No, I can not change the values used.
Yes, it has to be this sort order. :-)
/Henrik Schröder
I would probably fix this by changing your membershiptype column to an ENUM,
like this:
ALTER TABLE yourtable MODIFY membershiptype ENUM('1', '2', '0');
ENUM columns return what's in the quotes in string context, but return the
position number in numeric context. So,
SELECT * FROM yourtable ORDER BY membershiptype;
would display membershiptype as 1, 2, or 0, but sort them in the order you
want (as '1' => 1, '2' => 2, '0' => 3). Also, as you would now be using the
column itself, rather than a function of the column, it is possible for an
index to help.
WARNING: Note, however, that if you do this, you will have to change any
code that sets membershiptype (INSERT or UPDATE) to add quotes, at least in
the case of 0. That is,
INSERT INTO yourtable (membershiptype) VALUES ('0');
instead of
INSERT INTO yourtable (membershiptype) VALUES (0);
This is because, if you assign a number, mysql assumes you want the value in
that position, and position 0 is the special empty string error value. (The
same will happen for 1 and 2, but they're in the "right" place.)
From your description, this may not apply to your case, but if the values
1, 2, and 0 code for something, you could create an ENUM column with the
descriptive text in each position, ordered as you want. For example, if 1,
2, and 0 mean 'one year', 'two year', and 'expired', then you could create a
new column with ENUM('one year', 'two year', 'expired'), set the new
column's values using membershiptype, drop membershiptype, and rename the
new column as membershiptype. The payoff would be that you could eliminate
code to translate 1, 2, and 0 into text.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
I assume you are just trying to make things more readable. As far as I know, MySQL does not support custom sort orders, except when a field is an enum or a set. Even then, that's not a custom order since your still sorting on the underlying values. You could do something like this for readability: SELECT ...,if(membershiptype=0,3,membershiptype) as sortby ... ORDER BY sortby This just creates a calculation column in the select that changes the value 0 to 3 and then you use the calculation column for the sort. I doubt this will speed things up, but I think it's easier to read. You may want to look into what's involved in using enum or set for the field for the future. On Mar 24, 2004, at 9:47 AM, Henrik Schröder wrote: Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with the calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- 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]
RE: How to optimize ugly order by?
You are correct. On Wed, 24 Mar 2004, Henrik Schröder wrote: > I don't think it does right now, but that can always be taken care of later. > > Assuming that the query uses one index that contains all the where-columns > and the orderby-columns, having an ORDER BY that sorts on expressions is > still the biggest problem, right? > > > /Henrik > > -Original Message- > From: Victor Pendleton [mailto:[EMAIL PROTECTED] > Sent: den 24 mars 2004 16:22 > To: [EMAIL PROTECTED]; Henrik Schröder; '[EMAIL PROTECTED]' > Subject: Re: How to optimize ugly order by? > > > Let me rephrase my statement. Does the index being used include the order by > columns? If not, the optimizer will not be able to perform both row > selection and row ordering in the same pass. > > On Wed, 24 Mar 2004, Victor Pendleton wrote: > > Does the where clause contain the order by columns? If not, then you > > will have a filesort no matter what. > > > > On Wed, 24 Mar 2004, Henrik Schröder wrote: > > > Hi all, > > > > > > I have a table of users which contains a lot of information, and > > > also a column called membershiptype which can either be 0, 1, or 2. > > > This table is used a lot in various searches, and all results should > > > be ordered by membership type, but the order should not be 0-1-2, > > > but instead 1-2-0. Currently, this is achieved like this: > > > > > > SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> 2) > > > ASC, login ASC > > > > > > ...which is rather ugly, and forces MySQL to create a temp table > > > with tthe calculated expressions and then re-sort the result using > > > these. Since this query is used a lot, it would be nice if I could > > > get rid of this. I'm completely stumped. Any ideas? > > > > > > No, I did not choose the sort order. No, I can not change the values > > > used. Yes, it has to be this sort order. :-) > > > > > > > > > /Henrik Schröder > > > > > > -- > > > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to optimize ugly order by?
Thanks, I'll have to try that to see if it's faster. Adding another column as someone else suggested is too complex in comparison because it forces a bigger code change, and I don't really want that. Changing one SELECT-statement is enough... /Henrik -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: den 24 mars 2004 16:12 To: [EMAIL PROTECTED] Subject: Re: How to optimize ugly order by? From: "Henrik Schröder" <[EMAIL PROTECTED]> > used a lot in various searches, and all results should be ordered by > membership type, but the order should not be 0-1-2, but instead 1-2-0. One thing you could do (not very neat IMHO) is to JOIN this query with a table that contains membershiptype and a sortorder. Then you can ORDER BY the sortorder of that table... Regards, Jigal. -- 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]
RE: How to optimize ugly order by?
I don't think it does right now, but that can always be taken care of later. Assuming that the query uses one index that contains all the where-columns and the orderby-columns, having an ORDER BY that sorts on expressions is still the biggest problem, right? /Henrik -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: den 24 mars 2004 16:22 To: [EMAIL PROTECTED]; Henrik Schröder; '[EMAIL PROTECTED]' Subject: Re: How to optimize ugly order by? Let me rephrase my statement. Does the index being used include the order by columns? If not, the optimizer will not be able to perform both row selection and row ordering in the same pass. On Wed, 24 Mar 2004, Victor Pendleton wrote: > Does the where clause contain the order by columns? If not, then you > will have a filesort no matter what. > > On Wed, 24 Mar 2004, Henrik Schröder wrote: > > Hi all, > > > > I have a table of users which contains a lot of information, and > > also a column called membershiptype which can either be 0, 1, or 2. > > This table is used a lot in various searches, and all results should > > be ordered by membership type, but the order should not be 0-1-2, > > but instead 1-2-0. Currently, this is achieved like this: > > > > SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> 2) > > ASC, login ASC > > > > ...which is rather ugly, and forces MySQL to create a temp table > > with tthe calculated expressions and then re-sort the result using > > these. Since this query is used a lot, it would be nice if I could > > get rid of this. I'm completely stumped. Any ideas? > > > > No, I did not choose the sort order. No, I can not change the values > > used. Yes, it has to be this sort order. :-) > > > > > > /Henrik Schröder > > > > -- > > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
On Wed, 24 Mar 2004 15:47:46 +0100, Henrik Schröder wrote: > I have a table of users which contains a lot of information, and > also a column called membershiptype which can either be 0, 1, or 2. > This table is used a lot in various searches, and all results > should be ordered by membership type, but the order should not be 0- > 1-2, but instead 1-2-0. Currently, this is achieved like this: > > SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> > 2) ASC, login ASC > > ...which is rather ugly, and forces MySQL to create a temp table > with the calculated expressions and then re-sort the result using > these. Since this query is used a lot, it would be nice if I could > get rid of this. I'm completely stumped. Any ideas? > > No, I did not choose the sort order. No, I can not change the > values used. Yes, it has to be this sort order. :-) There may be a cleaner method but could you add a field that's used for sorting so that the sorted field has a value of 0 where membership type is 1, 1 where it is 2, and 2 where it is 0? You could then ORDER by this field but use the value from membershiptype for your other purposes. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
Let me rephrase my statement. Does the index being used include the order by columns? If not, the optimizer will not be able to perform both row selection and row ordering in the same pass. On Wed, 24 Mar 2004, Victor Pendleton wrote: > Does the where clause contain the order by columns? If not, then you will have > a filesort no matter what. > > On Wed, 24 Mar 2004, Henrik Schröder wrote: > > Hi all, > > > > I have a table of users which contains a lot of information, and also a > > column called membershiptype which can either be 0, 1, or 2. This table is > > used a lot in various searches, and all results should be ordered by > > membership type, but the order should not be 0-1-2, but instead 1-2-0. > > Currently, this is achieved like this: > > > > SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> 2) ASC, > > login ASC > > > > ...which is rather ugly, and forces MySQL to create a temp table with tthe > > calculated expressions and then re-sort the result using these. Since this > > query is used a lot, it would be nice if I could get rid of this. I'm > > completely stumped. Any ideas? > > > > No, I did not choose the sort order. No, I can not change the values used. > > Yes, it has to be this sort order. :-) > > > > > > /Henrik Schröder > > > > -- > > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
From: "Henrik Schröder" <[EMAIL PROTECTED]> > used a lot in various searches, and all results should be ordered by > membership type, but the order should not be 0-1-2, but instead 1-2-0. One thing you could do (not very neat IMHO) is to JOIN this query with a table that contains membershiptype and a sortorder. Then you can ORDER BY the sortorder of that table... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to optimize ugly order by?
Does the where clause contain the order by columns? If not, then you will have a filesort no matter what. On Wed, 24 Mar 2004, Henrik Schröder wrote: > Hi all, > > I have a table of users which contains a lot of information, and also a > column called membershiptype which can either be 0, 1, or 2. This table is > used a lot in various searches, and all results should be ordered by > membership type, but the order should not be 0-1-2, but instead 1-2-0. > Currently, this is achieved like this: > > SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> 2) ASC, > login ASC > > ...which is rather ugly, and forces MySQL to create a temp table with tthe > calculated expressions and then re-sort the result using these. Since this > query is used a lot, it would be nice if I could get rid of this. I'm > completely stumped. Any ideas? > > No, I did not choose the sort order. No, I can not change the values used. > Yes, it has to be this sort order. :-) > > > /Henrik Schröder > > -- > 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]
