Optimize/fix database tables keys and indexes

As I interpret it...

I've never heard of this. Can you provide a link?

.s

This bit?

MySQL cannot use a partial index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

So sometimes the node_status_type index is not used (if the query is not using all columns in the index). But that doesn't make it obsolete: if all three columns (status, type, nid) are used in the query, then the index is used fine.

That's my reading of it.

I don't think your interpretation is correct. I don't think indexes are automatically created with left-most columns. (Order obviously matters, sometimes a multi-key index is not used, but that is no reason to drop an index.)

But I'm not an expert. At least we have fleshed out the issue and maybe someone can cast the deciding vote.

njt1982 is correct – the 'nid' and 'status' indices are redundant.

If the nid or status column is the only column in a WHERE clause, then MySQL can use the primary key (nid,vid) or node_status_type (status, type, nid) index respectively, as per the quote from the manual above.

MySQL's EXPLAIN SELECT command shows which index MySQL is using. If you drop both the 'nid' and 'status' indices ...

EXPLAIN SELECT * FROM node WHERE nid = 1;
# MySQL uses the primary key (nid,vid)

EXPLAIN SELECT * FROM node WHERE status = 1;
# MySQL uses the node_status_type index (status, type, nid)

HTH,
James Harvard

I don't know how big a performance hit it is, but that's essentially correct. Normal inserts and updates will be delayed while MySQL rewrites the indexes affected. Getting rid of them will eliminate a bunch of disk writes in many cases.

I'm not a MySQL guru by any streatch, but even I noticed complaints by phpmydamin regarding these duplicate indices and there are a couple previous bug reports:

http://drupal.org/node/59843

So is it just a matter of someone producing a patch for the /database/database.4.x.mysql files? Also I presume that an update function would be needed to drop the indices on existing installations - is that just a matter of submitting a patch too, or does one of the admins have to do it?

Right, a patch is all we need. Not sure we will apply it to 4.7, but certainly to CVS HEAD. Changing the version.

if we do not get a patch no release will ever see it. :p

We generally try to avoid database updates in the stable branch, so I am unlikely to apply a patch for 4.7.4 since it doesn't really break anything.

Normal course of action - someone with the ability and motivation may provide a patch. When and if that will happen is not guaranteed.

If you can fully enumerate the pointless keys, I'll try to turn that into a patch.

Ok, just playing with the Devel module.

On the /node/add page on of the slow queries was

12.67 0 list_themes SELECT * FROM system WHERE type = 'theme'

Looking at the schema, there is no index on the type column or status columns. Is there a downside to adding them? Is it too few rows to be helpful?

Ok, here's an initial list of indices that look pointless:

node: nid since PRIMARY is (nid,vid)

node: status since node_status_type is (status, type, nid)

term_hierarchy: tid since PRIMARY is (tid, parent)

term_node: tid since PRIMARY is (tid, nid)

phpmyadmin thows up error messages for all of these while browsing the tables.

patch attached that removes the 4 indicies listed above.

oops- stray comma in the above SQL- use this one instead

How does this affect the results of EXPLAIN ... on some relevant queries? Can you paste before and after results?

I think a couple examples are layed out already above: http://drupal.org/node/76040#comment-119974

However, here are a couple more examples:

current schema:

mysql> EXPLAIN SELECT * FROM node WHERE status = 1
+----+-------------+-------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | node  | ALL  | status,node_status_type | NULL |    NULL | NULL |  162 | Using where |
+----+-------------+-------+------+-------------------------+------+---------+------+------+-------------+

after dropping the status index:

EXPLAIN SELECT * FROM node WHERE status = 1;
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | node  | ALL  | node_status_type | NULL |    NULL | NULL |  162 | Using where |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+

current schema:

EXPLAIN SELECT * FROM node WHERE status = 1 AND nid > 50;
+----+-------------+-------+------+-------------------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys                       | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+-------------------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | node  | ALL  | PRIMARY,node_status_type,nid,status | NULL |    NULL | NULL |  162 | Using where |
+----+-------------+-------+------+-------------------------------------+------+---------+------+------+-------------+

after dropping nid and status indices:

EXPLAIN SELECT * FROM node WHERE status = 1 AND nid > 50;
+----+-------------+-------+------+--------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys            | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+--------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | node  | ALL  | PRIMARY,node_status_type | NULL |    NULL | NULL |  162 | Using where |
+----+-------------+-------+------+--------------------------+------+---------+------+------+-------------+

I think what this shows is that there is no point in having a status index at all, since it's never being used!

and finally, current schma:

EXPLAIN SELECT * FROM node n JOIN term_node tn ON tn.nid=n.nid WHERE n.status = 1 AND tn.tid = 3;
+----+-------------+-------+------+-------------------------------------+---------+---------+------------+------+--------------------------+
| id | select_type | table | type | possible_keys                       | key     | key_len | ref        | rows | Extra                    |
+----+-------------+-------+------+-------------------------------------+---------+---------+------------+------+--------------------------+
|  1 | SIMPLE      | tn    | ref  | PRIMARY,nid,tid                     | PRIMARY |       4 | const      |    7 | Using where; Using index |
|  1 | SIMPLE      | n     | ref  | PRIMARY,node_status_type,nid,status | nid     |       4 | dp1.tn.nid |    1 | Using where              |
+----+-------------+-------+------+-------------------------------------+---------+---------+------------+------+--------------------------+

deleting the 3 indices as above (n.ni, n.status, tn.tid)

EXPLAIN SELECT * FROM node n JOIN term_node tn ON tn.nid=n.nid WHERE n.status = 1 AND tn.tid = 3;
+----+-------------+-------+------+--------------------------+---------+---------+------------+------+--------------------------+
| id | select_type | table | type | possible_keys            | key     | key_len | ref        | rows | Extra                    |
+----+-------------+-------+------+--------------------------+---------+---------+------------+------+--------------------------+
|  1 | SIMPLE      | tn    | ref  | PRIMARY,nid              | PRIMARY |       4 | const      |    7 | Using where; Using index |
|  1 | SIMPLE      | n     | ref  | PRIMARY,node_status_type | PRIMARY |       4 | dp1.tn.nid |    2 | Using where              |
+----+-------------+-------+------+--------------------------+---------+---------+------------+------+--------------------------+

Here there is some difference in the outcome.

A couple more examples attached. I'm trying to decipher these results. It looks to me that mysql may have to do a little more work with these keys removed?

This is a priority!

More information about duplicate indexes:
* http://drupal.org/node/58942
* http://drupal.org/node/65456

Now that D5 has long been released and reached end-of-support, and that D8 is in development, I assume this issue can be safely closed.