On 14 Sep 2011, at 19:35, PierreW <[email protected]> wrote:
> Thanks a lot guys for your help. > > I apologize I did not mention it, but I had the index on article_id > unfortunately. > > Fred: I tried your LEFT JOIN method and indeed, when I EXPLAIN it the > second select is of type eq_ref (vs unique_subquery with my method) so > it should be faster. Yet, when I try it on a smaller test DB, the > difference seems only marginal I am afraid. It still goes through all > the comments rows it seems. > > I just made a simple test directly in mySQL on my instance: "select > count(*) from comments" and even that one does not want to return! I > tried to restart mysql: same. I did not know this was even possible. > Count(*) isn't magically fast in innodb - mysql has to do an index scan (versus being able to just read some table metadata for some db types). You might try using show innodb status to see what is going on (there's another thing you can do to see more detailed info about locks being held, but I don't remember off the top of my head. Another option might be to delete the comments before you delete the articles (so inner joining comments & articles with whatever condition on articles you use to determine what to delete). Fred > I realize it is now more a mySQL issue than a Rails one, but just in > case: have you guys ever been confronted to something like that? > > I will try partitioning as well. > > Thanks > Pierre > > > > On Sep 14, 5:13 pm, Jason Stover <[email protected]> wrote: >> On Wed, Sep 14, 2011 at 11:03 AM, Philip Hallstrom <[email protected]> wrote: >> >>> Another option if you're willing to stick with mysql is to use their ???? >>> (can't remember the name) feature. >>> It lets you create what appears to be a normal table, but it actually >>> splits it up into multiple tables based >>> on one of the columns -- in your case... the date. So when you remove the >>> old entries you're not touching >>> the "latest table". At least if I'm remembering things right. >> >>> -philip >> >> I think you're talking about partitioning: >> >> http://dev.mysql.com/doc/refman/5.1/en/partitioning.html >> >> -J > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Talk" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
