Don't use NULL values: a query with WHERE col IS NULL or IS NOT NULL, either syntax will be very slow for lookup - best to default the values.
Do not use LEFT JOIN t ON t.col1 = t2.col1 OR t.col2 = t2.col3
Using OR during LEFT JOIN is almost always going to move at a glacial pace.
We all know about indexing, but having efficient lookups and relationships that are indexed in a reliable way that doesn't require data duplication is only half of the battle - the other half is making sure you do not write boneheaded queries - especially when running against massive databases.
As others said, segment out the data - I use rolling timers to do "garbage" cleanup. We typically track every single micro-thing both the server and the user does - 6 months from now, it doesn't really matter what time you logged in and out of the platform.
Always scan your DB for tables that are taking up a lot of space - you would be surprised how an errant column of data you don't need can balloon your database size.
Here comes the controversial advice: I found that while using PDO in PHP, binding too many parameters can be incredibly slow. If you are absolutely sure about the source of the data and have sanitized it, there doesn't seem to be a reachable "query size limit" - if I try to bind 3,000 parameters, my sqld is going to shit the bed - but a 40,000 character "query" flys through at the speed of light. YMMV and this may be the worst advice you read on the internet for the rest of the week, but if you absolutely need to juice the last bit of performance out of a query with PDO, test the speed difference between binding versus just shoving a huge long query through. :)
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
A few I didn't see:
Don't use NULL values: a query with WHERE col IS NULL or IS NOT NULL, either syntax will be very slow for lookup - best to default the values.
Do not use LEFT JOIN t ON t.col1 = t2.col1 OR t.col2 = t2.col3
Using OR during LEFT JOIN is almost always going to move at a glacial pace.
We all know about indexing, but having efficient lookups and relationships that are indexed in a reliable way that doesn't require data duplication is only half of the battle - the other half is making sure you do not write boneheaded queries - especially when running against massive databases.
As others said, segment out the data - I use rolling timers to do "garbage" cleanup. We typically track every single micro-thing both the server and the user does - 6 months from now, it doesn't really matter what time you logged in and out of the platform.
Always scan your DB for tables that are taking up a lot of space - you would be surprised how an errant column of data you don't need can balloon your database size.
Here comes the controversial advice: I found that while using PDO in PHP, binding too many parameters can be incredibly slow. If you are absolutely sure about the source of the data and have sanitized it, there doesn't seem to be a reachable "query size limit" - if I try to bind 3,000 parameters, my sqld is going to shit the bed - but a 40,000 character "query" flys through at the speed of light. YMMV and this may be the worst advice you read on the internet for the rest of the week, but if you absolutely need to juice the last bit of performance out of a query with PDO, test the speed difference between binding versus just shoving a huge long query through. :)