MySql Database issues

[sesmar]

Does anyone else have trouble running what seems to be simple queries in MySql?

I going through and trying to clean up some stuff in the datbase. Particularly faction tables right now. My Problem is that any of the queries that I want to run to give me the information I need just lock up my server. These queries are really simple and should not have this effect esp when I am able to run what seems to be more complicated queries.

For example:


SELECT
npc_faction_id,
npc_faction.name
FROM
npc_types
INNER JOIN spawnentry
ON npcID=npc_types.id
INNER JOIN spawn2
ON spawnentry.spawngroupID=spawn2.spawngroupID
INNER JOIN npc_faction
ON npc_faction.id=npc_types.npc_faction_id
WHERE
spawn2.zone='qeynos'
GROUP BY
npc_faction_id,
npc_faction.name

This query will run with out a problem at all. but if I try and run this:


SELECT
npc_faction.id,
npc_faction.name
FROM
npc_faction
LEFT JOIN npc_types
ON npc_types.npc_faction_id=npc_faction.id
WHERE
npc_types.id IS NULL


It will lock up my server.

But I can run this query which is the same type of concept:


SELECT
id,
name
FROM
faction_list
LEFT JOIN npc_faction_entries
ON faction_id=id AND npc_faction_id = 2613
WHERE
npc_faction_id IS NULL
ORDER BY
name


I have tried doing some searching on the internet but can not find anything as to the cause of this. I have tried this on MySql 4 and 5 and have had problem with other queries as well. Usually they have something to do with LEFT or RIGHT JOINS looking for NULL values. Any information on this would be appreciated

[GeorgeS]

ok try

SELECT
npc_faction.id,
npc_faction.name
FROM
npc_faction,npc_types
WHERE (npc_types.npc_faction_id=npc_faction.id)
AND npc_types.id is null


I'm wondering why you would check for 'is null' when the schema dissallows null entries in ncp_types.id

GeorgeS

[sesmar]

The point of the Query is to find all Faction Tables (npc_faction entries) that are not referenced in npc_types.npc_faction_id.

By doing a LEFT JOIN to npc_types I am telling it I want all npc_faction.name and id regardless of whether or not it is in npc_types (hard for me to explain this so I hope you understand what I mean.)

So I am not really checking for NULLs in the npc_types table but rather for rows that a return a NULL in place of a npc_type.id because that means they are not being referenced in the table. It would be that same as this query:


SELECT
npc_faction.id,
npc_faction.name
FROM
npc_faction
WHERE
npc_faction.id NOT IN(SELECT npc_types.npc_faction_id FROM npc_types)


This query however also locks up my server and MySql 4 does not support sub queries so even if it did work it would not do me any good in the end.

[GeorgeS]

ok figured out the partial problem. I ran the query in my program and the reason I think it locked up is because it used up all memory and took forever to run.

I've got a compiler which creates the standalone .exe from a query, but it does not work as an .exe.

I solved this problem by running the query recursively, but it took me about an hour to run. It did find many 'null's however, which suprised me.

When I get this thing to work, i'll fwd you the solution.
George

[sesmar]

Yeah, I can run the query and it will take about an hour for it to run. I just wanted to know if anyone else was having this issue or if it was just me. I know this is possible to do and I know how to do it programatically I was just hoping that I would not have to do it that way because of the time it will take, it is kind of hard to make it a filter in your DB editor if it takes that long to run. I am just used to working with MS SQL Server and running a query like this never gives me a problem but that is a discussion for a different time and place. Anyways thanks for the feedback and confirmation of my problem.