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!
Best Answer
Switch to InnoDB, and