Speeding up queries to conv_link table...
Submitted Alexia Smith, Jul 23 2010 10:29 AM | Last updated Jul 23 2010 10:29 AM
I went through since I was annoyed with how slow queries were to the conv_link table. This may not help small forums, but with the current forum I am converting pushing five million entries in the conv_link table it really helps.
1.) Add an index to the type field. This has the immediate effect of going from looking at all entries in the table to just the type being search for. This cut queries down from 2 seconds down to 0.2 seconds.
2.) Change foreign_id from varchar(32) to int(8). This cut the query time down from the remaining 0.2 to 0.0006 seconds. This will be an issue for any conversions that encounter a foreign_id that is not an integer. However, in converting multiple vBulletin, phpBB, and DragonflyCMS installations I have never encountered any non-integer values in that field.
Both the above steps have massively sped up the conversion process for me.
1.) Add an index to the type field. This has the immediate effect of going from looking at all entries in the table to just the type being search for. This cut queries down from 2 seconds down to 0.2 seconds.
2.) Change foreign_id from varchar(32) to int(8). This cut the query time down from the remaining 0.2 to 0.0006 seconds. This will be an issue for any conversions that encounter a foreign_id that is not an integer. However, in converting multiple vBulletin, phpBB, and DragonflyCMS installations I have never encountered any non-integer values in that field.
Both the above steps have massively sped up the conversion process for me.
| Status: | Not a Bug |
| Version: | |
| Fixed In: |
2 Comments
Hello, while I appreciate your suggestions and that they may have worked in your case they will not work for all converters. For #1, I do agree that add an index for the type field will help, I will go ahead and have that added. As for #2, I cannot make this field an integer as there are a few applications that do not provide an integer id and do contain some char types. Thank you for your suggestions.