I had to do a script for iterating through a set of MySQL fields of type Datetime. Since I’ve run through this before, I’m blogging the litle thing for rememberance in the future. Here is the a version in Javascript.

for(var a=2001; a <2007; a++) {

 for(var m=1; m<13; m++) {

 	var monthdays = 31;

 	if(m==4 || m==6 || m==9 || m==11) {

 		monthdays = 30;

 	}

 i	f(m==2) {

 		if( a%4 == 0 ) {

 			monthdays = 29;

 		} else {

 			monthdays = 28;

 		}

 	}

 	for(var d=1; d< (monthdays+1); d++) {

 		document.write(a + ' ' + m + ' ' + d + '<br/>');

 	}

 }

}

Fulltext indexes improve the performance of queries from text or long varchar fields, compared to using like or Rlike they are way faster. They also provide more intelligence to searching by matching and sorting not just by existence of a word in the field but by considering the weight of the word in that text ( my guess on the meaning of this is actual count of the word in the text divided by total words in the text). A more comprehensive explanation of MySql’s implementation of fulltext search can be accessed here (I’m linking to google’s cache since the original presentation is not accessible anymore). There is an interesting explanation there of how the index is actually created.

Basic Syntax

To query a table by full text search firts the field needs to be indexed as fulltext:

Alter table mytable add Fulltext mytable_fieldToSearch( fieldToSearch );

To do a basic search the syntax is the following:

Select elemID from fieldToSearch where Match( fieldToSearch ) against("word1 word2") limit 10

Considerations

• When searching by fulltext search it’s not posible to add other conditions to the select statement.
• It’s important to add a limit to the query
• To further improve performance (specially when searching within multiple fulltext indexed fields) is to create an intermediate table with just the ID of what we are searching for and the different fields of text concatenated into one, and then querying that table directly.
• It may also be important to collate the database field to a character set (I’m writing this to remember in the future):

  
  ALTER TABLE mytable DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci;