fenway

You're telling me the database is wrong. I'm telling you to show me the 3 records from each table that match on strMessage.

Again, * is garbage with GROUP BY -- don't do that.

I don't need to see you code, I need to see you data.

That doesn't exist. Set theory doesn't allow for "one of".Which one would you want? Based on that, you can use a self-join to limit that result to a single record, and then you're golden.

There's nothing unique about that.

You almost never need to physically reordering the records -- you're not supposed to even know how they're being stored. There are rare circumstances for historical tables which are huge where this matters -- like 0.01% of the time.

SELECT * FROM tblDocuments INNER JOIN theOtherTable ON ( theOtherTable.strMessage = tblDocuments.strMessage ) ORDER BY tblDocuments.dtAdded DESC"

Or this.

You can't concatenate queries like that -- and use code tags next time.

Different structure for a primary vs backup table is just a maintenance nightmare. Again, I don't see why you need record-level granularity for really old data. MERGE tables are your friend -- no need to delete anything -- I never do. In terms of downtime, I already suggested that you make a shadow copy of the table, and do the maintenance there.

Well, virtual page design is a subject for the php help forum. But if you want to store multiple categories for a given link, you'll need another table.

Your DB design is already flawed -- you have tribbles.

I highly doubt you're running real-time reports on 53M records. If you are, you have a different problem. You can trivially make summary tables of the past, and just keep the last N months in the "live" table. Or you can use MERGE tables, one per year, for example. Or you can use partitioning if you're using newer versions of MySQL. What are you indexing? Besides, you can repair off-line with MyISAM.

A few things. First, as of MySQL 5, you can make VARCHARs larger than 255 characters. And you can't be possible be using your user agent strings for anything signficant, a truncation at 500 would be reasonable too (for 1% of the time that happens anyway). Second, URLs aren't much different -- seriously, how long are your URLs? Not more than 1000 characters even if you tried really, really hard -- unless you have crazy GETs. Third, TEXT is very, very, very evil -- don't use it for simple strings. Fourth, don't pull them out.

This topic has been moved to Other RDBMS and SQL dialects. http://www.phpfreaks.com/forums/index.php?topic=347986.0

You know, you don't have to ask us when mysql tells you.

If it's a number, store it as INT.

Then it's an INSERT -- not an UPDATE. Just store the date alongside each recod.

Verification question? That's not a mysql thing.

Execute "SHOW TABLES" and see.

If you didn't back up your database, then no.

No one should be hosting with a provider that disables the standard storage as of 5.5. You can't have real transactions with MyISAM under any reasonable amount of load.

This topic has been moved to PHP Coding Help. http://www.phpfreaks.com/forums/index.php?topic=347849.0

Sounds like you need a summary table to cache the calculation.

I still prefer a WHERE clause and a GROUP BY, just so it won't be useless later on.