[pLog-svn] db optimization

[Mark Wu]

Wow... That's nice! Thanks for such detail  analysis.

BTW, there are two reasons that why we did not use fulltext index:

1. For mysql version compatability:

Our original idea is support mysql DB from 3.2.x to 4.0.x, 4.1.x and 5.x. As
I know Full text index only work in the version that higher then 4.0.x....
(Maybe we can give up the mysql 3.2.x version, not sure how many people
still use it?)

2. For mulitiple language support:

The most bad things is, mysql fulltext index does not support CJK (actually,
all double bytes languages), even I use the utf-8 as charset and collation.

** So, if you have any ideas to break these two challenges, I see no reason
that we should stick in "like %" syntax.

Mark


> Avoid like% statements, they are very slow and can't make use 
> of indices:
> SELECT a.id as id, a.id, a.date,
> a.user_id,a.blog_id,a.status,a.properties, a.num_reads, 
> a.slug, 1 AS relevance, a.num_comments AS num_comments, 
> a.num_nonspam_comments AS num_nonspam_comments, 
> a.num_trackbacks AS num_trackbacks, a.num_nonspam_trackbacks 
> AS num_nonspam_trackbacks, a.global_category_id AS 
> global_category_id, a.in_summary_page AS in_summary_page FROM 
> plog_articles a, plog_articles_categories c, 
> plog_article_categories_link l WHERE  a.blog_id = 1 AND 
> FROM_UNIXTIME(UNIX_TIMESTAMP(a.date)+0)+0 LIKE '200601%'
> AND c.id = l.category_id AND a.id = l.article_id  AND 
> a.status = '1' AND a.date <= '20060402144438'
> GROUP BY a.id
> ORDER BY a.date DESC LIMIT 0, 5;
> 
> Use fulltext index with match against instead of like SELECT 
> article_id FROM plog_articles_text WHERE (((normalized_topic LIKE
> 'S') OR (normalized_text LIKE 'S')) OR ((normalized_topic 
> LIKE 'S') OR (normalized_text LIKE 'S')));