fenway

You need an index on numericid. And don't use DISTINCT.

You want this.

As long as you're in the valid TIMESTAMP range, then yes -- it's not useful for things like birthdays, but then again, those aren't subject to timezones, and shouldn't have been DATETIMEs anyway.

Well, if separate games from teams, you won't need a union.

If you used TIMESTAMP, you'd have do absolutely nothing.

I just mean that if a simple query like this is crazy, then the design needs to be re-worked. I didn't have a specific recommendation, but I can think of a few if you're interested.

Well, yes, that design is far from ideal. You can just UNION the results.

Yes, mysql will cache indexes -- and there are ways to "warm" the cache.

That's not how full-text works. If you want to search across columns, then you need to create a full-text index across those same columns. Those individual column full-text indexes aren't going to be used for the query provided -- there's no "index merge" for full-text.

Then you don't have a FULLTEXT index in place. In fact, I don't even see a single key. Post the SHOW CREATE TABLE.

So what's the problem?

I don't understand the question.

Show us EXPLAIN otuput.

You should be using LEFT JOIN... IS NULL.

Try again.

I've heard nothing but horror stories about them.

Covers: Part 1: Using the MySQL Improved Extension, mysqli Part 2: Using the MySQL Extension, mysql Part 3: Using the PDO Extension With MySQL Driver, pdo_mysql Part 4: Using the MySQL Native Driver for PHP, mysqlnd Tutorial is here.

Well, a scalar subquery, that is.

Hopefully this presentation will stay online at scribd... it's simply fantastic, probably the best I've come across in recent memory. At 220 slides, it's quite lengthy -- but the lessons learned are invaluable, so be sure to read all the way to the end. A MUST READ!!!! EDIT: This year's version of the presentation -- some really great stuff in here, particuarly about hierarchies.

At the 2008 MySQL Conference and Expo, The Pythian Group gave away EXPLAIN cheatsheets (PDF).

This resource covers a very broad range of topics... it's worth a look, though, especially if you're stumbling in the early stages of configuration.

Basically, this blog contains a regularly-updated run-down of MySQL functions, with a short description and a few hints on how to use them; RSS feed here.

Please read the excellent FAQ thread on this issue by our very own wildteen88.

To ensure that your post gets answered as soon as possible, please make sure you've included the following: your MySQL server version -- absolutely required! the raw MySQL statement in question [in a CODE block, and without any PHP variables] any errors that MySQL returns to the client [from mysql_error()] the table structure & column indexes of the relevant tables [via SHOW CREATE TABLE is preferred] the EXPLAIN output for your query, if applicable a clear and concise description of what you want this statement to achieve a description of what it's currently doing that's not to your liking a brief listing of the types of things you've attempted so far If you don't provide any or all of the above, don't be surprised if your post never gets the attention it deserves. --fenway

You'll find a very good list of do's and don'ts written by Matt Kruse here -- highly recommended.