Comment

For example, this query: SELECT t FROM p GROUP BY t don't use the index. How can I correct that ?

The default index for a MEMORY table is a HASH index they are very fast when finding const values. But you can't use them for ranges or numerical comparison (sorting).
Which is what a GROUP BY needs to do.

So when you create the table you must define that the index should be a BTREE.

Comment

SELECT id FROM users WHERE ip='xxx.xxx.xxx.xxx' AND en='yes' AND sta='confirmed' LIMIT 1SELECT id, pl, nl, cl FROM users WHERE ip='xxx.xxx.xxx.xxx' AND en = 'yes' ORDER BY la_acc DESC LIMIT 1

You can create a combined index on users(ip, en, sta).
Since (ip,en) are the first columns in this index mysql will be able to use it for the second query also.
And by creating this index mysql will also be able to use this index instead of your ip index.
So you can drop the ip index after you have created this.

To solve this query:

SELECT * FROM users WHERE id = 12 AND en='yes' AND sta = 'confirmed'

You should create a index on (id, en, sta).
And the same applies here for redundancy with indexes.
If you have an index on only the first column you can remove that since mysql can use this index instead.

Comment

On the other hand, I have something strange with my HEAP table `p`. It seems that now this table is limited to 99270 record, but I'm sure this should be higher. I really don't know why this limitation. Have you an idea ? It's like that since I have modified the indexes with USING BTREE.

Thanks for your help...you're helping me a lot.

Edit

Sorry I found the solution: max_heap_table_size - Google is my friend too