fenway

You need to consult some basic tutorials on mysql_query() usage.

First, don't use all caps. Second, try actually checking the error. Third, consult the refman for proper select syntax -- you can't put the where clause after GROUP BY.

utf8_bin = binary, not text. That's all.

You're comparing a field value to itself?!?!

The number of records has nothing to do with which records are considered -- so I don't understand your question.

Stop -- don't ever use *, throw away all of the data, and just get a count. That's what COUNT(*) is for.

What's not working?

If you want a great read, see here; but that's a lot to digest. Ignoring efficiency for a moment, first start by writing a simple query -- with a gROUP BY -- to get the most recent uid for each topic, by date.

I mean those queries are fairly rudimentary, and I don't see how they couldn't work. So I suspect there's a php issue lurking.

So many things. First, you need a left join if you want to find non-matching rows. Second, a count(*) without a group by will always return just one row. Third, DISTINCT is not a function.

Try sphinx -- horribly annoying to set up and configure, and the documentation is very dry, but it's super-fast.

Well, the issue is that there are still 40K rows to examine. Don't you necessarily have the siteID in the users table? If so, skip the join to the site table at the beginning.

You'll have to check both conditions.

Sorry, I don't follow.

TLDR... sounds like you have logic issues, not just mysql problems.

Then you haven't looked enough. DELETE u.*, c.*, c2.*, t.* FROM users u LEFT JOIN chats c ON c.userId = $row['id'] LEFT JOIN chats c2 ON c.randomUserId = $row['id'] LEFT JOIN typing t ON t.id = $row['id'] WHERE u.userId = $row['id']

Not sure about the details, but I'm sure they've published extensively. But I'll tell you what I tell everyone else -- you're not going to get to craiglist's size using information you obtain on a free forum. So I wouldn't worry about that limitation until you get there. As for mysql crashing & performance, everything's interconnected -- but table size is unlikely the root cause.

Millions? That's nothing -- see here.

I'm confused, reading over this thread -- don't you start with a bookmark? Why the left join?

Forget about getting the rows back to php -- that's a different issue entirely. We need to focus on getting back the distinct list. And I have a hard time believing that it takes the same time to send the rows.

Easier to handle this in php -- but meaningless without an order by clause.

It just means to have to scan O**2 rows.

Of course, that's horrible for index usage.

If you have a php problem, try a php solution -- dump $row.

Don't even dream of denormalizing the table -- that's a bad crutch. How fast it is without the GROUP BY?