MySQL Performance: MySQL => Query Showing in LOG_QUERIES_WITHOUT

Query Showing in LOG_QUERIES_WITHOUT_INDEXES Why? [message #3099]

Mon, 19 May 2008 14:29

StephenJ
Messages: 5
Registered: March 2008

Junior Member

I have this query:

SELECT
`guild_areas`.`name`,
`guild_areas`.`show_name`,
`guild_areas`.`description`,
`guild_areas`.`area_id`,
`guild_area_config`.`guild_id`,
`guild_area_config`.`active`
FROM
`guild_area_config`
Join
`guild_areas` ON `guild_area_config`.`area_id` = `guild_areas`.`area_id`
and guild_areas.show_content_option = 0
and `guild_area_config`.`guild_id` = 35290
and guild_areas.show_content_option=0
order by
`guild_areas`.`area_id`;

Which shows this EXPLAIN:

id select_type table             type   possible_keys               key     key_len ref                       rows   Extra  
1  SIMPLE      guild_areas       index  PRIMARY,idx_covering        PRIMARY 1       NULL                      16     Using where 
1  SIMPLE      guild_area_config eq_ref PRIMARY,idx_guild,idx_area  PRIMARY 5       guild_areas.area_id,const 1

Incidentally, the query returns 9 rows. guild_areas should only match once per row in guild_are_config, so the 16 is already a bit of an oddity.

describe guild_areas; shows this

Field                Type            Null        Key          Default        Extra  
area_id              tinyint(4)      NO          PRI          NULL           auto_increment 
name                 varchar(25)     NO          MUL     
show_name            varchar(50)     YES         NULL   
description          varchar(200)    YES         NULL   
show_auth            tinyint(1)      NO       
show_content_option  tinyint(1)      NO                       0   
is_admin_auth        tinyint(4)      NO                       0   
is_widget            tinyint(4)      NO                       0

describe guild_area_config; shows this

Field       Type        Null  Key  Default  Extra  
guild_id    int(11)     NO    PRI     
area_id     tinyint(4)  NO    PRI     
active      binary(255) NO

Anyone have any idea why this query shows up in the LOG_QUERIES_WITHOUT_INDEX log? The explain seems to show it using indexes.

[Updated on: Mon, 19 May 2008 15:40]

Report message to a moderator