Hi all, I'm currently renewing a community. The community has a forum with ~1/2 million records already, and it seems that I should be prepared for at least 10 million rows. Let's say table name is forum. I divided forum into 10 parts: forum1, forum2, forum3... And want to spread according to ID's of the entries. to increase performance to O(N)/10 +1 How should I search across those tables, which has the same structure? Is SQL1 UNION SQL2 UNION.... the right choice? How can I order the results according to one coloumn? I guess it's impossible with UNION. How would you distribute the data? There should be definite number of forum tables. I'm open to all offers.
Hi tolgafiratoglu, You need to check out the table partitioning capabilities for the database engine you are using. Table partitioning lets you split a large table into a number of smaller tables to improve performance. The relevant man pages for MySQL begin at: http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html HTH Petey
And that number is one. There's no need for you to create more than one forum table. You'll have more problems with complicated queries you would have to use than with a number of records. 10 million is not too much if you setup your database server correctly and optimize your database and queries.
Just to clarify. If you implement table partitioning you only ever have ONE forum table. The database engine parses your SQL statement and will only select data from the table partitions that meet the where clause in you SQL. Petey
Use FULLTEXT and make you have indexes every where. It sounds like your forum will probably grow some more so you want to be prepared. Also schedule some "optimize table" jobs to reduce overhead. I would also see what kind of other ids you can use to cross join your tables. For example if categories 1-10 are only in forums2 why go reading through it if the user is surfing category 12? This is something you would have to make a decision on doing and put forth the coding effort.
You need to check out the table partitioning capabilities for the database engine you are using. Table partitioning lets you split a large table into a number of smaller tables to improve performance.