Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search

Question

Followings are what I need to do on my project:

1- If a search term is new and unique, then add the search term

2- Show lastest searched 1000 search terms (I don't need to store dates of all search terms)

3- Show 20 similar search terms for a search term

4- If an old search term seaches again, then update view count of the search term.

http://www.ptf.com/tai/tai+ve+dot+kich/ and some other big sites uses first word of search terms for performance I believe. But I am not sure how to apply that structure.

Also, you can get an idea what I'm doing from the link.

This is the current structure and I know it is pretty bad:

mysql> use article; SHOW TABLE STATUS LIKE 'searches';
Database changed
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+--------------
-------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time| Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+--------------
-------+---------------------+---------------------+-----------------+----------+----------------+---------+
| searches | MyISAM |      10 | Dynamic    | 973577 |             40 |    38960308 | 281474976710655 |     91711488 |         0 |           NULL | 2012-02-08 22:22:33 | 2012-02-09 11:32:31 | 2012-02-08 22:23:50 | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+--------------
-------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> DESCRIBE searches;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   |     | NULL    |       |
| q     | varchar(255) | NO   | PRI |         |       |
| date  | datetime     | NO   | MUL | NULL    |       |
| view  | int(10)      | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)


mysql> SHOW INDEXES FROM searches;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| searches |          0 | PRIMARY  |            1 | q           | A         |      973577 |     NULL | NULL   |      | BTREE      |         |
| searches |          1 | date     |            1 | date        | A         |        3416 |     NULL | NULL   |      | BTREE      |         |
| searches |          1 | q        |            1 | q           | NULL      |       21635 |     NULL | NULL   |      | FULLTEXT   |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

Currently, there are %70 write and %30 read for this table.

Assume there are 10 million rows and 200 searches perform in a second. What is the recommended structure for this kind of need?

I'm struggling with several index and field combinations on this table but I can't make any significant improvement. So I'll be glad if you can help!

Answer 1

Switch to InnoDB, and

• Set innodb_buffer_pool_size to at least 250M
• Show us the tentative SELECTs
• SHOW CREATE TABLE (DESC is inadequate)

Answer 2

For Such Projects..and scenarios where Write is more than reads, and you need to handle data in large scale, you should go for NoSQL, Like MongoDB, CouchDB or Hbase etc.