Php mysql processes large-capacity data storage

I saw such a post on a forum in php. I hope the index will post the answer here for you to see.

Quote:
--------------------------------------------------------------------------------

When the database is large, such as 0.1 million, 1 GB, how can I search efficiently?
I am currently debugging a forum and writing more than 0.1 million pieces of information to the database. At this time, the main content is concentrated on a table with the size of nearly 1 GB.
Searching for this forum is slow, especially in full-text search
Select * from 'cdb _ posts' where message = 'old tattered booklet is taken out of your arms and handed to me'

It is also very slow, and can't even run on the desktop. However, I have heard that sun's Forum (nearly a hundred sub-forums below) does not take more than 20 seconds for full-text searches. Can we do this.

When I run the search program, the cpu and memory usage are very low, and only the hard drive lights are flashing. I always think that the program is not efficient enough. Can I write a program similar to "multithreading ", to improve efficiency.

Please advise the moderator and various heroes!
Thank you.

--------------------------------------------------------------------------------



Someone suggested using oracle

I think it should be noted that if you want oracle to execute a select * from xxxx where xxx = 'xxxxxxxxxxxxxxxx ';

When this table has 1 GB, it will never be much faster than mysql.

Currently, it is impossible to perform the same operations on so many popular database systems much faster.

The key lies in your database design.

I am working on a search engine for business information and have some experience in the process. As follows:

1. Create a summary table
2. Indexes must be created for data that are frequently queried in the numerical form. I don't remember where I saw a Post saying: "Don't use the junk Index". I 've been depressed for a long time. Such posts are available .. Ah.
Suggestion: Generally, indexes are required for condition with a small size used in the where clause. Large individual tables are stored in separate tables that use like queries.
3. When there are many results, do not select *... first select id... then obtain the id and perform paging control to retrieve the id from the current page to the array .. Then execute select * from xxx where id in (implode (',', $ id array );
If you want to join the data, use this select statement. In this way, the selection factor for database join operations is much smaller.

4. The data to be searched in full text is separated from the current data. In the form of the second paradigm in the database design. The information corresponding to the data primary key is stored in a separate table.

5. Add a primary to the table, int (smallint, tinyint to see how much data is required ).

6. After completing the system, use explain select... to analyze the time-consuming statements in the system. Let's see how mysql handles this statement.

7. If possible, store information sub-tables (which can also be distributed on different database servers)

I think it's a good idea .. It's a joke if you think it's bad...


__________________

I have not tested any questions that I have answered unless otherwise specified ....