asmith

I see. I guess I could add 'OR name_column LIKE '%mykeyword%' add the end of the statement so that if fulltext failed, it searches with LIKE. But still this makes the query bit heavier and MySQL is not really good at searching large data. I have sphinx setup for another table though, ultimately I'll add this table too. :/

Are you suggesting to get rid of the fulltext index/search? it is not possible to find those conditions with fulltext?

Found this thread: http://forums.phpfre...grating-to-ipb/ I've been with phpfreaks for some years, I can't believe phpfreaks did that. To me it seems more like something personal with SMF rather than analyzing 2 softwares and picking one. phpfreaks kinda lost attraction to me. I hope I'm wrong and it is doing even better than ever.

Hello, This is kinda driving me crazy. My search key words doesn't seem to return the result I want. I have this name that ends with a name like this: something_mykeyword So I use FULLTEXT for MATCH(name_column) AGAINST ('mykeyword') and it doesn't find the row. The manual says that * in boolean mode must just append. In this case I need it to be like this: MATCH(name_column) AGAINST ('*mykeyword' in BOOLEAN MODE) Let's say the record doesn't end and mykeyword is in the middle of a bigger word: somebiggermykeywordlying around How do you find it with FULLTEXT? Thanks for your time Mysql 5.5

Do you mind telling me what was wrong with SMF?

Ok here's an update. str_replace() was kinda messing the string, because if 2 urls had same start and if str_replace() were doing the shorter one first, the second was getting half-way done and it produced broken urls. So, I sorted urls by length first: function bylength($a, $b) { $length1 = strlen($a); $length2 = strlen($b); if ($length1 == $length2) return 0; return $length2 > $length1 ? 1 : -1; } uasort($matches[0], 'bylength'); Everything's so far so good and fast enough

Thanks DavidAM. Very detailed. I'll try this and see how much time it will take. Thanks for going into trouble for me

Thanks for the reply. It is all clear. The $content is my example is the whole html output. My code scans the whole output and replace urls with new ones. Your code is dealing only with one url. So I assume you mean, I get all the urls in the output myself in an array, then run each of them by this function.

isn't mod_rewrite an Apache/webserver module for resolving friendly urls to their actual address? I'm trying to do the other way around. Make my boards friendly then use nginx and use its mod_rewrite to resolve these back.

Hello, I'm rewriting my boards names. The original is like SMF: (first number is board id, second is board page) example.com/index.php?board=1.0 to example.com/name-of-board/ if page number is 0 (board=1.0), then page won't be in url: example.com/name-of-board/ if page number is not (board=1.5): example.com/name-of-board-5/ I have an array, with board IDs as keys and values as board names: $new_names = array( 1 => 'new-discussion', 2 => 'feedback' ); Here's the first batch I tried. It is very slow, since I have more than 200 boards: <?php $url_input = array(); $url_output = array(); foreach ($new_names as $id_board => $name) { // When url has other variables. The output put ? at the end to not break other GET variables. $url_input[] = "'example.com/index.php?board=" . $id_board . "\.([1-9][0-9]*)[;&]'"; $url_output[] = 'example.com/' . $name . '-$1/?'; // When url has nothing at the end. $url_input[] = "'example.com/index.php?board=" . $id_board . "\.([1-9][0-9]*)'"; $url_output[] = 'example.com/' . $name . '-$1/'; // Should I go another round for when page number is 0? too nasty! $url_input[] = "'example.com/index.php?board=" . $id_board . "\.0[;&]'"; $url_output[] = 'example.com/' . $name . '/?'; // omg $url_input[] = "'example.com/index.php?board=" . $id_board . "\.0'"; $url_output[] = 'example.com/' . $name . '/'; } $content = preg_replace($url_input, $url_output, $content); ?> That's 4 rule per board (x 200). 800 rules, it is taking 0.2s to 0.5s! Any advise is greatly appreciated.

That's my typo mistake when I was typing the question. That is rm.type.

Hello, Using MySQL 5.5, It is actually so simple, I think I'm missing something in front of my eyes. I have these 2 tables: CREATE TABLE IF NOT EXISTS `records_members` ( `id_record` mediumint( unsigned NOT NULL DEFAULT '0', `id_member` mediumint( unsigned NOT NULL DEFAULT '0', `type` tinyint(4) unsigned NOT NULL DEFAULT '0', KEY `id_record` (`id_record`), KEY `id_member` (`id_member`,`type`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `records` ( `id_record` mediumint( unsigned NOT NULL AUTO_INCREMENT, `id_group` mediumint( unsigned NOT NULL DEFAULT '0', `record` float NOT NULL, `downloads` mediumint( unsigned NOT NULL DEFAULT '0', `posted_time` int(10) unsigned NOT NULL DEFAULT '0', `is_activated` tinyint(4) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id_record`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8; I want to get all the record by a specific member: EXPLAIN SELECT rm.id_member, COUNT(DISTINCT(r.id_group)) AS num FROM records_members AS rm INNER JOIN records AS r ON r.id_record = rm.id_record WHERE rm.id_member IN (95) AND rm.type = 0 GROUP BY rm.id_member This works fine as it is supposed to: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE rm ref id_record,id_member id_member 4 const,const 143 1 SIMPLE r eq_ref PRIMARY PRIMARY 3 test.rm.id_record 1 Same query, if I just request for much more members, it will does a full table scan: EXPLAIN SELECT rm.id_member, COUNT(DISTINCT(r.id_group)) AS num FROM records_members AS rm INNER JOIN records AS r ON r.id_record = rm.id_record WHERE rm.id_member IN (95, 956, 739, 363, 204, 920, 286, 40, 332, 1239, 989, 443, 347, 1122, 11, 75, 2238, 4, 1073, 5, 193, 591, 880, 361, 850, 499, 2213, 592, 133, 1802, 1702, 20, 2111, 1929, 63, 868, 698, 1923, 22, 1881, 507, 143, 785, 2, 586, 2172, 433, 827, 2216, 1987) AND crp.type = 0 GROUP BY rm.id_member EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE rm ALL id_record,id_member NULL NULL NULL 7701 Using where; Using filesort 1 SIMPLE r eq_ref PRIMARY PRIMARY 3 test.rm.id_record 1

it is not members table to have pk and auto_increment. It is points table based on members activity and each member can have different points for different activity. The whole 6 columns must be UNIQUE for example: id_member member_type id_forum id_group id_sport id_season points 23 1 2 3 4 2 45 23 1 3 2 4 2 60 the first 6 columns must be UNIQUE.

Hello, Using MySQL 5.5 and MyISAM tables. I have this table which stores points for members on different situations. Other than id_member, there are 5 other columns that specify these situations. 4 of these columns tiny int and one varchar 10. These 6 columns must be unique. So I made a primary key on all 6 and I update the system using such query: INSERT INTO points VALUES (x, y, ....) ON DUPLICATE KEY UPDATE points = points + VALUES(points) Everything works fine. I tried combining the 5 columns into 1 varchar field separated with coma. So that my primary key goes on 2 columns, id_member and id_situation. It is working with the same speed or maybe a little bit faster but combination of 5 values into one columns make things for other queries harder. I want to know is it a good practice to have primary key on 6 columns? Is there a big downside on such design? This table gets updated A LOT and it is easier for me to have the columns separated than combine them into one. Thanks for your time.

Thanks for you detailed reply!! I've already started reading your tutorial.