List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 23 1999 12:40am
Subject:Re: I wish I could use LIMIT but....
View as plain text  
On Wed, 1999-09-21 13:49:19 -0700, Chanel wrote:
> What is returned to the user as 50 record labels comes from the
> database as anywhere between 50 and 1000 rows depending on how many
> genres are associated with each label (I loop through the rows
> concatenating the genre information so that it shows up as only one
> table row when presented to the user).
[...]
> What seems like it would be a worse solution (but easier on my
> brain) is to do a SELECT only on the record label table to find out
> the LabelIDs of the next 50 labels and then explicitly loop through
> those 50 doing a SELECT to get all of their associated genres.

Maybe this isn't as bad is might look at first sight, because thus you
dramatically reduce the amount of data to be transferred between the
mySQL server and your client, the queries itself maybe run quite fast
with the necessary indexes in place, and if you send all these queries
over a single connection you even won't have any connection overhead.


A very different solution to your problem:
  Could you change the genre information into a SET field?
  Or at least into a number and each genre being another
  bit (number 1, 2, 4, 8, 16, ...)?

Because then you can use the BIT_OR group function to pick up all
genres with a simple GROUP BY.  On application side you only have to
decode the resulting number into the written genre names ...

Even without such a change of field definition it's possible, though
much more inefficient:
  SELECT LabelID,
         MAKE_SET(
           BIT_OR(
             POW(2,
               FIND_IN_SET(genre,'name1,name2,list,of,all,genres')
             )
           ),
           'name1,name2,list,of,all,genres'
         ) AS genre
  FROM ...
  GROUP BY LabelID;

(I think I found a bug here, as MAKE_SET in itself does work,
 the large BOT_OR term does, too, but in this combination MAKE_SET
 always gives an empty string ...)


> Thanks for all the help everyone's given me!

Hope that helps!

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
I wish I could use LIMIT but....a rancid amoeba22 Sep
  • Re: I wish I could use LIMIT but....Martin Ramsch23 Sep
    • Re: I wish I could use LIMIT but....Martin Ramsch23 Sep