Apache Web Hosting Php - Php4, Php5 Programming Blog

Web page design - BLOG DESIGN SOLUTIONS The code should be somewhat

The code should be somewhat familiar by now, with a database query first, followed by a do-while loop in the body of your page. However the SELECT statement in the database query warrants further investigation: SELECT post_id, title, summary, DATE_FORMAT(postdate, ‘%e %b %Y . at %H:%i’) . AS dateattime FROM posts WHERE . MATCH (title,summary,post) AGAINST (’$q’) LIMIT 50 MySQL text searching is performed by combing the MATCH and AGAINST functions. The arguments passed to the MATCH function should be a comma-separated list of the columns you used to create the fulltext index, and the argument to the AGAINST function should be the search term. MySQL automatically returns the results in order of relevance. Because you have only a few posts in your database, the fulltext searching can produce some unexpected results, particularly if you search for a word you know to be repeated in all your posts. In this instance, MySQL will deem the word as common, and you might receive no results for the search. However, as you write more and more, the search results become increasingly accurate. Indexing your blog database MySQL fulltext searching is extremely quick because it uses an index. If you were trying to find a topic in a reference book, you would most likely look at the index first, rather than leafing through the book page by page. In essence, this is how database indexes work, too. Every time you have a WHERE clause in a SELECT query you should ensure that you have an index on each field mentioned in the clause, which can increase speed by an order of magnitude or more (especially for tables with large amounts of data). When you created the posts table, you set the Primary Key option. When this was done, an index was automatically created for the post_id field. However, some queries are being performed on fields that are not indexed: archive.php also queries the postdate field, and post.php queries the post_id field in the comments table. To add indexes to these fields, open up phpMyAdmin in your browser, select the blog database, and click the posts link in the left frame. In the Indexes box, type 1 in the Create an index on n columns box and click Go. Now name the index as idx_postdate, select Index as the Index type (it is probably already selected) and select postdate as the Fields, then press Save (see Figure 7-23).

This entry was posted on Wednesday, January 23rd, 2008 at 3:21 am and is filed under Apache. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.