1

I have a MySQL table with a long data type field:

`create_date` long NOT NULL,

I want to index it:

CREATE INDEX ix_logs_create_date ON `logs` (create_date) USING BTREE;

But I encounter the following exception:

Error Code: 1170. BLOB/TEXT column 'create_date' used in key specification without a key length 0.049 sec

Is it possible to index a long data type field? I have searched but cannot find anything discussing this particular issue

1
  • Seems you mixed long from C/C++ (as big integer type) with mysql alias 'long' which means mediumtext (string/blob datatype) as can be seen here sqlfiddle.com/#!9/57834/1 so you either need to change it to numeric type to save timestamps or to date/datetime which I would prefer.
    – jkavalik
    Commented Jun 28, 2015 at 8:53

1 Answer 1

1

For long (mediumtext) you have to specify how much of the string you want to index. If you want the first 100 bytes in the column to be indexed:

CREATE INDEX ix_logs_create_date ON logs (create_date(100)) USING BTREE;

May I ask why you named a column of type long create_date?

2
  • I store dates as longs. I think I want to convert to bigint Commented Jun 27, 2015 at 16:34
  • 1
    Well, a mediumtext can store up to 16,777,215 (2^24−1) bytes, are you worried that your date representation may one day require a lot more storage? Don't aim for BIGINT, the only sane thing to do is to store dates as DATE. Assume the following scenario, you want to find out the top five days each month that creates the most log-entries. Using a DATE column it will be a walk in the park, using a BIGINT, well I'll leave that as an exercise for you. Commented Jun 27, 2015 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.