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