Field consolidation [ was: Row Consolidation]

[mrnightowl]

What I really meant was Field Consolidation.
I have a couple tables in my database that where poorly constructed. At the rate my site is growing it will only get slower and slower. Every time a user wants to add other users it has to index an entire table now of about 500,000 rows to see if they have added that user. What I want to do for this table is see if there is way for me to take existing data in the table and consildate it to a more managable row size. I believe the amount of rows should be in direct relation with the amount of users. Here is the table structure.

tbl_user_points
user_points_id user_login_id points_for points added_date added_ip
14704 201990106 adduser 2 2007-07-13 0
14705 201990106 adduser 1 2007-07-13 0
14706 201990106 adduser 1 2007-07-13 0
14707 201990106 adduser 1 2007-07-13 0
14708 201990106 adduser 1 2007-07-13 0
14709 201990106 adduser 1 2007-07-13 0
14710 200996995 adduser 1 2007-07-13 0
14711 200996995 adduser 1 2007-07-13 0

What I would like there to be is only 2 Fields...user_login_id which has to be there.... and points_for which would have a format of |adduser,2,2007-07-13,IP ADDY|adduser,1,2007-07-13,IP ADDY| So basically 4 fields consolidated to 1. As you can see the user_login_id repeats every time they add someone. If I have 1,500 uniques a day and they each add 50 people. Thats 75,000 new rows a day. When I don't see why I cant have the IDs of those they added to just 1 row and keep adding to that one row. So what I'm asking is there a program out there that could do this for me? Or is there some other way to do this? I have another table with same problem. But I believe i can take the data from the other table and insert it into a user_table thats already inplace. Set functions will all have to be changed and a bunch of PHP for this but I'm willing to do it. Even if it takes me 2 weeks. :) I setup a TEST site with one of my parked domains to do all the work. In the process now of migrating a copy the database to the test site.

[guelphdad]

you don't want to merge fields like that because any time you save on inserts you will lose on selecting data from that particular field.
500,000 rows is miniscule for mysql to handle.

what fields do you have indexed now? don't over index your table. what type of search do you do on your table (i.e. the common columns in a WHERE or JOIN are the only ones that should be indexed).

have you tuned mysql properly so you are making best use of caching etc?

on another note, when you are adding a user are you using one of

INSERT ON DUPLICATE IGNORE
or
INSERT ON DUPLICATE UPDATE

as necessary? You shouldn't be querying the table to see if the row exists and then enter it but use one of the above as necessary.

[mrnightowl]

you don't want to merge fields like that because any time you save on inserts you will lose on selecting data from that particular field.
500,000 rows is miniscule for mysql to handle.

what fields do you have indexed now? don't over index your table. what type of search do you do on your table (i.e. the common columns in a WHERE or JOIN are the only ones that should be indexed).

have you tuned mysql properly so you are making best use of caching etc?

on another note, when you are adding a user are you using one of

INSERT ON DUPLICATE IGNORE
or
INSERT ON DUPLICATE UPDATE

as necessary? You shouldn't be querying the table to see if the row exists and then enter it but use one of the above as necessary.

Right now the only Column indexed is user_points_id... I've gone over my.cnf with a few others and everyone has agreed for what I'm running it looks good.
that INSERT ON DUPLICAT UPDATE sounds promising. Someone did mention something about using INNODB about row level locking. What's happening is I'm getting alot of people at one time on. It only slows down the add user portion of the site. I'll show you some of the MYsql warnings.

[mrnightowl]

Slow_queries 3,387

Handler_read_rnd 69 M

Handler_read_rnd_next 2,865.90 M

Created_tmp_disk_tables 20 k

Select_full_join 33 k

Sort_merge_passes 32

Table_locks_waited 9,509

I'm open to any option to speed up this portion of the site... these tables allow some unique features that other similiar sites don't have. So if going about it by updating the duplicates. Can I combine the duplicates that are already there?

[guelphdad]

how do you want to combine or eliminate the duplicates? this article (http://www.guelphdad.wefixtech.co.uk/sqlhelp/deleteduplicates.shtml) will explain two ways.

[mrnightowl]

how do you want to combine or eliminate the duplicates? this article (http://www.guelphdad.wefixtech.co.uk/sqlhelp/deleteduplicates.shtml) will explain two ways.

want to combine those with same user_login_id and same date... I'll stick to this forum instead of bouncing back and forth. This is how i have it set now..

$today = date('Y-m-d');
$sql_insert = "INSERT INTO tbl_user_points SET
user_login_id='$friend_id',
points_for='$points_for',
points='$point',
added_date='$today',
added_ip='$this_ip'";
$ado->execute($sql_insert);



Want to thank all for the help..... I was able to work it all out after a long day of reading and trial and error.... Thanks again!