Adding indexes to modx core tables.

I have query for a user export, there are about 6000 users in this particular modx database,




It runs VERY slow, about 27 seconds, after a bit of digging around, I find that the member column in the modx_member_groups table is not indexed.

Adding an index to that column (member, member, Normal, BTREE) the query now takes under a second. Great! So that seems fine, but:


- are there any drawbacks or issues I may encounter after adding the index?
- will updates to modx remove the index?
- why isn't this field indexed in the first place? Seems like it should be!

-thanks
-sean

An update won't remove the index. Updates generally only add fields if necessary. It's the core files that get replaced, overwriting any changes. The index won't be reflected in the schema files, though, and those would get replaced during an upgrade. Since you're not using any API functions that won't make a difference.

I would be interested to see how fast using pdoUsers would be in generating your export file. http://docs.modx.pro/en/components/pdotools/snippets/pdousers [ed. note: sottwell last edited this post 11 years, 8 months ago.]

Quote from: sottwell at Jan 25, 2015, 09:39 PM

An update won't't remove the index. Updates generally only add fields if necessary. It's the core files that get replaced, overwriting any changes. The index won't be reflected in the schema files, though, and those would get replaced during an upgrade. Since you're not using any API functions that won't make a difference.

I would be interested to see how fast using pdoUsers would be in generating your export file. http://docs.modx.pro/en/components/pdotools/snippets/pdousers

the problem with any plugins for user export is that I need to:

1. break out all the extended fields into columns [not so big a deal - there are about 30 of them]
2. break out all the user groups into columns AND insert a little TRUE/FALSE for groups they belong/don't belong there are 28 groups.

So generally Extras don't have what I need and start to break down with the number of users [just over 6000] all the extra object processing and view rendering needs to go!

Right now I have the export doing all this using the phpexcel library and have it down to just under 30 seconds. ~ I believe that's as good as it gets.



Now, whether or not the index is in the schema, does it really matter? I would assume the performance boost from the index is handled at the database level. [also assuming that modx/xpdo is joining that table on the member column at some point....]

Now, whether or not the index is in the schema, does it really matter? I would assume the performance boost from the index is handled at the database level. [also assuming that modx/xpdo is joining that table on the member column at some point....]

You are probably correct. I don't know all that much about how the xPDO schemas work internally.

During normal MODX operations, the schema is completely ignored, since all the information it contains is already in the class and map files. It might be used during setup, but I don't think so, and even then I'm pretty sure it wouldn't remove an existing index.

You might check out the PeopleGroups snippet for the group part (part of the Peoples package).

Using prepare() might, or might not speed it up. And select * might possibly help, since MySQL wouldn't have to test the field names.

Exporting a prepared query on an Evo site takes me less than 2 seconds to export over 1,000 users on a localhost site even with some extensive postprocessing of the data (but with a SSD drive). If you're on a shared server, the MySQL server might be overloaded.