why my db don't use index?
Posted by: Torsten Mueller
Date: July 13, 2013 03:59AM
Date: July 13, 2013 03:59AM
Hi,
i have a table:
CREATE TABLE artikel_Mode (
product_id bigint(20) NOT NULL AUTO_INCREMENT,
anbieter varchar(64) NOT NULL,
ueberschrift varchar(260) NOT NULL,
beschreibung varchar(4512) NOT NULL,
bildlink varchar(255) NOT NULL,
shoplink varchar(512) NOT NULL,
preis float NOT NULL,
marke varchar(255) NOT NULL,
kat varchar(12) NOT NULL,
lieferkosten double NOT NULL,
lieferzeit varchar(56) NOT NULL,
extra1 varchar(2000) NOT NULL,
extra2 varchar(2000) NOT NULL,
zupid varchar(128) NOT NULL,
PRIMARY KEY (product_id),
KEY preis (preis),
FULLTEXT KEY ueberschrift (ueberschrift,beschreibung,anbieter,marke)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=14359062 ;
Now, i've made the following query:
Explain SELECT ueberschrift, SUBSTRING(beschreibung,1,250),bildlink,shoplink,preis,marke,anbieter,lieferkosten,lieferzeit,extra1,extra2,zupid,kat FROM artikel_Mode WHERE kat IN ('132','133','134','135','136','137','138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','497','498','499','500','501','502','503','504','505','506','507','508','509','510','511','512','513','514','515','516','517','518','519','520','521','522','523','524','525','526','527','528','529','530','531','532','533','534','535','536','537','538','539','540','541','542','543','544','545','546','547','548','549','550','551','552','553','554','555','556','557','558','559','560','561','562','563','564','565','566','567','568','569','570','571','572','573','574','575','576','577','578','579','580','581','582','583','584','585','586','587','588','589','590','591','592','593','594','595','596','597','598','599','600','601','602','603','604','605','606','607','608','609','610','611','612','613','614','615','616','617','618','619','620','621','622','623','624','625','626','627','628','629','630','631','632','633','634','635','636','637','638','639','640','641','642','643','644','645','646','647','648','649','650','651','652','653','654','655','656','657','658','659','660','661','662','663','664','665','666','667','668','669','670','671','672','673','674','675','676','677','678','679','680','681','682','683','684','685','686','687','190','190','190','190','185','185','185','185','185','162','178','178','178','601','184','188','188','177','177','601','601','576','576','576','576','576','576','576','576','576','601','601','578','578','578','578','578','539','539','539','539','628','628','628','626','626','626','626','626','626','626','626','626','626','636','555','555','555','555','555','555','555','592','592','592','556','556','556','593','638','642','642','551','551','551','551','551','551','634','635','545','581','581','581','581','581','575','575','575','575','576','576','564','558','558','558','558','578','537','537','537','583','583','614','616','653','653','504','504','504','502','502','656','653','653','653','620','620','578','575','575','575','575','575','575','581','539','539','556','575','575','575','576','576','576','626','629','629','629','629','626','626','629','504','186','644','185','185','630','630','630','630','630','628','626','626','626','626','626','626','626','626','505','505','505','505','505','626','626','626','626','626','626','626','626','626','626','626','626','631','644','644','644','644','644','644','644','644','644','644','644','644','644','644','644','644','653','653','653','653','653','653','649','649','624','624','624','624','624','624','624','624','618','642','642','686','686','686','686','686','686','686','610','610','612','612','613','613','631','631','631','631','649','653','624','631','642','635','686','505','190','190','185','497','497','497','497','504','507','507','507','507','507','502','502','502','508','631','631','631','631','631','631','631','631','631','631','631','631','631','631','651','651','651','655','655','655','655','655','645','645','645','645','645','645','626','626','626','626','626','626','626','619','619','619','619','620','620','620','620','620','654','654','609','609','610','610','610','610','686','686','613','613','613','612','612','188','191','184','625','631','643','155','160','161','557','547','541','133','142','523','523','530','523','522','562','558','558','501','549','517','512','680','682','146','142','541','549','549','549','549','577','577','557','542','545','545','549','539','539','143','556','556','517','523','523','148','147','160','557','564','562','168','501','146','146','161','600','600','600','600','600','587','587','582','582','597','589','589','589','589','141','141','589','589','178','178','178','178','182','584','584','584','605','607','606','607','142','142','142','142','142','142','684','684','684','587','594','593','587','144','172','597','172','575','575','575','575','575','576','576','576','576','576','576','602','551','551','551','551','551','551','551','551','551','551','551','551','551','551','549','549','549','549','549','549','549','549','497','497','500','501','502','502','502','137','137','137','137','500','500','500','500','500','500','500','500','500','500','500','500','555','555','555','555','555','555','555','555','555','555','592','592','592','592','539','539','539','539','539','539','539','539','539','539','539','539','502','607','576','155','162','179','179','178','179','564','558','558','558','587','587','575','575','575','575','575','575','575','575','575','575','575','575','575','547','600','600','575','575','575','155','537','537','537','537','154','156','157','555','549','558','558','558','558','558','558','558','558','558','558','558','558','558','558','558','558','558','556','557','588','594','587','587','587','589','589','590','593','593','588','600','596','596','596','596','538','578','578','578','578','578','578','576','551','551','551','551','551','551','551','551','551','551','539','539','539','539','539','539','539','539','539','577','577','577','577','577','576','539','539','538','581','549','549','549','549','549','558','558','558','558','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','162','549','576','576','576','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','576','576','576','576','575','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','551','551','549','549','549','558','558','558','575','575','575','575','564','564','564','564','564','564','564','564','564','578','581','137','137','137','142','142','142','154','154','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','576','576','576','576','576','576','581','538','538','600','600','600','600','600','600','601','601','551','551','551','551','551','551','551','551','551','551','551','551','551','549','589','589','589','551','551','551','551','634','634','549','549','549','645','645','645','555','539','575','578','573','574','572','572','538','541','542','557','558','577','575','577','600','602','589','589','592','587','587','588','587','594','594','582','582','601','537','539','578','558','556','555','557','542','133','133','133','146','142','146','501','497','500','537','521','521','521','511','513','769','769','769','522','522','770','575','575','575','578','578','578','555','555','555','556','771','154','154','166','162','165','166','161','160','772','773','774','520','536','536','536','536','536','536','536','536','536','536','536','536','536','536','545','561','551','549','577','520','520','589','592','588','587','583','601','776','776','502','584','777','176','782','782','782','782','142','142','138','551','551','551','551','551','551','551','551','551','551','551','549','549','549','549','549','549','549','575','575','575','575','575','575','501','500','545','538','579','538','532','500','500','500','500','500','500','500','542','539','539','539','539','539','561','561','542','542','542','542','542','542','542','542','542','542','546','546','556','556','556','556','556','556','542','537','537','793','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','536','536','539','539','539','539','539','539','539','539','575','575','575','575','575','577','581','577','575','601','548','548','548','548','548','548','548','548','548','548','548','548','799','509','509','513','513','513','800','800','801','603','603','603','603','603','500','680','808','684','809','686','537','538','524','528','669','583','812','154','546','546','546','546','545','586','586','586','564','564','564','564','549','549','549','551','551','551','551','551','575','814','814','536','536','536','536','509','509','509','509','509','603','603','603','603','603','603','551','551','575','581','576','538','821','132','822','664','664','664','664','829','582','601','601','601','600','600','589','589','591','592','592','584','584','584','584','584','584','830','830','584','584','584','584','584','584','584','176','176','181','831','595','595','595','595','601','601','776','776','587','587','591','591','832','832','832','832','832','832','832','832','832','832','832','832','832','832','595','595','595','595','595','595','595','595','595','595','595','595','591','142','598','684','684','607','607','605','537','551','551','551','551','143','524','531','531','531','577','577','602','602','549','834','497','539','539','539','539','539','548','548','497','497','500','500','500','555','555','592','636','638','639','637','578','579','814','656','620','599','558','558','557','591','587','832','588','587','594','642','564','134','144','564','540','537','541','542','542','536','149','141','836','152','152','556','556','517','517','509','514','545','550','838','572','573','574','564','528','528','520','680','501','501','680','560','561','561','561','839','523','523','649','649','649','653','653','659','635','618','618','632','614','618','624','624','506','840','611','582','583','589','590','584','584','584','586','593','596','585','841','502','597','842','606','607','603','684','598','154','155','159','160','163','161','161','162','164','166','169','167','154','165','170','168','641','625','619','652','650','650','654','650','633','619','619','619','507','507','507','507','507','549','558','558','549','549','548','558','551','577','575','581','576','576','575','575','575','539','539','539','539','539','536','579','579','579','579','579','578','578','547','511','518','517','509','513','549','523','535','530','523','523','523','533','528','517','520','520','520','520','520','523','523','555','555','555','555','555','555','555','550','556','602','601','601','601','600','600','589','589','590','590','582','583','583','584','584','584','584','585','585','592','592','592','592','592','592','593','594','587','587','587','588','591','178','177','177','180','180','179','179','182','183','154','155','155','156','160','160','160','166','155','161','161','161','160','161','165','165','166','167','165','164','164') AND MATCH (ueberschrift,beschreibung) AGAINST ('wolle' IN BOOLEAN MODE)
ORDER BY ueberschrift ASC LIMIT 26,24
It takes about 11 seconds and show me that:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE artikel_Mode ALL NULL NULL NULL NULL 515967 Using where; Using filesort
What can i do? How can i optimize the table? The data i load in this table with LOAD DATA INFILE.
Best regards and thanks for help.
Torsten
i have a table:
CREATE TABLE artikel_Mode (
product_id bigint(20) NOT NULL AUTO_INCREMENT,
anbieter varchar(64) NOT NULL,
ueberschrift varchar(260) NOT NULL,
beschreibung varchar(4512) NOT NULL,
bildlink varchar(255) NOT NULL,
shoplink varchar(512) NOT NULL,
preis float NOT NULL,
marke varchar(255) NOT NULL,
kat varchar(12) NOT NULL,
lieferkosten double NOT NULL,
lieferzeit varchar(56) NOT NULL,
extra1 varchar(2000) NOT NULL,
extra2 varchar(2000) NOT NULL,
zupid varchar(128) NOT NULL,
PRIMARY KEY (product_id),
KEY preis (preis),
FULLTEXT KEY ueberschrift (ueberschrift,beschreibung,anbieter,marke)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=14359062 ;
Now, i've made the following query:
Explain SELECT ueberschrift, SUBSTRING(beschreibung,1,250),bildlink,shoplink,preis,marke,anbieter,lieferkosten,lieferzeit,extra1,extra2,zupid,kat FROM artikel_Mode WHERE kat IN ('132','133','134','135','136','137','138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','497','498','499','500','501','502','503','504','505','506','507','508','509','510','511','512','513','514','515','516','517','518','519','520','521','522','523','524','525','526','527','528','529','530','531','532','533','534','535','536','537','538','539','540','541','542','543','544','545','546','547','548','549','550','551','552','553','554','555','556','557','558','559','560','561','562','563','564','565','566','567','568','569','570','571','572','573','574','575','576','577','578','579','580','581','582','583','584','585','586','587','588','589','590','591','592','593','594','595','596','597','598','599','600','601','602','603','604','605','606','607','608','609','610','611','612','613','614','615','616','617','618','619','620','621','622','623','624','625','626','627','628','629','630','631','632','633','634','635','636','637','638','639','640','641','642','643','644','645','646','647','648','649','650','651','652','653','654','655','656','657','658','659','660','661','662','663','664','665','666','667','668','669','670','671','672','673','674','675','676','677','678','679','680','681','682','683','684','685','686','687','190','190','190','190','185','185','185','185','185','162','178','178','178','601','184','188','188','177','177','601','601','576','576','576','576','576','576','576','576','576','601','601','578','578','578','578','578','539','539','539','539','628','628','628','626','626','626','626','626','626','626','626','626','626','636','555','555','555','555','555','555','555','592','592','592','556','556','556','593','638','642','642','551','551','551','551','551','551','634','635','545','581','581','581','581','581','575','575','575','575','576','576','564','558','558','558','558','578','537','537','537','583','583','614','616','653','653','504','504','504','502','502','656','653','653','653','620','620','578','575','575','575','575','575','575','581','539','539','556','575','575','575','576','576','576','626','629','629','629','629','626','626','629','504','186','644','185','185','630','630','630','630','630','628','626','626','626','626','626','626','626','626','505','505','505','505','505','626','626','626','626','626','626','626','626','626','626','626','626','631','644','644','644','644','644','644','644','644','644','644','644','644','644','644','644','644','653','653','653','653','653','653','649','649','624','624','624','624','624','624','624','624','618','642','642','686','686','686','686','686','686','686','610','610','612','612','613','613','631','631','631','631','649','653','624','631','642','635','686','505','190','190','185','497','497','497','497','504','507','507','507','507','507','502','502','502','508','631','631','631','631','631','631','631','631','631','631','631','631','631','631','651','651','651','655','655','655','655','655','645','645','645','645','645','645','626','626','626','626','626','626','626','619','619','619','619','620','620','620','620','620','654','654','609','609','610','610','610','610','686','686','613','613','613','612','612','188','191','184','625','631','643','155','160','161','557','547','541','133','142','523','523','530','523','522','562','558','558','501','549','517','512','680','682','146','142','541','549','549','549','549','577','577','557','542','545','545','549','539','539','143','556','556','517','523','523','148','147','160','557','564','562','168','501','146','146','161','600','600','600','600','600','587','587','582','582','597','589','589','589','589','141','141','589','589','178','178','178','178','182','584','584','584','605','607','606','607','142','142','142','142','142','142','684','684','684','587','594','593','587','144','172','597','172','575','575','575','575','575','576','576','576','576','576','576','602','551','551','551','551','551','551','551','551','551','551','551','551','551','551','549','549','549','549','549','549','549','549','497','497','500','501','502','502','502','137','137','137','137','500','500','500','500','500','500','500','500','500','500','500','500','555','555','555','555','555','555','555','555','555','555','592','592','592','592','539','539','539','539','539','539','539','539','539','539','539','539','502','607','576','155','162','179','179','178','179','564','558','558','558','587','587','575','575','575','575','575','575','575','575','575','575','575','575','575','547','600','600','575','575','575','155','537','537','537','537','154','156','157','555','549','558','558','558','558','558','558','558','558','558','558','558','558','558','558','558','558','558','556','557','588','594','587','587','587','589','589','590','593','593','588','600','596','596','596','596','538','578','578','578','578','578','578','576','551','551','551','551','551','551','551','551','551','551','539','539','539','539','539','539','539','539','539','577','577','577','577','577','576','539','539','538','581','549','549','549','549','549','558','558','558','558','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','564','162','549','576','576','576','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','576','576','576','576','575','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','539','551','551','549','549','549','558','558','558','575','575','575','575','564','564','564','564','564','564','564','564','564','578','581','137','137','137','142','142','142','154','154','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','575','576','576','576','576','576','576','581','538','538','600','600','600','600','600','600','601','601','551','551','551','551','551','551','551','551','551','551','551','551','551','549','589','589','589','551','551','551','551','634','634','549','549','549','645','645','645','555','539','575','578','573','574','572','572','538','541','542','557','558','577','575','577','600','602','589','589','592','587','587','588','587','594','594','582','582','601','537','539','578','558','556','555','557','542','133','133','133','146','142','146','501','497','500','537','521','521','521','511','513','769','769','769','522','522','770','575','575','575','578','578','578','555','555','555','556','771','154','154','166','162','165','166','161','160','772','773','774','520','536','536','536','536','536','536','536','536','536','536','536','536','536','536','545','561','551','549','577','520','520','589','592','588','587','583','601','776','776','502','584','777','176','782','782','782','782','142','142','138','551','551','551','551','551','551','551','551','551','551','551','549','549','549','549','549','549','549','575','575','575','575','575','575','501','500','545','538','579','538','532','500','500','500','500','500','500','500','542','539','539','539','539','539','561','561','542','542','542','542','542','542','542','542','542','542','546','546','556','556','556','556','556','556','542','537','537','793','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','548','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','796','536','536','539','539','539','539','539','539','539','539','575','575','575','575','575','577','581','577','575','601','548','548','548','548','548','548','548','548','548','548','548','548','799','509','509','513','513','513','800','800','801','603','603','603','603','603','500','680','808','684','809','686','537','538','524','528','669','583','812','154','546','546','546','546','545','586','586','586','564','564','564','564','549','549','549','551','551','551','551','551','575','814','814','536','536','536','536','509','509','509','509','509','603','603','603','603','603','603','551','551','575','581','576','538','821','132','822','664','664','664','664','829','582','601','601','601','600','600','589','589','591','592','592','584','584','584','584','584','584','830','830','584','584','584','584','584','584','584','176','176','181','831','595','595','595','595','601','601','776','776','587','587','591','591','832','832','832','832','832','832','832','832','832','832','832','832','832','832','595','595','595','595','595','595','595','595','595','595','595','595','591','142','598','684','684','607','607','605','537','551','551','551','551','143','524','531','531','531','577','577','602','602','549','834','497','539','539','539','539','539','548','548','497','497','500','500','500','555','555','592','636','638','639','637','578','579','814','656','620','599','558','558','557','591','587','832','588','587','594','642','564','134','144','564','540','537','541','542','542','536','149','141','836','152','152','556','556','517','517','509','514','545','550','838','572','573','574','564','528','528','520','680','501','501','680','560','561','561','561','839','523','523','649','649','649','653','653','659','635','618','618','632','614','618','624','624','506','840','611','582','583','589','590','584','584','584','586','593','596','585','841','502','597','842','606','607','603','684','598','154','155','159','160','163','161','161','162','164','166','169','167','154','165','170','168','641','625','619','652','650','650','654','650','633','619','619','619','507','507','507','507','507','549','558','558','549','549','548','558','551','577','575','581','576','576','575','575','575','539','539','539','539','539','536','579','579','579','579','579','578','578','547','511','518','517','509','513','549','523','535','530','523','523','523','533','528','517','520','520','520','520','520','523','523','555','555','555','555','555','555','555','550','556','602','601','601','601','600','600','589','589','590','590','582','583','583','584','584','584','584','585','585','592','592','592','592','592','592','593','594','587','587','587','588','591','178','177','177','180','180','179','179','182','183','154','155','155','156','160','160','160','166','155','161','161','161','160','161','165','165','166','167','165','164','164') AND MATCH (ueberschrift,beschreibung) AGAINST ('wolle' IN BOOLEAN MODE)
ORDER BY ueberschrift ASC LIMIT 26,24
It takes about 11 seconds and show me that:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE artikel_Mode ALL NULL NULL NULL NULL 515967 Using where; Using filesort
What can i do? How can i optimize the table? The data i load in this table with LOAD DATA INFILE.
Best regards and thanks for help.
Torsten
Subject
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.