Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Posts
    15

    Unanswered: Partition Question - 2 tables with one-to-many

    Hello,

    Just found out about partitions in MySQL tables and I have a question on how I should build my partition logic.

    I have these two table:

    PHP Code:
    CREATE TABLE `test_tableone`(
           `
    idint(11auto_increment,
           `
    activetinyint(3not null,
           
    primary key(`id`)
    )

    CREATE TABLE `test_tabletwo`(
            `
    idint(11auto_increment,
            `
    table1_idint(11not null,
            `
    miscvarchar(255),
            
    primary key(`id`)

    test_tabletwo can have thousands of records per test_tableone record so I would only like MySQL to scan test_tabletwo where the records pointing to test_tableone is active ( value in column `active` is 1).

    So my Select Query would look something like this...

    PHP Code:
    Select t1.*,t2.* from test_tableone t1test_tabletwo t2 where t1.id t2.tableone_id and t1.active '1'
    Can anyone help me with my partition logic for those two tables?

    Thanks in advance,
    Dyip

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dyip
    Can anyone help me with my partition logic for those two tables?
    tables are partitioned based on values that they contain

    presumably, you were looking for a way to partition table 2 into active and non-active rows?

    this means you'd have to carry the active column redundantly in table 2

    to be honest, i'm not quite sure what would happen when a row in table 1 goes from active to inactive, but you might consider incorporating the active column into table 1's PK and then setting table 2's FK accordingly, with ON UPDATE CASCADE

    furthermore--and i'm not sure about this either--i suspect that the table 2 rows would actually have to be moved from one partition to the other

    on the whole, my advice is not to go this route until you need to

    remember the old saying about "premature optimization"...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    15
    I see...thanks a lot R937! Helped me out again.

    And I will try it out anyway since I suspect tabletwo can grow into hundreds of thousands of records easily...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hundreds of thousands of rows is actually not a big table

    hundreds of millions would be

    you do plan to index your tables properly, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Posts
    15
    I'm on the edge of indexing the tables, my tables are changing constantly (2-3 times every second?). Would indexing it have a negative impact since MySQL have to rebuild the index every time the table changes?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you don't have any indexes now, then i suggest you create one on table2's table1_id column

    inserts will be marginally slower, but your join queries should improve dramatically
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2009
    Posts
    15
    Quote Originally Posted by r937
    inserts will be marginally slower, but your join queries should improve dramatically
    Your emphasis on dramatically got me going! Thanks r937, I really appreciate all your help.

  8. #8
    Join Date
    Jan 2009
    Posts
    15
    Hey r937,

    I created an index for tableone_id on table two and when i tried to do the same join query above, it does not use the index. EXPLAIN shows this...

    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: t1
    type: ref
    possible_keys: PRIMARY,active_idx
    key: active_idx
    key_len: 1
    ref: const
    rows: 2
    Extra:
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: t2
    type: ALL
    possible_keys: id_idx
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 16
    Extra: Using where

    UPDATE: Nevermind, I just needed to rebuild my indexes! Can't believe i forgot to...
    Last edited by dyip; 06-24-09 at 19:37.

Posting Permissions

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