Hi Achim, On 04.05.2012 22:47, Joachim Astel wrote:
The fast way - create the indexes on the first 200 chars (mysql offers that option). I assume that it is a safe assumption, that index searches are always done on scalars which wont be longer than that.Hi OpenXPKI-folks,The following is an excerpt from our database init script for one of our Oracle installations. In particular, have a look at the indices on the workflow_context table. CREATE INDEX wf_state_state on workflow (workflow_state); CREATE INDEX cert_realm_index on certificate (pki_realm); CREATE INDEX csr_profile_index on csr (profile); CREATE INDEX csr_role_index on csr (role); CREATE INDEX cert_csrid_index on certificate (req_key); CREATE INDEX wf_realm_index on workflow (pki_realm); CREATE INDEX csr_subject_index on csr (subject); CREATE INDEX wf_context_value_index on workflow_context (workflow_context_value); CREATE INDEX cert_role_index on certificate (role); CREATE INDEX wf_hist_wfserial_index on workflow_history (workflow_id); CREATE INDEX wf_context_key_index on workflow_context (workflow_context_key); CREATE INDEX wf_type_index on workflow (workflow_type); CREATE INDEX cert_subject_index on certificate (subject); CREATE INDEX cert_identifier_index on certificate (identifier); CREATE INDEX cert_status_index on certificate (status); CREATE INDEX cert_attributes_key_index on certificate_attributes (attribute_contentkey); CREATE INDEX cert_attributes_value_index on certificate_attributes (attribute_value);I've tried this with MySQL as backend database, and ran into the following index creation problems: CREATE INDEX csr_profile_index on csr (profile); -> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'profile' used in key specification without a key length (profile is a "text" field without length restriction.) CREATE INDEX csr_role_index on csr (role); -> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'role' used in key specification without a key length (role is a "text" field without length restriction.) CREATE INDEX wf_context_value_index on workflow_context (workflow_context_value); -> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'workflow_context_value' used in key specification without a key length (workflow_context_value is a "text" field without length restriction.) CREATE INDEX cert_role_index on certificate (role); -> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'role' used in key specification without a key length (role is a "text" field without length restriction.) CREATE INDEX cert_status_index on certificate (status); -> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'status' used in key specification without a key length (status is a "text" field without length restriction.) CREATE INDEX cert_attributes_value_index on certificate_attributes (attribute_value); -> ERROR 1170 (42000) at line 1: BLOB/TEXT column 'attribute_value' used in key specification without a key length (attribute_value is a "longtext" field without length restriction.) Is there a way workarounding this MySQL behaviour in not indexingnon-specified field lenghts of TEXT and LONGTEXT fields?>
It should be also safe to shrink the role/profile/status fields to something like tinytext or varchar 255 - the default roles and status names are around are around 10 chars long, the longest profile name is 49 chars, as long as you did not choose to make very bulky names, that should do the trick.
As the problem with the context_value column is even worse on oracle, a proper solution is on the to-do list - so if anybody has some better ideas, just shoot! Oli -- Protect your environment - close windows and adopt a penguin! PGP-Key: 3B2C 8095 A7DF 8BB5 2CFF 8168 CAB7 B0DD 3985 1721
smime.p7s
Description: S/MIME Cryptographic Signature
------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________ OpenXPKI-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openxpki-devel
