fenway

I will say it again -- you want the index on the column that's the most selective... I assume that by not addressing my valid=3 question you mean to say that this would match many more records. If there are just 100 rows, and valid is indeed not selective, then it's unlikely that mysql would choose to use the index anyway, since a table scan might be more efficient.

You can't call your table "database".

It's here.

You could get mysql to determine this for you, but that's much more work when any type of validation would prevent this issue on the server side.

Sounds like a LEFT JOIN...IS NULL.

Obviously, they can't be normalized the same way... size of a record is really in issue here, determines number held in various buffers, which determines how fast mysql can perform many operations -- which is why it's bad to have BLOB/TEXT fields mixed in with other data types in general.

RELEASE was not a reserved keyword in 4.1, but is in 5.0 -- and if your provided blindly upgraded your DB without telling you, I'd be very concerned.

Could you give specific examples and table structures/

Which variable is this?

You should look at full-text indexing,too.

I refer you to this refman page.

That's becuase you can't mix ASC/DESC -- you can either go up or down, but not both.

I guess a clarification is in order... You are, of course, correct -- I should have specified that I was talking about the situation where the where clause contains references to the left join-ed tables. That is, if you query on t2.something = 'something', and you never check IS NULL, then gathering all those extra rows is just more expensive, since the where clause throws them out anyway. It would be more efficient to use an inner join to discard them at the earlier stage, especially if there are subsequent tables to join! Otherwise, what you described for postgresql will occur in mysql too, and you should be able to demonstrate that with EXPLAIN.

The question is how many records does valid = 3 match for all names? Furthermore, you do get some benefit from a covering index if you decide to order by these columns, or just return part of the multi-column key vs *.

It's just a self join to the original table using parentID as the join condition.

DB normalization should always be at the top of the list.

Depends what you mean by "both" -- you mean 2 separate indexes or one covering index? In general, you want the index on the column with the most selectivity... sometimes that's one (e.g. a name), but if it's just two flags each match a lot of records but together limit them, it's two.

You can't use an index to find where a value *isn't*... just because you have an index of values present, you still need to check each one to see if it doesn't match your value .... making the index useless -- which is why it's doing a full table scan. I meant something like this: select a.id from adoptables a left join rarecandylimiter r on ( a.id = r.pokeid AND r.ip= inet_aton('121.94.235.68') ) where r.pokeid IS NULL That is, you find all rows that match your ip -- then the ones that don't will be NULLed out by the left join -- making them easy to find with IS NULL. Obviously, once this works, you can add whatever order/limit you desire.

Let's talk about the second query with the left join... that limit is in a very strange place. Let's also drop the distinct/order by for a second. That != is going to cause problems. Also, you said you're storing ip as an int, yet you're querying it as a string? What you need is a left join with the ip as an equality in the on clause, then IS NULL in the where clause. But I need to clarify the earlier points first.

What I'm suggesting is that you don't need the duplicates, you can simple "archive" the old records to the old table (probably even with the ARCHIVE engine)... no need for dupes.

That's a php issue... just keep track of the last order number & bill to values, and don't output anything if they're in the same as the current one; otherwise, update the last with the current, and continue.

Can you not archive some of this information in another table? You could always use a merge table for complete historical reporting if need be, but the "latest" report wouldn't need to have anywhere near this number of records.

Hopefully you're not outputting the recordset directly...

Yes, but depending on which version of mysql you're using and what indexes your have, this may or may not be more efficient.

I don't understand what you're describing... give concrete examples.