MySQL Explain says Key Length is 0

[meman1188]

I was wondering what this means and if it was bad.  The index is a fulltext search and the query is running reliatively slow so i was wondering how i'm investigating ways to improve it but have sort of limited knowledge of keys and mysql in general.

 

Thanks for the help

 

This is the explains:

id

select_type

table

 

type

possible_keys

key

key_len

ref

rows

Extra

   

1

   

SIMPLE

   

feeditems

   

fulltext

   

PRIMARY,feedID_pubdate,title

   

title

   

0

   

[/td]

    [td]1

   

Using where; Using temporary; Using filesort

   

1

   

SIMPLE

   

feeds

 

   

eq_ref

   

PRIMARY,cat_subcat,cat_FEEDID

   

PRIMARY

   

2

   

feeditems.feedID

   

1

 

   

Using where

 

[dreamwest]

What size are you columns your querying??

 

are they varcha,r blob, int etc

[meman1188]

Thanks for the response!

 

This is a sample query I'm running that lead to the Explain above:

SELECT `feeditems`.`ITEMID`, (MATCH(`feeditems`.`title`,`feeditems`.`tags`) AGAINST ('XXX' IN BOOLEAN MODE)*.XXX + MATCH(`feeditems`.`title`,`feeditems`.`tags`,`feeditems`.`desc`) AGAINST ('XXX' IN BOOLEAN MODE)*.XXX + `feeditems`.`pubdate`/XXX) AS `relevance` FROM `feeditems`

INNER JOIN `feeds` ON (`feeds`.`FEEDID` = `feeditems`.`feedID` AND `feeds`.`cat` != XXX AND `feeds`.`cat` != XXX) WHERE (MATCH(`feeditems`.`title`,`feeditems`.`tags`) AGAINST ('XXX' IN BOOLEAN MODE)) AND (MATCH(`feeditems`.`title`,`feeditems`.`tags`,`feeditems`.`desc`) AGAINST ('XXX' IN BOOLEAN MODE)) AND (`feeditems`.`ITEMID` != 'XXX') GROUP BY `feeditems`.`title` ORDER BY `relevance` DESC LIMIT XXX;

 

Structure:

title, tags, desc: text

pubdate: uint(10)

 

The FULLTEXT key is on:

title, tags, desc in that order

 

[dreamwest]

Does your column need to be text??

 

I would recommend changing it to VARCHAR if you can otherwise leave it as text. This will speed the query

 

The second step is to create an index of the columns your joining

 

You wont need an index for a single query but for joins you should use one, this should bring your query down to a millisecond

[meman1188]

The reason I have gone with text is because these three fields can be very large, the following are the max_lengths for the fields:

title: 442, tags: 2393, desc: 53497

Although the avg_lengths are much more accurate to an average entry:

title: 48, tags: 72, desc: 409

 

The fields used in the join to feeds are already indexed.  I'm worried about the FULLTEXT search.  I realize its an expensive query to run, but I was wondering what I can do to improve it.  It currently averages about 5 seconds.

[dreamwest]

Create an index for all the columns your joining.

 

you can do this in phpmyadmin under "Structure" then scroll down to "Indexes" the select "Create an index on x columns" and add all the columns your joining