Add Index to Table/Field cas_user.uid to Prevent Slow Queries

I wonder if we can get some data on the speed improvement offered by this patch. Also, which queries were causing the problem? It could be there are code architecture improvements that could be made to solve the problem, in addition to (or instead of) just slapping an index on it.

Currently with our hardware and table size, it is taking an average of 33 ms for the query (it's hitting our slow query due to log-queries-not-using-indexes). This may not sound like a lot but that's an extra .03 seconds on any page load that loads a user object (and of course for pages that load many users, like a blog listing, that's several 30ms hits which adds up fast). As our user base grows, this will get slower, and I'm sure there are organizations like universities that may want to implement logins with far larger user databases than I.

Mark's output above was from pt-query-report, here is the top part with better columns from a different day. You can ignore the pct column, as that is percentage of across all queries in our report that day. Exec time row's min, max, avg, and 95th percentile are the key items there.

# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3    9317
# Exec time     57    311s    13ms   125ms    33ms    87ms    22ms    23ms
# Lock time      1   360ms    20us    12ms    38us    44us   121us    33us
# Rows sent      0   8.11k       0       1    0.89    0.99    0.31    0.99
# Rows examine  86 721.95M  56.46k 213.48k  79.35k 211.82k  53.11k  56.74k
# Query size     0 464.55k      48      53   51.06   51.63    2.07   51.63

Oh, and the performance improvement is from the old 13-125ms to a new 0.00 sec.

I think that this is a reasonable request, however, I noticed that in the patch, you call the index cas_user in one place and UID in another. Also, to improve upgradability it would be best to add a test to see if the index exists prior to adding it in the update hook. Would you mind resubmitting this patch with those mods?

Thanks

Dave

Looks good to me... I will work on committing these today.

Committed and pushed to 7.x-1.x and 6.x-3.x branches. I made a small alteration to the latest 6.x patch so that it was using the same index name as the 7.x patch.