Question

我想加入两张《我的后勤》表格,并将其作为一种观点加以储存,因此,我可以在申请中提出这一看法,而不是提出两个表格。但这种观点非常缓慢。

我的表格是:

CREATE TABLE spectrumsets (
    setid INT(11) NOT NULL,
    timestampdt INT(11) NULL DEFAULT NULL,
    timestampd INT(10) UNSIGNED NOT NULL,
    timestampt INT(10) UNSIGNED NOT NULL,
    device INT(11) NOT NULL,
    methodname VARCHAR(50) NOT NULL,
    PRIMARY KEY (setid),
    UNIQUE INDEX setid_idx (setid),
    UNIQUE INDEX timestamp_device_idx (timestampd, timestampt, device),
    INDEX device_fk (device),
    INDEX timestampd_idx (timestampd),
    CONSTRAINT device_fk FOREIGN KEY (device)
        REFERENCES spectrumdevices (deviceid)
)
COLLATE= utf8_general_ci 
ENGINE=InnoDB

CREATE TABLE spectrumdata (
    valueid INT(11) NOT NULL AUTO_INCREMENT,
    spectrumset INT(11) NOT NULL,
    wavelength DOUBLE NULL DEFAULT NULL,
    intensity DOUBLE NULL DEFAULT NULL,
    PRIMARY KEY (valueid),
    INDEX spectrumset_idx (spectrumset),
    CONSTRAINT spectrumset_fk FOREIGN KEY (spectrumset)
        REFERENCES spectrumsets (setid)
)
COLLATE= utf8_general_ci 
ENGINE=InnoDB

我认为:

SELECT spectrumsets.timestampd,spectrumsets.timestampt,spectrumsets.device,
    spectrumdata.wavelength,spectrumdata.intensity
FROM spectrumdata INNER JOIN spectrumsets ON spectrumdata.spectrumset=
    spectrumsets.setid
WHERE spectrumdata.wavelength>0 
ORDER BY spectrumsets.timestampd,spectrumsets.timestampt,spectrumsets.device,
    spectrumdata.wavelength

我的机体上的“密码>(*)为385.516秒,结果为82923705记录,因此数据集相当庞大。

我已经找到了这个网站link,但仍未完全理解什么错误。

最新资料:

http://www.un.org/Depts/DGACM/index_spanish.htm

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","spectrumsets","index","PRIMARY,setid_idx","timestamp_device_idx","12",NULL,"327177","Using index; Using temporary; Using filesort"
"1","SIMPLE","spectrumdata","ref","spectrumset_idx","spectrumset_idx","4","primprod.spectrumsets.setid","130","Using where"

Answer 1

解释表明,盘问正在打上加入指数(这是好的),但随后用临时表格和档案处理其他询问。

原因有两个:

the where clause isn t hitting the index
the order by clause isn t hitting the index

在一项评论中,你说,删除哪项条款可带来重大改进;这意味着,如果假设波长有适当的数量可能值,则需要复合指数,即波长(如果它仅仅具有10项价值,指数可能不会有任何作用)。

如果你将“按顺序排列”条款排除在你看来之外,它就应当大大加快,而让令有一个很好的理由,由提取数据的询问而不是意见来决定。我猜测大多数问题,将非常有选择性地分析数据,仅限于几个时间序列;你将顺序按观点加以改动,为每次分类付出代价。

如果你真的必须“按顺序排列”来制定指数,把所有领域按“顺序排列”排列,并加入前线。例如:

UNI Request INDEX timestamp_device_idx (set_id, timestampd, timetampt, Tool),

友情链接