MySQL - Advice for using two tables, one each for open and closed records? I'm migrating an app from ASP/MSSQL Server 2008 to PHP/MySQL. In the previous app, I had an object using multiple joins and advanced search queries with hundreds of thousands of records. I split the records into 'table1' and 'table1_closed' so I could keep the open-records table speedy. This worked in speeding up the queries by a tremendous amount (the closed table is orders of magnitude slower), but resulted in some weird workarounds to allow closed records to be moved back to the open records table (manual locks and such). Needless to say it’s hacky and buggy. 

My question is, in MySQL, does anyone see the need to keep doing it this way, or would you merge the two tables back to one and just use an indexed field to differentiate? Is MySQL more adept at advanced queries with joins on very large recordsets?

From comment:

Select  t1.*, t2.*, t3.*
    from  properties t1
    Join  inspection_groups t2  ON t1.id = t2.property_id
    Join  inspection_types t3  ON t2.inspection_type_id = t3.id
    where  (      t1.address = 'x'
              or  t1.city = 'x'
           )
      And  t2.inspection_type_id = 1
      And  t2.is_active = 1
    ORDER BY  t2.created_at DESC
    LIMIT  1,12
Add a column to distinguish open/closed.  It should be small (TINYINT or ENUM).

Then pick one:

Plan A:

Then have most indexes be composite and start with that column.  These indexes will make it efficient to find just opened (or just closed) rows.

If you also need to find both opened and closed, then also have index(es) that do not start with the new flag.

Plan B:

PARTITION by the new column.