Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
1  
Not really part of the issue, but what is the point of the id column if it's not the primary key? I'm assuming that it's an arbitrary number, so let us know if it's not. Also, what version of mysql? –  Derek Downey Feb 13 '12 at 13:51
    
also, let us know your hardware (amount of RAM, speed of disks, raid setup, etc) –  Derek Downey Feb 13 '12 at 13:53

2 Answers 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.

share|improve this answer
8  
Sorry, but generally switching technologies isn't the right answer. This data-set should not be that difficult to achieve required performance. –  Derek Downey Feb 13 '12 at 13:52
    
Hmm.. I dont Say you cannot do it with RDBMS, But this was a suggestion,I dont Understand Why we have a narrow Minded Approach, For this. See When there is a technology specifically made to handle it, why don't we use it.. Comon, Google, FB, Twitter, All use this, and we know how scalable and Performance oriented it is. Please read : dbta.com/Articles/Columns/Notes-on-NoSQL/Why-NoSQL-69165.aspx java.dzone.com/articles/why-nosql-not-just-google-and –  azzaxp Feb 14 '12 at 6:35
10  
I don't see how making a suggestion to switch technologies, without any details on how to use that technology for the asker's problem, is helpful at all. –  Derek Downey Feb 14 '12 at 13:40
    
Sorry for that. I agree to your Point. @DTest –  azzaxp Feb 15 '12 at 7:00
5  
We do ~ 3000 - 5000 writes per second, ~ 300 - 500 concurrent users, 24/7 on a single PostgreSQL database server. Why would we need NoSQL? What problem would that solve? A dbms is fine piece of technology. –  Frank Heikens May 6 '12 at 11:50

Switch to InnoDB, and

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.