Huge Number of Records

[mrgrammar]

I'm designing a membership site which will probably have millions of members. The server can handle the visitors, but I'm concerned about two aspects:

1. There is one record per visitor, 6 fields per record. Will MySQL Server handle all those records? If not, what do I need to do?

2. When displaying the total record count (to see the total number of members), the only method I know is to run a query and use mysql_num_rows to count the records. Example:

$qname = "select id from table";
$qrec = mysql_db_query($dbname, $qname);
echo mysql_num_rows($qrec);

Once several hundred thousand records are stored in the database, it takes awhile to get the result. Is there a better way to get the total record count?

BD

[crazychris]

millions of members? good luck on that.

"select count(*) from table" is the fastest query, real damn fast.

I have some tracking tables for page views which have millions of records in them, and all I can say is to plan them very well. Think, test and plan. Once I got to about 4.5 million records my queries started to slow down (select, insert still fine) and I spent a huge amount of time trimming the structure to make keys better and queries faster.

seriously, if you are expecting a table with 1,000,000+ records in it, create a structure, fill it up with records, then test against it and refine it till it works as hoped!

[mrgrammar]

Thanks. I'm laughing and kicking myself because I knew about count(*) and had completely forgotten about it. lol

As far as testing goes, I'm already doing that. Luckily, all the owner wants to use the db for is maintaining membership info and login. Comparing three input fields against one particular id number is very quick so far.

There are actually 2 tables. One for membership data (6 fields that rarely have to be updated other than to deactivate or delete them) and a second for valid membership numbers. The owner sells memberships to groups of people. When he does, he simply enters the range of valid membership numbers.

I have a do while loop set up so it starts at the first number input and runs an insert query for each record until it gets to the end.

$cnum = $_POST[startingnumber];
$qnum = $_POST[quitnumber];
do{
	$insnum = "INSERT INTO table (field1, field2) VALUES (".$cnum.", 1)";
	$doins = mysql_db_query($dbname, $insnum);
	if ($doins != 1){
		echo "error msg to user";
		echo "<p><b>Error:</b> ".mysql_error();
		echo "</p>";
		echo "finish error msg to user";
		die;
	}else{
		echo "Card number ".$cnum." added successfully.<br>";
	}
	$cnum++;
}while($cnum < $qnum);

I ran a test to insert 1 million numbers and it takes quite a bit of time. Any suggestions for that one?

[crazychris]

there are ways to speed up queries....

1. remove all keys, do the inserts, put the keys back - big saving
2. try bulk-inserts "insert into table (filed,filed) values (val,val),(val,val),(val,val)..." - a query can be quite large (I tend to limit to 500K😎 - small saving
3. save the data in a tab/csv file and use "LOAD DATA INFILE..." which is blindingly fast!