Fast indexing for chat SQL table

I'm building a MySQL table that will hold all the data for many one-on-one chat sessions开发者_开发知识库.

Are insertions to an indexed table still slow when insertions are always sequential?

With a chat system, I expect frequent insertions as well as frequent searches.

Each chat session will keep track of it's own session ID, and the latest post ID. So to get new posts I could run a query like this:

SELECT * FROM chat WHERE sessionID = xxxx AND postID > xxx

Is there a way to have multi-dimensional indexing? It seems like the fastest way to index would be primarily by sessionID, then by postID.

---

You can use a multi-column index for that query, as suggested by others as well.

That is, however, not a multi-dimensional index, it is just putting two columns on the same dimension. Think of a printed telephone directory ordered by last name, first name. Two columns, but one dimension only.

The important aspect in your particular query is that you have one range condition. For best performance, it is important to put the columns with the equality conditions first, and the range condition last into the index, like suggested by the other commenters. You don't need anything multi-dimensional for that.

If you are having more than one range-conditions, you would really need (want) a multi-dimensional index.

More about that: http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/greater-less-between-tuning-sql-access-filter-predicates

---

You can create an index across the two columns with something like this.

    ALTER TABLE chat ADD INDEX chat_sessionID_postID_idx (sessionID ASC, postID ASC);