1

I am using Ruby on Rails 3.2.2 and MySQL. From the logger I know that my system is running an SQL query like the following:

SELECT `articles`.* FROM `articles` WHERE (articles IN (1,2,...,<A_LOT_OF_ID_VALUES>))

From the source code I know that the <A_LOT_OF_ID_VALUES> value growns as the number of rows / records present in the related articles database table... should I be aware of that "directly proportional growing"?

Bonus: "Practically" speaking, should I be aware of too "long" text outputs (for example, text outputs as like the previously mentioned "1,2,...,100,101,...,<A_LOT_OF_ID_VALUES>" string) in log files?

user12882
  • 4,702
  • 9
  • 39
  • 54
  • 2
    Curious, what operation generates such query? – zerkms Jun 19 '12 at 23:08
  • @zerkms - I read [this question](http://stackoverflow.com/questions/11074008/what-is-a-common-approach-to-scope-records-by-those-that-an-user-can-read) and I am trying to implement something like that. The code mentioned in the linked question causes this behavior. – user12882 Jun 19 '12 at 23:17
  • I'm sure it can be done with trivial join – zerkms Jun 19 '12 at 23:35
  • @zerkms - I cannot think how to make that "with trivial joins"... it is impossible!!! – user12882 Jun 19 '12 at 23:46
  • if you have "readable by user` somewhere in DB - then just join it with the `articles` table – zerkms Jun 19 '12 at 23:49
  • @zerkms - As for my case (and as it is explained and I assume for the case in the [linked question](http://stackoverflow.com/questions/11074008/what-is-a-common-approach-to-scope-records-by-those-that-an-user-can-read)) the `readable_by_user` method has "internal" calls to other methods "that are not easy to translate into an SQL query". So, it is not "trivial" (at least, for me) to *join* tables. – user12882 Jun 19 '12 at 23:54
  • so if you're sure you have no alternatives - what this question is about? You need exactly that code - you use it. You don't have choice – zerkms Jun 20 '12 at 00:01
  • 2
    @zerkms - Referring to the current question: **should I be aware of that "directly proportional growing"?** *"Practically" speaking, should I be aware of too "long" text outputs (for example, text outputs as like the previously mentioned "1,2,...,100,101,...," string) in log files?* It is just a "simple" question, but you are *Curious* and I try to satisfy you curiousity... – user12882 Jun 20 '12 at 00:02
  • even if you should be aware - what would it change? You're saying that you don't have other options. – zerkms Jun 20 '12 at 00:05
  • @zerkms - It may / would change the system *performance* over the time (by assuming the `articles` database table is constantly growing). – user12882 Jun 20 '12 at 00:08
  • yes, it will. And the solution - is to use joins :-) If you don't want to rewrite it using joins - you need to accept that sooner or later this solution will be inacceptable slow – zerkms Jun 20 '12 at 00:09
  • @zerkms - Maybe your "answer" in your latter comment fit better as an answer to the [linked question](http://stackoverflow.com/questions/11074008/what-is-a-common-approach-to-scope-records-by-those-that-an-user-can-read) and not here. Thank you, anyway. – user12882 Jun 20 '12 at 00:11

1 Answers1

0

Yes you shoud be aware of growing list of ids ..... it is ok for a bit, but once you go to hundreds or thousands then the query will get really slow. The alternate is to use a subquery that selects the ids that you require.

Naval
  • 344
  • 2
  • 7
  • OK, but in my case I am already using the sub-query you mentioned and, in order to retrieve `ids` that I require, it *must* run a lot of methods (in my specific case, it must run a method on each object related to each retrieved `id`). – user12882 Jun 20 '12 at 03:35
  • there has to be an easier way.... if u r running a method on each object to retrieve each id ... then my guess is that there is some logical flaw ... we need to look into the conditions that prompted you towards this direction – Naval Jun 20 '12 at 05:06
  • What conditions "that prompted me towards this direction" you need to look? at least, conditions relating to what? – user12882 Jun 20 '12 at 07:12
  • i was talking about the prerequisites ... conditions relating to you creating methods for retrieving each id object. Conditions that decided your approach towards the solution that you took – Naval Jun 21 '12 at 03:06