Huge Databases

Hi Guys

My website currently has about 55 different databases, each between 700mb and 2gb all with a minimum of 1 million records.

Due to the nature of the data and the size of the databases, everytime a search is conducted I have to link to the various databases that need to be searched.

A typical search will have to search a minimum of 53 million records.

A typical search will take about 8 - 10 minutes. Some searches takes up to 20 minutes before results are displayed.

Is there a way to make results faster or not?

 

For such big tables, you need to try to group the information into smaller tables, called leafs. For example if you have a `sales` table and a `visits` table, you can create leafs for each month and define a MERGE for all the leafs.

Example:

`sales` = MERGE(`sales_2009_01`, `sales_2009_02`, etc).

Thus, when you need to extract the data for a certain day, you can use the leaf instead of all the table. For bigger than a month intervals, you will have to fragment the query for each leaf and sum up the results.

Also, make sure that you have the right indexes defined for your table, as to contain the fields involved in the WHERE clause for your SELECT.

 

Thanks Dwaighty

My problem is that I am only searching one field. Like I said some fields have more than 10 million lines...

 

Rian, that was a general advice I use when working on big tables. If you could show me an example table structure and the field you are searching for, maybe the problem we'll be easily spotted for your peculiar case. You can see the table structure with the SQL command "show create table `table_name`" in your phpMyAdmin.

 

If you have 55 - 1Gb+ tables, you're most likely looking at hardware constraints, even if the database is perfectly designed and all of the queries and tables are optimized.

What server setup do you currently have?

 

There are 2 possible reasons
- issue with indexes, for this case you need to post tables structure
- hardware issue, post please configuration of your server

 

I'm also interested I run a site that would definately get over 1 billion entries per a month. (or about 750,000 if slow) but I'm only storing 300bytes per a query.

Would I have some search problems?

 

Aside from indexing itself It's very important if your query is index-friendly.

 

If you want to get an answer you should dprovide
- tables structure
- queries that you run
- server configuration