indexes problem?

MySQL & MariaDB Other MySQL® Questions
1

Hello all,

here is the query I’m executing:
mysql> explain SELECT profile_id FROM search_region AS p FORCE INDEX (region_prof_links) WHERE region_id=3866 ORDER BY links DESC;
±—±------------±------±-----±------------------±---- --------------±--------±------±---------±--------------- --------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±------------------±---- --------------±--------±------±---------±--------------- --------------------------+
| 1 | SIMPLE | p | ref | region_prof_links | region_prof_links | 5 | const | 12758122 | Using where; Using index; Using filesort |
±—±------------±------±-----±------------------±---- --------------±--------±------±---------±--------------- --------------------------+

and as you can see its not really greate cause it takes about 250 sec to execute…

Here are the indexes for the table search_region:

mysql> show index from search_region;
±--------------±-----------±-------------------------±-- -----------±------------±----------±------------±------- --±-------±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±--------------±-----------±-------------------------±-- -----------±------------±----------±------------±------- --±-------±-----±-----------±--------+
| search_region | 0 | region_profid | 1 | region_id | A | 18 | NULL | NULL | YES | BTREE | |
| search_region | 0 | region_profid | 2 | profile_id | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | prof_id | 1 | profile_id | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | region_prof_links | 1 | region_id | A | 18 | NULL | NULL | YES | BTREE | |
| search_region | 1 | region_prof_links | 2 | profile_id | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | region_prof_links | 3 | links | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_country_region_links | 1 | first_name | A | 18 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_country_region_links | 2 | country_id | A | 18 | NULL | NULL | | BTREE | |
| search_region | 1 | f_country_region_links | 3 | region_id | A | 74304 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_country_region_links | 4 | links | A | 521404 | NULL | NULL | YES | BTREE | |
| search_region | 1 | l_country_region_links | 1 | last_name | A | 30502140 | NULL | NULL | YES | BTREE | |
| search_region | 1 | l_country_region_links | 2 | country_id | A | 30502140 | NULL | NULL | | BTREE | |
| search_region | 1 | l_country_region_links | 3 | region_id | A | 61004281 | NULL | NULL | YES | BTREE | |
| search_region | 1 | l_country_region_links | 4 | links | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_l_country_region_links | 1 | first_name | A | 18 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_l_country_region_links | 2 | last_name | A | 61004281 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_l_country_region_links | 3 | country_id | A | 61004281 | NULL | NULL | | BTREE | |
| search_region | 1 | f_l_country_region_links | 4 | region_id | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_l_country_region_links | 5 | links | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_profid_region | 1 | first_name | A | 316905 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_profid_region | 2 | profile_id | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_profid_region | 3 | region_id | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | l_profid_region | 1 | last_name | A | 968321 | NULL | NULL | YES | BTREE | |
| search_region | 1 | l_profid_region | 2 | profile_id | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | l_profid_region | 3 | region_id | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_l_profid_region | 1 | first_name | A | 1544412 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_l_profid_region | 2 | last_name | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_l_profid_region | 3 | profile_id | A | 122008563 | NULL | NULL | YES | BTREE | |
| search_region | 1 | f_l_profid_region | 4 | region_id | A | 122008563 | NULL | NULL | YES | BTREE | |
±--------------±-----------±-------------------------±-- -----------±------------±----------±------------±------- --±-------±-----±-----------±--------+
29 rows in set (6.33 sec)

Any suggestions ? :frowning:

2

What index does the optimizer choose when you don’t force one?

Also, have you tried an index that is just region_id, links?

3

hey there,

The Optimizer is using the same index.

And yes, i have tried the index you suggested and works a lot better BUT i need to make sure i’m selecting ’ * ’ instead of just profile_id AND add ’ and links > 0 '; this way it uses index which is pretty weird :-/
One more thing here, i just tried adding index (region_id, links, profile_id) and ran this query:

ysql> explain SELECT region_id, links, profile_id FROM search_region WHERE region_id=3866 and links > 0 ORDER BY links DESC limit 0, 10;
±—±------------±------±-----±------------------±---- --------------±--------±------±---------±--------------- --------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±------------------±---- --------------±--------±------±---------±--------------- --------------------------+
| 1 | SIMPLE | p | ref | region_links_profid | region_links_profid | 5 | const | 27085 | Using where; Using index; |
±—±------------±------±-----±------------------±---- --------------±--------±------±---------±--------------- --------------------------+

really interesting )

but thanx for the reply

4

It seems to me you have a huge number of rows in that table. And if you are going to select ‘*’ - how many columns is that? And what storage engine are you using for the table?

Maybe you can show us output of create table statement for that table.

5
[B]haaseg wrote on Fri, 18 April 2008 20:27[/B]
It seems to me you have a huge number of rows in that table. And if you are going to select '*' - how many columns is that? And what storage engine are you using for the table?

Maybe you can show us output of create table statement for that table.

here you go:

CREATE TABLE search_region (
region_id int(11) default NULL,
profile_id int(11) default NULL,
country_id int(11) NOT NULL,
links int(3) default NULL,
first_name int(cool: default NULL,
last_name int(cool: default NULL,
UNIQUE KEY region_profid (region_id,profile_id),
KEY prof_id (profile_id),
KEY region_prof_links (region_id,profile_id,links),
KEY f_country_region_links (first_name,country_id,region_id,links),
KEY l_country_region_links (last_name,country_id,region_id,links),
KEY f_l_country_region_links (first_name,last_name,country_id,region_id,links),
KEY f_profid_region (first_name,profile_id,region_id),
KEY l_profid_region (last_name,profile_id,region_id),
KEY f_l_profid_region (first_name,last_name,profile_id,region_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

6
[B]haaseg wrote on Fri, 18 April 2008 20:27[/B]
It seems to me you have a huge number of rows in that table. And if you are going to select '*' - how many columns is that? And what storage engine are you using for the table?

Maybe you can show us output of create table statement for that table.

btw, i'm selecting 10 at a time so its not a big deal i think