At 13:05 -0500 2/1/02, Diego, Emil wrote: >I have a table that I created and i am trying to create an index that >contains a text field. I am having prblems with the syntax and keep >receiving errors whenever I try to create the table. > >Here is the SQL statement for the table: > >CREATE TABLE IF NOT EXISTS logInfo ( > ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, > entryDate datetime NOT NULL, > ipAddress varchar(20) NOT NULL, > siteURL TEXT NOT NULL, > > /* Indexes */ > INDEX idxEntryDate(entryDate, ipAddress), > PRIMARY KEY (ID) >); > >How do i create a key that includes the entryDate, ipAddress and siteURL >fields?
You cannot index TEXT or BLOB fields in their entirety, so you'll need to specify a length indicating how many leftmost characters to index. For example, if the first 40 characters are enough, you should be able to do this: INDEX idxEntryDate(entryDate, ipAddress, siteURL(40)) Trouble is, URLs tend to be unique on the *right*, not the left. Maybe you should store the URLs as reversed strings. It'd improve the efficiency of the lookup. > > >Emil Diego >Web Coordinator >University of Miami School of Business >[EMAIL PROTECTED] >ph: 305.284.5449 >fx: 305.284.3404 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php