Re: How to get acceptable/repeatable performance on huge table

[Rick James]

> * How could "A secondary INDEX(aid, userUid) (or even simply INDEX(aid), which happens to be identical in InnoDB)):" be identical?

A secondary key in InnoDB implicitly contains all the fields of the PRIMARY KEY. That is how it can locate the row. A lookup by secondary key first drills down the BTree of the secondary key, there it finds the primary key for the row(s), then it looks up the row(s) via the PK.

> * Is it just to add an index on the Slave Db Table? It won't mess up the replication in any way?

The ALTER TABLE .. ADD INDEX .. you on the Master will be replicated to the Slave(s), where it will be reexecuted.

If you only add the index to the Slave, but not the Master, that is ok. However, note that all INSERTs that are done on the Master must be done on the Slave(s) also. So, my warnings about 'random' I/O would still apply to the Slave. However, with 47G of buffer_pool (on 64GB machine), and an estimated 8GB for the new INDEX, means that you should not have to worry about becoming I/O bound.

> the other query where I have the Uid and need all rows for that Uid.

WHERE UID=... -- without mentioning other fields in the WHERE:
This begs for an index (or PK) _beginning_ with UID.



DEMAND does nothing (other than cause overhead) until you say SELECT SQL_CACHE...