Bulletin design help

jasondavis

Here is my bulletins table

DROP TABLE IF EXISTS `friend_bulletin`;
CREATE TABLE `friend_bulletin` (
  `auto_id` int(11) NOT NULL auto_increment,
  `user_id` int(10) NOT NULL default '0',
  `bulletin` text NOT NULL,
  `subject` varchar(255) NOT NULL default '',
  `color` varchar(6) NOT NULL default '000000',
  `submit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` enum('Active','In Active') NOT NULL default 'Active',
  `spam` enum('0','1') NOT NULL default '1',
  PRIMARY KEY  (`auto_id`),
  KEY `user_id` (`user_id`),
  KEY `submit_date` (`submit_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is my friends Table

CREATE TABLE `friend_friend` (
  `autoid` int(11) NOT NULL auto_increment,
  `userid` int(10) default NULL,
  `friendid` int(10) default NULL,
  `status` enum('1','0','3') NOT NULL default '0',
  `submit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `alert_message` enum('yes','no') NOT NULL default 'yes',
  PRIMARY KEY  (`autoid`),
  KEY `userid` (`userid`),
  KEY `friendid` (`friendid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here is my Quiery to get users friend bulletins

SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
FROM friend_bulletin AS fb
WHERE (user_id IN (SELECT userid FROM friend_friend WHERE friendid = 13 AND status =1) OR user_id = 13)
AND spam = '1'
ORDER BY submit_date DESC
LIMIT 0 , 20

Somebody told me for speed I could create a lookup table for buleltins with just 2 fields

bulletin id and uid

Im confused on how to do this though, after I have new table up and I select from the lookup table instead, how do I make the results lookup the proper list of bulletins to display

bpat1434

Basically, you'd query against the look-up table and join information from the other tables on the look-up table:

SELECT fb.auto_id, fb.user_id, fb.bulletin, fb.subject, fb.color, fb.submit_date, fb.spam
FROM friend_lookup AS lu
LEFT JOIN friend_friend AS ff
    ON lu.friend_id = ff.autoid
LEFT JOIN friend_bulletin AS fb
    ON lu.bulletin_id = fb.auto_id
WHERE  .....

Hope that helps...

jasondavis

am I wrong in thinking that this would probably hurt performance instead of help it by joining 2 extra tables?

troybtj

jasondavis wrote:
am I wrong in thinking that this would probably hurt performance instead of help it by joining 2 extra tables?

if you put "Explain" before SELECT, it will describe how the query will work and in what order, to give you an idea of performance.

Here is the explain of a long query using a sub-select:

| id | select_type        | table        | type   | possible_keys       | key     | key_len | ref                     | rows | Extra                           |
+----+--------------------+--------------+--------+---------------------+---------+---------+-------------------------+------+---------------------------------+
|  1 | PRIMARY            | f            | ALL    | PRIMARY             | NULL    | NULL    | NULL                    | 1133 | Using temporary; Using filesort | 
|  1 | PRIMARY            | u            | eq_ref | PRIMARY,usergroupid | PRIMARY | 4       | vbulletin.f.userid      |    1 | Using where                     | 
|  2 | DEPENDENT SUBQUERY | usergroup | eq_ref | PRIMARY             | PRIMARY | 2       | vbulletin.u.usergroupid |    1 |                                 | 
Time was about 0.01 seconds.

If it were written as a join, there wouldn't be a need for a temporary table or subquery, which would improve performance.

Compare to a Join table to get similar information:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	user	const	PRIMARY	PRIMARY	4	const	1	 
1	SIMPLE	userfield	const	PRIMARY	PRIMARY	4	const	1	 
1	SIMPLE	usertextfield	const	PRIMARY	PRIMARY	4	const	1	 
1	SIMPLE	language	system	PRIMARY	 	 	 	1	
Time Taken: 0.00056 seconds

bpat1434

very good point with the EXPLAIN syntax. I fixed your tags to use [code] tags instead of [pre] since there is no [pre] tag on this forum.

jasondavis

I made a new lookup table to test here are my results
the lookup table seems to slow things down

//Version 1  (20 total, Query took 0.0485 sec)
//-------------------------------------------------

SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
FROM friend_bulletin AS fb
LEFT JOIN friend_friend AS ff ON fb.user_id = ff.userid
WHERE (
ff.status =1
AND ff.friendid =13
AND fb.spam = '1'
)
OR fb.user_id =13
ORDER BY fb.submit_date DESC
LIMIT 0 , 20 


//Version 2  (20 total, Query took 0.0643 sec) USING LOOK Up TABLE
//-------------------------------------------------
SELECT fb.auto_id, fb.user_id, fb.bulletin, fb.subject, fb.color, fb.submit_date, fb.spam
FROM friend_bulletin_lookup AS lu
LEFT JOIN friend_friend AS ff
    ON lu.f_id = ff.autoid
LEFT JOIN friend_bulletin AS fb
    ON lu.b_id = fb.auto_id
WHERE
(
ff.status =1
AND ff.friendid =13
AND fb.spam = '1'
)
OR fb.user_id =13
ORDER BY fb.submit_date DESC
LIMIT 0 , 20 


//Version 3 Showing rows 0 - 19 (20 total, Query took 0.0254 sec)
//-------------------------------------------------
SELECT auto_id, user_id, bulletin, subject, color, fb.submit_date, spam
FROM friend_bulletin AS fb
WHERE (user_id IN (SELECT userid FROM friend_friend WHERE friendid = 13 AND status =1) OR user_id = 13)
AND spam = '1'
ORDER BY submit_date DESC
LIMIT 0 , 20

bpat1434

Is the lookup table indexed?

jasondavis

yes

bpat1434

Well, if your tables are small (like less than a few thousand rows) it may be slower to join; however, once the tables grow huge, it should be faster.

Try running the EXPLAIN statement for the query and see what that shoots out at you.

jasondavis

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	lu 	ALL 	NULL 	NULL 	NULL 	NULL 	3008 	Using temporary; Using filesort
1 	SIMPLE 	ff 	eq_ref 	PRIMARY 	PRIMARY 	4 	friend_main.lu.f_id 	1 	 
1 	SIMPLE 	fb 	eq_ref 	PRIMARY,user_id 	PRIMARY 	4 	friend_main.lu.b_id 	1 	Using where

jasondavis

i will try it when the DB gets larger I guess

jasondavis

I guy wgho runs a big social network around 1 million members tells me that he uses a different technique, he says he uses hash to look up bulletins entries much faster, has anyone heard of anything like this?

halojoy

jasondavis wrote:
I guy wgho runs a big social network around 1 million members tells me that he uses a different technique,

he says he uses hash to look up bulletins entries much faster,
has anyone heard of anything like this?

No, I havent.
But this wikipedia describes a bit:
http://en.wikipedia.org/wiki/Hash_function

In short:
A larger data can be hashed, to create a smaller fingerprint,
that make a unique identification of this data.

🙂

halojoy

to make an example
how to use hash to speed up

say you create a new table 'userpass_hash', where each row have:
'hashvalue', 'userid'
each hashvalue is created by 2 values from 'users' table:
$hashvalue = hash( username[userid]+password[userid] );

someone submits a login: uname, pword
you create a
$lookfor_hash = hash( uname+pword );

Then if not $lookfor_hash is found in 'userpass_hash' table, then is FALSE login.
If is found, you get the 'userid'
and can select from 'users' table.

🙂

jasondavis

that makes a little sense to me but I dont see how this guy could use hash for fast bulletins lookup, It would seem to me no matter what I would have to search whole entire buleltin table to see if the user id that posted that bulletin is in my friend list table

halojoy

jasondavis wrote:
It would seem to me no matter what I would have to search whole entire buleltin table
to see if the user id that posted that bulletin is in my friend list table

Yes, in this case you are right.
It would not be possible to use some hashing.
Not what I can see either.