Hi, We're creating a dating site in Symfony Framework (PHP) Our problem: * Let's say there's a "ban" table in database and a "users" table. For each page-view and search, we should JOIN these two tables to find if the user can view the page. It can be a problem in performance when the system will run with million users to JOIN these two very heavy tables. * We can store in a COOKIE, it's ok but how about if someone will ban a user when he/she's online? (Generally users are banned when they are online) there are alternative solutions for good performance, like * storing online bans in a online_bans table and deleting that row when the user'll be online again OR storing all bans with pipes (|) in a table field, But all of these did not seem a real solution for us for good-performanced ban with a 100K user dating site.
I don't see the need for the JOIN. If the ban table just holds the user ID, and timestamp of the ban lift (and maybe a ban reason), you could just query this table for en entry matching the ID and appropriate timestamp. If there's a row, throw an error and exit. Or is there a specific reason you need to use JOINs? EDIT: And also, remember to LIMIT the query to 1, for better performance. You don't need to query the rest of the table after finding a match.
nico, OK my example was wrong, viewing a page can be solved without a join but how about search results? I shouldn't show people who banned that user. In search SQL, I may need a join.
I'm still somewhat confused. Do you want to exclude these members from a general member list (like when they're banned from the whole site by the admin), or can the users block each other and you want to exclude these banned users from private searches?
e.g. when you banned me, I shouldn't see you in my searches anymore. + we want to minimize all kind of db connection, 0 if possible. checking everytime every visited page, if that user banned me, means a distinct db connection per that view. if 1000 users will be online, this many db connections may mean very important.
I don't really understand the tables interaction of your website, I think I understood that a person x can ban a person y,z,... so after he should not see them anymore in contacts,search, etc... Is it that you mean ? - If you have only 1 table user, 1 table ban and 1 table contact : I guess your ban table will not be big as the user table, if everything is indexed well, it should not make too much problem. For example, you can have an algorithm like this : if ($banned_contact > 0) { my $id_banned_contact_list = get_banned_contact($id_user); // "SELECT id_contact FROM ban WHERE id_user='$id_user'" my $query = "SELECT id_user FROM contact WHERE id_user='$id_user' AND id_contact NOT IN ($id_banned_contact_list);"; ... } But it will be also possible to update your table contact each time you ban somebody using a new field, and you will need only 1 request : my $query = "SELECT id_user FROM contact WHERE id_user='$id_user' AND visible='1';";