List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 1 2002 6:40pm
Subject:Re: problem with text column and indexes
View as plain text  
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
>ediego@stripped
>ph: 305.284.5449
>fx: 305.284.3404

Thread
problem with text column and indexesEmil Diego1 Feb
  • Re: problem with text column and indexesPaul DuBois1 Feb