I'm seeing simple {path_redirect} queries (in the Devel log) take ~20ms each, so I looked at the schema and realized there are no indexes. Is this intentional or an oversight? It seems like simple indexes on the most commonly queried columns would make sense.
The attached patch attempts to add them.

CommentFileSizeAuthor
#1 path_redirect-indexes.patch1011 bytesthebuckst0p
path_redirect-indexes.patch1011 bytesthebuckst0p

Comments

thebuckst0p’s picture

thebuckst0p commented
StatusFileSize
new path_redirect-indexes.patch1011 bytes

Typo in the update #, fixed

thebuckst0p’s picture

thebuckst0p commented

The 3rd db_add_index is failing, it runs the query -

ALTER TABLE {path_redirect} ADD INDEX source_redirect (source, redirect)

- and throws an error. Is the syntax wrong?

thebuckst0p’s picture

thebuckst0p commented

Error is "ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes". I'm not expert enough on mysql indexes to know the solution to that one.

dave reid’s picture

dave reid
he/him
Primary language English
Location Nebraska USA
commented
Status: Active » Needs work

We can't have an index on source and redirect fields as both fields are varchar(255) and combined would be over the index limit of 1000 bytes. 255 characters x 3 bytes per character x 2 fields = 1530 bytes.

We already have a unique index on source and language which was the main query causing problems. Why do we need additional indexes?

dave reid’s picture

dave reid
he/him
Primary language English
Location Nebraska USA
commented

aka can you give details from the slow query log or copy/paste the EXPLAIN results on which queries are causing problems on your site?

thebuckst0p’s picture

thebuckst0p commented

I'm seeing a lot of these taking around 12ms each, for example:

DELETE FROM {path_redirect} WHERE source = 'content/title' AND redirect = 'node/XXX'