If you are lazy like me, you can use the following to create a list
of all needed indexes.
The generated code is for MaxDB:
private void _printCreateIndexForModel(String modelName)
{
EOModel model =
EOModelGroup.globalModelGroup().modelNamed(modelName);
NSMutableArray<String> indexes = new
NSMutableArray<String>();
NSMutableDictionary<String, Integer> namesDict = new
NSMutableDictionary<String, Integer>();
NSMutableDictionary<String, Boolean> namesDoneDict =
new NSMutableDictionary<String, Boolean>();
for (int i = 0; i < model.entities().count(); i++)
{
EOEntity sourceEntity =
model.entities().objectAtIndex(i);
if (!sourceEntity.isAbstractEntity())
{
for (int j = 0; j <
sourceEntity.relationships().count(); j++)
{
EORelationship relationship =
sourceEntity.relationships().objectAtIndex(j);
EOEntity destEntity =
relationship.destinationEntity();
String destTableName =
destEntity.name().toUpperCase();
// Shorten the index name
if (destTableName.length() > 25)
{
destTableName = destTableName.substring(0,
25);
}
NSArray<EOAttribute> destAttributes =
relationship.destinationAttributes();
if (destAttributes.count() == 1 &&
destEntity.primaryKeyAttributes().contains(destAttributes.get(0)))
{
// PKs should have an index
continue;
}
NSMutableArray<String> relKeys = new
NSMutableArray<String>();
for (int k = 0; k < destAttributes.count();
k++)
{
EOAttribute attribute =
destAttributes.objectAtIndex(k);
relKeys.add(attribute.columnName());
}
String keys =
relKeys.componentsJoinedByString(", ");
if (namesDoneDict.get(destTableName + "!" +
keys) != null)
{
namesDoneDict.setObjectForKey(Boolean.TRUE,
destTableName + "!" + keys);
}
// Make the index name unique
int indexCount = (namesDict.get(destTableName)
!= null) ? namesDict.get(destTableName).intValue() + 1 : 1;
namesDict.setObjectForKey(indexCount,
destTableName);
String indexName = "I_" + destTableName + "_" +
indexCount;
indexes.add("CREATE INDEX " +
indexName.replace('.', '_') + " ON " + destTableName + " (" + keys +
")");
}
}
}
NSArray<String>sortedIndexes =
OrderGermanString.sortStringArray(indexes);
NSLog.debug.appendln(sortedIndexes.componentsJoinedByString("\n//\n")
+ "\n//\ncommit\n//\n");
}
Stefan
Am 06.03.12 21:10, schrieb Kieran Kelleher:
If it was me with this task, I would create a simple Excel sheet with 3 columns, generate the SQL with a fill down formula for the simple single columns indexes. Paste all the formulas in a text file and just execute it. However, as Lachlan suggested, do it on a developer copy of the real db first to make sure that you understand how long the process will take.
Example excel file attached........
On Mar 6, 2012, at 1:42 PM, Jesse Tayler wrote:
oh!
just so as I can see things more easily eh?
whew.
I'm concerned that if we are unable to make decent indexes automatically, I'll do a terrible job at it by hand!
I'd go in and basically setup indexes where I see foreign keys, my database isn't fancy, but what else can or should I look to do or avoid while cleaning this up on my model?
I have maybe 20 entities and likely 50 relationships I'm sure.
On Mar 6, 2012, at 1:34 PM, David LeBer <email@hidden> wrote:
Jesse,
I don't think anyone said anything about dumping your data.
Kieran said dump your 'schema' not data.
--
David LeBer
Codeferous Software
On 2012-03-06, at 12:57 PM, Jesse Tayler wrote:
why would I have to dump my data just to create the indexes?
should I not be able to create indexes on the fly on mysql?
and yes, this is my first mysql setup and no, I did NOT realize fully that I had to create indexes - some have been, other not, it seems...
anyone have a nice - simple WO-person's explanation of indexing strategy? especially needs for things like a data dump/restore or these attributed correlations spanning multiple entities and supporting/troubleshooting queries?
On Mar 6, 2012, at 12:00 PM, Theodore Petrosky <email@hidden> wrote:
This conversation has piqued my interest.
I just looked at my postgresql database to see what indexes are created in a 'normal' migration and I was happy to see that the foreign key did get an index:
Indexes:
"person_pk" PRIMARY KEY, btree (id)
"person_erattachmentid_idx" btree (erattachmentid)
Foreign-key constraints:
"person_erattachmentid_id_fk" FOREIGN KEY (erattachmentid) REFERENCES erattachment(id) DEFERRABLE INITIALLY DEFERRED
inquiring minds need to know
------------------------------
Message: 6
Date: Tue, 06 Mar 2012 11:16:55 -0500
From: Kieran Kelleher <email@hidden>
To: Jesse Tayler <email@hidden>
Cc: WebObjects Development <email@hidden>
Subject: Re: EOQualifier proper fetch across to-many?
Message-ID: email@hidden"><email@hidden>
Content-Type: text/plain; charset="utf-8"
Whoa..... yes, YOU MUST create foreign key indexes yourself
in MySQL! (The auto SQL from EntityModeler does not do it
for you since creating true foreign key constraints in MySQL
is a rat's nest of problems due to the lack of the most
desired feature that MySQL lacks currently (deferred
constraints)
Dump a schema (mysqldump --no-data > schema.sql) of your
db and highlight all FKs that need indexes and create them
asap ..... your performance on relationships will soar on
larger tables.
As a rule, I create FK indexes on every table - would not
give it a second thought not to create them.
Also, on the many-to-many relationship "join table", the
default SQL will have created the compound PK using the two
FK fields, however you should also create a INDEX with the
two same keys in the opposite order..... for example, if
your join table has two fields A and B, then the compound PK
might be (A,B) in which case you need to add another index
based on (B,A)
HTH, Kieran
On Mar 6, 2012, at 11:03 AM, Jesse Tayler wrote:
oh, the fetch kills the database alright -- I'll
attempt to fix with indexes, but I've had mixed luck with
that.
I notice there's not all the indexes I'd expect on
foreign keys? mysql have anything funny there? or I should
have at least an index for each foreign key, no?
On Mar 6, 2012, at 8:48 AM, Kieran Kelleher <email@hidden>
wrote:
Prematurely looking for a fetch solution that does
not overkill the database when the we don't know if the
fetch overkills the database yet. :-)
Regards Kieran
___________________________
Sent from my iPad.
On Mar 5, 2012, at 9:44 PM, Paul Yu <email@hidden>
wrote:
Premature what?
--
Paul Yu
Sent with Sparrow
On Monday, March 5, 2012 at 8:55 PM, Kieran
Kelleher wrote:
Donald Knuth once said "premature
optimization is the root of all evil" :-)
Try it out before assuming the performance
is bad. If your tables have the needed indexes it should be
fine.
If performance is bad, log the generated
SQL and just apply whatever tools you have at your disposal
for your database platform to figure out the problem (index,
join buffer size, etc.)
Regards Kieran
___________________________
Sent from my iPad.
On Mar 5, 2012, at 3:43 PM, Jesse Tayler
<email@hidden>
wrote:
is there a proper way to fetch across a
to-many and not overkill the database?
if I wanted to return a list of
recently used venues that the user has associated with posts
they have authored, I'd want a distinct return of venues,
each having a post->author being the user, but this query
like this would just churn on the database wouldn't it?
I didn't see a "distinct" wonder fetch
property either, don't I have to use something to ensure the
list is returned without duplicates?
EOQualifier qual =
Venue.POSTS.dot(Post.AUTHOR_KEY).eq(user());
ERXRestFetchSpecification<Venue>
fetchSpec = new
ERXRestFetchSpecification<Venue>(Venue.ENTITY_NAME,
qual, null, queryFilter(), Venue.CREATED.descs(), 25);
what's the best practice on that kind
of fetch?
_______________________________________________
Do not post admin requests to the list.
They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
|