2

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 
0

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.

  • I'll have to look up partitioning on a column, that's a new one for me. Can you combine these two plans or is it one or the other? – Dan S Oct 31 at 16:59
  • "Partition pruning" will pick the partition; the rest of the WHERE can use an index to find the rows in the table. Provide a sample SELECT; I'll help you with the CREATE TABLE and index(es). – Rick James Nov 1 at 15:29
  • It will be a while before I'm writing queries for this since I'm at the very beginning of the database design at this point. But here is a simple example (this will morph based upon user input, and I will also be creating lookup objects rather than creating too many joins within the query): 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 – Dan S Nov 1 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.