Loot

[mollymillions]

Although the spawns and paths are excellent in the PEQ_Kunark db, the loot's a little slim, most Live type quests can't be completed, so I referenced old databases (by Dwarde and MW) and wrote a query to add the loot to the existing loot. Heres a link to the query to insert the loot, use it at your own risk and be warned that it will add heaps of loot. The query will produce many errors as some of the item drops already exist.

http://members.dodo.net.au/~mollymillions/loot+.zip



Are there any SQL gurus that can suggest how to speed up this query (used to create the queries to merge the data form db EQ1 into EQ)?

select distinct
/*npc_types*/
concat('update npc_types set loottable_id = ',
if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.loottable_entries.loottable_id, eq.loottable_entries.loottable_id),
' where id = ', eq.npc_types.id, ';') as npc_types1
/*loottable*/
,concat('insert into loottable values(',
if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.loottable_entries.loottable_id, eq.loottable_entries.loottable_id), ',',
char(39),if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.npc_types.name, eq.npc_types.name), char(39),
',0,0,0);') as loottable1,
/*loottable_entries*/
concat('insert into loottable_entries values(',
if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.loottable_entries.loottable_id, eq.loottable_entries.loottable_id), ',',
if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.loottable_entries.lootdrop_id, eq.loottable_entries.lootdrop_id), ',',
if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.loottable_entries.multiplier, eq.loottable_entries.multiplier), ',',
if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.loottable_entries.probability, eq.loottable_entries.probability),
');') as loottable_entries1,
/*lootdrop*/
concat('insert into lootdrop values(',
if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.lootdrop_entries.lootdrop_id, eq.lootdrop_entries.lootdrop_id), ',',
char(39),if(isnull(eq.lootdrop_entries.lootdrop_id), concat(eq1.loottable_entries.loottable_id, '_', eq1.npc_types.name), concat(eq.loottable_entries.loottable_id, '_', eq.npc_types.name)), char(39),
');') as lootdrop1,
/*lootdrop_entries*/
concat('insert into lootdrop_entries values(',
if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.lootdrop_entries.lootdrop_id, eq.lootdrop_entries.lootdrop_id), ',',
if(isnull(eq.lootdrop_entries.lootdrop_id), eq1.lootdrop_entries.item_id, eq.lootdrop_entries.item_id), ',',
if(isnull(eq1.lootdrop_entries.lootdrop_id), eq.lootdrop_entries.item_charges, eq1.lootdrop_entries.item_charges), ',',
if(isnull(eq1.lootdrop_entries.lootdrop_id), eq.lootdrop_entries.equip_item, eq1.lootdrop_entries.equip_item), ',',
if(isnull(eq1.lootdrop_entries.lootdrop_id), eq.lootdrop_entries.chance, eq1.lootdrop_entries.chance),
');') as lootdrop_entries1
from eq1.npc_types, eq1.spawnentry, eq1.spawn2, eq.npc_types, eq.spawnentry, eq.spawn2
left join eq1.loottable_entries on eq1.npc_types.loottable_id = eq1.loottable_entries.loottable_id
left join eq1.lootdrop_entries on eq1.loottable_entries.lootdrop_id = eq1.lootdrop_entries.lootdrop_id
left join eq.loottable_entries on eq.npc_types.loottable_id = eq.loottable_entries.loottable_id
left join eq.lootdrop_entries on eq.loottable_entries.lootdrop_id = eq.lootdrop_entries.lootdrop_id
where eq1.npc_types.id = eq1.spawnentry.npcid
and eq1.spawnentry.spawngroupid = eq1.spawn2.spawngroupid
and eq.npc_types.id = eq.spawnentry.npcid
and eq.spawnentry.spawngroupid = eq.spawn2.spawngroupid
and eq.npc_types.name = eq1.npc_types.name
and eq.spawn2.zone = eq1.spawn2.zone
/*and eq1.spawn2.zone = 'oot'*/
and not isnull(eq1.lootdrop_entries.item_id)
and not (isnull(eq1.lootdrop_entries.item_id) and isnull(eq.lootdrop_entries.item_id))
order by eq1.npc_types.name