Unknown column error with an index

Question

I created a simple index associated with a timestamp column

CREATE TABLE `data` (
    `id` INT(11) UNSIGNED NOT NULL,
    `ticker` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
    `comment` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
    `link` VARCHAR(256) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `comment_hash` CHAR(32) NOT NULL COLLATE 'utf8_unicode_ci',
    `time` DATETIME NOT NULL,
    `feed` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
    `source` INT(11) NOT NULL,
    `active` TINYINT(1) NOT NULL DEFAULT '1',
    `score` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `dttm` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ticker`, `comment_hash`),
    INDEX `time_index1` (`time`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

However when I try to do anything with the index, it gives me SQL Error (1054): Unknown column 'time_index1' in 'where clause'

Sample queries:

select *
from data
where time_index1 = '2013-10-01 17:18:06'

or

select *
from data
order by time_index1

score 2 · Accepted Answer · answered Oct 02 '13 at 15:55

2

Your SELECT statement is referencing the name of the index instead of the indexed field.

Use this instead:

select *
from data
where time = '2013-10-01 17:18:06'

answered Oct 02 '13 at 15:55

Hannah Vernon

70,041
22
171
315

You're welcome! Any chance you could upvote my answer, and perhaps mark it as correct? See the up/down arrows to the left of my answer, and the check-mark below the down-arrow? Click them! Thanks! – Hannah Vernon Oct 02 '13 at 15:58
2

Cant upvote, not enough points yet. Will accept it once cooldown is done! – rodling Oct 02 '13 at 16:05

Unknown column error with an index

1 Answers1