Can multicolumn indexes include the primary key

2005

I am trying to create indexes for queries as in this example:

mysql_query("SELECT total_number FROM weblinks where id='$id'");
mysql_query("SELECT total_number FROM weblinks where id='$id' and activated='1'");
mysql_query("SELECT total_number FROM weblinks where id='$id' and activated='1' and adult='0'");

I created the following table and indexes:

CREATE TABLE `weblinks` (
  `id` int(11) NOT NULL auto_increment,
  `total_number` int(11) NOT NULL default '0',
  `activated` tinyint(1) NOT NULL default '0',
  `adult` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `id` (`id`,`activated`),
  KEY `id_2` (`id`,`activated`,`adult`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

PhpMyAdmin shows the following errors:

PRIMARY and INDEX keys should not both be set for column `id`
More than one INDEX key was created for column `id`

Does it mean that multicolumn indexes cannot include the primary key? What will be the correct indexes for queries like in my example?

Thanks.

johanafm

Yes, you can indeed create several indexes for the same column, just as you've done. I have no idea why it does not work for you. Outdated MySQL version? Or using a new one containing a bug?

ALso, the primary key can be multicolumn, and if it's not, there is no point in adding it to a multicolumn index for the queries you are executing above, since the primary key is always unique. Consider:

SELECT total_number FROM weblinks where id='$id'

This ALWAYS retrieves either 1 or 0 rows. If the id exists, it exists only once, and then you get 1 row.

SELECT total_number FROM weblinks where id='$id' AND lots="of other things" AND then="some more things"

Will use the index for the primary key, find one row and then check the other where constraints to see if the row should be returned in the result set.

Execute this statement (once you have all your indices up), and replace 999 with an id that actually has activated=1 and adult=0

EXPLAIN SELECT id FROM weblinks WHERE id=999 and activated=1 and adult=0

Possible keys: primary, id, id_2
key: primary
Which tells you that the multicolumn indexing is pointless.

However, if you need to do range checks, it can be beneficial

EXPLAIN SELECT id FROM weblinks WHERE id<5000 and activated=1 and adult=0

possible keys: primary, id, id_2
key: id_2
And now there was finally a use for the index id_2

The point is, don't create an index unless it will actually be used (and then it is still sometimes better to not have it).

2005

johanafm, thank you for your reply. You explained a lot.

My Mysql runtime information shows different problems. One of them:

47 k
The number of joins that do not use indexes. 
If this value is not 0, you should carefully 
check the indexes of your tables.

I enabled logging queries which do not use indexes and log file shows a lot of queries like this:

SELECT u.id, u.comments, u.shorttext FROM blog u, usertable s 
WHERE u.uid=s.id and s.open_blog='1' 
order by u.id DESC limit 0,7;

column uid is primary key in usertable
column id is primary key in blog table
column open_blog is indexed

What more indexes should I create here that will be used? If none, the value of joins that do not use indexes in runtime info never can be 0.

johanafm

Either you're mixing things up when joining tables or when telling me about the keys.

column uid is primary key in usertable
column id is primary key in blog table
column open_blog is indexed

At the same time, you

s.id = u.uid

So, I would guess you mean that id is primary key in usertable.

What's not indexed is blog.uid. Add index on blog.uid, then run explain on your query again.

Also, I'd recommend writing your queries like this

SELECT u.id, u.comments, u.shorttext
FROM blog AS u,
	INNER JOIN usertable AS s ON u.uid = s.id
WHERE s.open_blog='1'
order by u.id DESC limit 0,7;

... when you are doing a join and want to impose a constraint on the join itself. Especially when you have long complex queries, this will allow you to easily see what actually happens instead of sifting through huge were clauses.

2005

I am sorry, the correct situation is:

column id is primary key in usertable
column uid in blog table is indexed
column open_blog in usertable is indexed

So, all "where" columns are indexed, but the query doesn't use indexes.

Yes, I am going to rewrite queries using INNER JOIN, however as I know there is no difference in performance when using "where" and "INNER JOIN".

johanafm

Well, if (from the query above) all of u.uid, s.id and s.open_blog are indexed (primary keys are always), then you query should use indexing.

What's the output of explain on that query?

Bjom

Yes, I am going to rewrite queries using INNER JOIN, however as I know there is no difference in performance when using "where" and "INNER JOIN".

That's a matter of preference - usually the optimizer will end up using the same execution plan, just don't mix styles.

2005

Thank you for your replies. Here is the EXPLAIN result. I don't know a lot about EXPLAIN. Is the result OK?

+----+-------------+-------+--------+------------------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type   | possible_keys          | key     | key_len | ref            | rows | Extra       |
+----+-------------+-------+--------+------------------------+---------+---------+----------------+------+-------------+
|  1 | SIMPLE      | u     | index  | uid                    | PRIMARY | 8       | NULL           | 1508 |             |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY,open_blog      | PRIMARY | 4       | datab3.u.uid   |    1 | Using where |
+----+-------------+-------+--------+------------------------+---------+---------+----------------+------+-------------+

One more question? Whould I create indexes on ORDER BY columns?

Bjom, what do you mean with "just don't mix styles"?

Bjom

Looks ok to me. Here is something about explain, that I found useful:

EXPLAIN explained

About indexing of the order by thing: interesting question - I haven't paid attention to that yet - a quick search on the web led me to this article on the mysql performance blog, that I'll have to read myself when I find the time 🙂

index and order by

There is a ton of good info on that site btw.

Mixing of styles: I mean don't use a JOIN (ansi) join and a WHERE (comma) join in the same query. (I believe mysql wont even accept it)
Bjom