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
