Scale Large MYSQL Table -

I have a table that is growing very fast, currently it has 47000000+ rows

Even very simple questions such as 46 take on second time.

  Selection id, userId, visitorId, user_views date range 20000000, 1;  

The table structure is:

  field type tap key default additional id int (11) unsigned PRI tap auto_increment userId int (11) unsigned multi-zero Visitor ID Int (11) No Multi Zero Date Date Time MUL NULL  

Already running with application 1 Master and 6 slaves. Can not tolerate more examples

There is a BTRE index on ID

Is there any way to make it faster?

Thanks

Firstly, consider using different storage approaches should do. Depending on your usage cases, a relational database may not be the best option, if 99% of all options are typing in the table but not updating existing records (which suggests your column names), then one The nosql database can perform better.

The second 200,000,000 rows without any specific order criteria, of course, on an index) are open to DBMS to implement an arbitrary order, which can be sub-arbitrary.

I do not know the MySQL-internal optimization mechanism, but the LIMIT is only after applied the whole result has been created, which means that the entire table loads in your memory has gone. Please try to reduce the size of the result set that limits before using the WHERE statement.