MySQL Forums :: Newbie :: why my db don't use index?


Advanced Search

why my db don't use index?
Posted by: Torsten Mueller ()
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

Options: ReplyQuote


Subject Written By Posted
why my db don't use index? Torsten Mueller 07/13/2013 03:59AM
Re: why my db don't use index? John Smith 07/13/2013 09:14PM
Re: why my db don't use index? Torsten Mueller 07/14/2013 07:25AM
Re: why my db don't use index? Rick James 07/14/2013 12:26PM


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.