What would I index to optimize this query?

Discussion in 'Databases' started by bigrollerdave, Jun 13, 2008.

0
  1. #1
    SELECT category_name, type , filename, description
    FROM Media
    WHERE (tags LIKE 'test')
    OR (description LIKE 'test')
    ORDER BY ordernum DESC

    When I run the explain the key, key_len and red are all null

    Thanks
     
    bigrollerdave, Jun 13, 2008 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    What are the data types of the columns?

    Generally you put indexes on columns that are in your where clause. However, it isn't always a good idea to put an index on a text field because it greatly increases the database size and may not improve the query speed at all. From your query, the `tags` column would be my first inclination, but it is always better to be using numeric fields (int, smallint, etc..) when indexing.
     
    jestep, Jun 13, 2008 IP
  3. bigrollerdave

    bigrollerdave Well-Known Member

    Messages:
    2,112
    Likes Received:
    52
    Best Answers:
    0
    Trophy Points:
    140
    #3
    Yeah I tried to put an index on both the where clauses but it still said null for the key here are the datatypes

    tags varchar(100)
    description varchar(200)

    Here is what I tried to do

    ALTER TABLE `Media` ADD INDEX ( `description` , `tags` )

    And it produced this 
    
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	Media 	ALL 	NULL 	NULL 	NULL 	NULL 	1723 	Using where; Using filesort
    Code (markup):
     
    bigrollerdave, Jun 13, 2008 IP
  4. bigrollerdave

    bigrollerdave Well-Known Member

    Messages:
    2,112
    Likes Received:
    52
    Best Answers:
    0
    Trophy Points:
    140
    #4
    Anyone have any ideas?
     
    bigrollerdave, Jun 19, 2008 IP