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:

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...

Can anyone help me with my partition logic for those two tables?

Thanks in advance,
Dyip

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"...

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...

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?

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?

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

Your emphasis on dramatically got me going! Thanks r937, I really appreciate all your help.

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...