I have a database table with 4.5 million rows running locally on a 4 core LINUX MySQL
Code:
mysql> select count(*) from xvideos; +----------+
| count(*) |
+----------+
| 4543335 |
+----------+
1 row in set (0.00 sec)
mysql> ANALYZE TABLE xvideos;
+-------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| seo.xvideos | analyze | status | OK |
+-------------+---------+----------+----------+
1 row in set (0.01 sec)
I am using a terminal not an interpreter code. Who wrote the code? I think the code may be the issue?
**added
Code:
mysql> SHOW CREATE TABLE xvideos;
.....
KEY `category` (`category`(10)),
FULLTEXT KEY `idx_1` (`category`),
FULLTEXT KEY `idx_2` (`tags`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
See whats there ... and what you are indexing.
Your alternatives are to fix whatever the issues are or to just buy more CPU time. In other words bad code can be remedied with more hardware -- not really recommended but... If that ANALYZE locks the tables too long your users will get no return from the db. So, if it's fast and dirty, just pay for the spike in CPU and move on.