index question

jonno946

Hi there,

I have the following query:

SELECT link_id FROM links WHERE link_code = 230685 AND link_source_id = 12 LIMIT 0 , 1

and a multi index on link_code and link_source_id:

ALTER TABLE links ADD INDEX test (link_code, link_source_id);

however, if i run it through EXPLAIN it is ignoring the index and doing a whole table scan? am i missing something?

Thanks,

Jonathan.

bogu

And what are u trying to do?

jonno946

bogu wrote:
And what are u trying to do?

index the above fields to make the query run faster and more efficiently?

bogu

And this query:

ALTER TABLE links ADD INDEX test (link_code, link_source_id);

need a long time to be execute?
Do u have lots of records in that table?
Or is this query, that need a long time to be execute?

SELECT link_id FROM links WHERE link_code = 230685 AND link_source_id = 12 LIMIT 0 , 1

bogu

I just made a test for u:

I have a table with lots of records (something like 500k records), I alter the table to create index on a field and got executed in 30 seconds, so altering the table this way is working very slow ...

jonno946

bogu wrote:
And this query:
ALTER TABLE links ADD INDEX test (link_code, link_source_id);
need a long time to be execute?
Do u have lots of records in that table?
Or is this query, that need a long time to be execute?
SELECT link_id FROM links WHERE link_code = 230685 AND link_source_id = 12 LIMIT 0 , 1

yes, the query above is ignoring the index for some reason and instead doing a full table scan.

bogu

Even if u got lots of records your select should run fast

SELECT link_id FROM links WHERE link_code = 230685 AND link_source_id = 12 LIMIT 0 , 1

Why do u limit your select, isnt link_source_id or link_code (unic) in your table?

Anyway your select should be real fast even if u got lots of records in your table, maybe your script is slower somewere else ...

jonno946

bogu wrote:
Even if u got lots of records your select should run fast
SELECT link_id FROM links WHERE link_code = 230685 AND link_source_id = 12 LIMIT 0 , 1
Why do u limit your select, isnt link_source_id or link_code (unic) in your table?

Anyway your select should be real fast even if u got lots of records in your table, maybe your script is slower somewere else ...

there is no problem with the speed of the query, resources are my main concern.

the fact is mysql is ignoring the index which means mysql is having to go through each and every row 1 by one.

bogu

And if u run it through EXPLAIN it show u that it is doing a whole table scan? and how did u realize that?

table
type
possible_keys
key
key_len
ref
rows
Extra

This is the result u get from EXPLAIN ...

jonno946

bogu wrote:
And if u run it through EXPLAIN it show u that it is doing a whole table scan? and how did u realize that?

table
type
possible_keys
key
key_len
ref
rows
Extra

This is the result u get from EXPLAIN ...

table type possible_keys key key_len ref rows Extra
links ALL link_code NULL NULL NULL 250781 Using where

bogu

Can u put your table structure here? something is fishy...

bogu

This your problem:

The key column indicates the key (index) that MySQL actually decided to use. The key is NULL if no index was chosen.

but I dont know why ...

jonno946

it's quite a large table so i've missed out the irrelivent fields:

CREATE TABLE `links` (
  `link_id` int(11) unsigned NOT NULL auto_increment,
  `link_source_id` int(11) unsigned NOT NULL default '0',
  `link_code` varchar(255) NOT NULL default '0',
  `link_url` varchar(255) NOT NULL default '',
  `link_status` varchar(255) NOT NULL default '',
[.. more simular irrelivent fields ..]
  PRIMARY KEY  (`link_id`),
  KEY `link_status` (`link_status`),
  KEY `link_code` (`link_code`,`link_source_id`)
);

bogu

Is bad to index by both link_code and link_source_id, u should index by only one of them ...

jonno946

bogu wrote:
Is bad to index by both link_code and link_source_id, u should index by only one of them ...

why do you say this?

bogu

This is from mysql manual:

If a range scan is possible on some key, Index Merge will not be considered. For example, consider this query:

SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

For this query, two plans are possible:

An Index Merge scan using the (goodkey1 < 10 OR goodkey2 < 20) condition.

A range scan using the badkey < 30 condition.