Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    2

    Unanswered: Virtual memory usage on joining tables

    As I understand it, every table which mysql has to search on for a query has to be loaded into virtual memory first. However, if a query joins on a single table more than once, will that table be loaded up once for EVERY reference in the query, or just once for the whole lot?

    eeew that was badly worded.. to put it another way, if I join against a table 3 times with 3 different aliases, will mysql create 3 images of that table (1 for each alias) in memory, or is it smart enough just to use one.

    I'm just trying to ascertain what the possible downsides are of joining several times on a single table in a single query - I realise this is totally out of context, but would anyone recommend breaking such an effort into 3 queries instead? My general rule of thumb is to do as much as possible in a single hit..

    thoughts?
    christo

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Virtual memory usage on joining tables

    Originally posted by christo
    As I understand it, every table which mysql has to search on for a query has to be loaded into virtual memory first. However, if a query joins on a single table more than once, will that table be loaded up once for EVERY reference in the query, or just once for the whole lot?

    eeew that was badly worded.. to put it another way, if I join against a table 3 times with 3 different aliases, will mysql create 3 images of that table (1 for each alias) in memory, or is it smart enough just to use one.

    I'm just trying to ascertain what the possible downsides are of joining several times on a single table in a single query - I realise this is totally out of context, but would anyone recommend breaking such an effort into 3 queries instead? My general rule of thumb is to do as much as possible in a single hit..

    thoughts?
    christo
    Well, I wouldn't worry too much about that right now... design your tables, normalize, design indexes and see how your app reacts... The optimize for performance...

    To answer your question, it all depends on how you join and what you join... It could just load the indexes, some indexes, use a temporary table, use memory, etc... To really know what's happening, you'll have to EXPLAIN the query to know how MySQL accesses the data.

  3. #3
    Join Date
    Sep 2003
    Location
    UK
    Posts
    2

    thanks

    Thanks - that's good advice. I admit, I'd actually forgotten to use the EXPLAIN statement, so I'll have a fiddle with that.

    The truth is, I have been nervous about a query I have which makes 11 joins on 7 tables. It's fine at the moment, but I'm thinking hard about the future when some of those tables could well have a couple million records. Would you agree with the philosophy of getting as much data out in as few queries as possible, rather than putting queries within loops, or breaking them into chunks?

    many thanks
    <possibly over cautious>
    christo

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: thanks

    Originally posted by christo
    Thanks - that's good advice. I admit, I'd actually forgotten to use the EXPLAIN statement, so I'll have a fiddle with that.

    The truth is, I have been nervous about a query I have which makes 11 joins on 7 tables. It's fine at the moment, but I'm thinking hard about the future when some of those tables could well have a couple million records. Would you agree with the philosophy of getting as much data out in as few queries as possible, rather than putting queries within loops, or breaking them into chunks?

    many thanks
    <possibly over cautious>
    christo
    Yep. Let the database server do the work... That way, if your data grows up to a point where the app becomes slow, you'll only have to upgrade one server, not all the clients! And 11 joins, when tables are properly indexed, is not so uncommon... As I said earlier, plan and test for the future and THEN, if there's no other way to get decent speed, optimize and de-normalize if necessary.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •