Hanging on a join query

[Michael Morris]

What could possibly cause MySQL to hang and not resolve on a query like so.

SELECT * FROM personal LEFT JOIN addresses ON ( personal.recordid = addresses.parentid )

I am well and truly stumped and angry - I've not in 6 years seen MySQL hang on something this trivial, and I've been using it a long time. I know it's something stupid but I can't seem to find it.

The tables do not have the same number of rows, (nor should they, there'd be no point to using separate tables if they do). Hmm...

[r937]

try a REPAIR on the tables

do an EXPLAIN on the query

[Michael Morris]

Tried that - didn't work (repaired ok). Emptied the tables (they only have imported test data) and tried the query, worked. Repopulated, put a limit of 10, worked.

Limit 100 and much slower, but worked. Hangs on any higher number of records or any attempt to add an order by clause. I'm stumped.

Next I'm considering dropping and rebuilding the tables, see if that works.

[r937]

hangs because it's doing table scans -- the more rows, the slower it gets

i'll bet you haven't created any indexes

could you please do a SHOW CREATE TABLE for both tables

[Michael Morris]

AH. Got it.

recordid is my primary key, but wasn't set to be an index. Both the recordid and parentid fields of every table need to be indexed.

Overlooked this because I'm writing a PHP system that autocreates the tables and hadn't wrote in the index setting as part of that code.

[r937]

if you declare a column (or columns) as PRIMARY KEY, you should ~not~ create an index on it, because PKs always get indexed anyway

so do FOREIGN KEYs in mysql (after version something-or-other)

is addresses.parentid a FOREIGN KEY or just an ordinary column?

[Michael Morris]

In my scheme parentid is always a foreign key to a row on the parent table of the current table. It's an abstraction for the PHP table creation system.

For example, a table called companies has a child table employees. The parent id of the employees table refers here to the row holding the specific company.

Now in a non-abstract system the field would be companyid most likely.

Tables can have multiple parents. The parent tables for addresses are companies and persons. Hence a "parent" column indicates which table holds the row in the unusual event that I need to join one (or both) of those tables back to the addresses table (because of the structure of the program it's almost always going to be the other way around).

Anyway, thanks for your help - I'll remove the indexing call on primary keys but I need my code to index the parent and parentid fields when they get created (When the user declares that the table has one or more parents)

This particular error was frustrating because I've done this a long while now, and yet this rather simple error caught me off guard and cost me a day of work.

[r937]

you store a table name inside a column? that's a bad sign...

anyhow, make sure that your "foreign key" is actually declared with FOREIGN KEY syntax -- otherwise you will have to declare the index for it manually

have you done the EXPLAIN on your query? spotted anything of concern?

[Michael Morris]

you store a table name inside a column? that's a bad sign...

Of?

The data of the table in question can be a child of one of three tables - companies, personal, claims. The only alternative is to create three tables with the exact same fields or use a master indexing schema which is a headache of another sort.

anyhow, make sure that your "foreign key" is actually declared with FOREIGN KEY syntax -- otherwise you will have to declare the index for it manually

Last time I checked MyISAM doesn't support foreign key declaration. And I find backing up large Innodb tables to be such a royal PITA it isn't worth it. As I said earlier I'm not new at this.

have you done the EXPLAIN on your query? spotted anything of concern?

That's how I discovered it wasn't indexing.

I guess asking a n00b question gets one treated like a n00b. Thanks, but not interested in being talked down to.

[r937]

well, pardon me for trying

you must understand that thousands of people post similar questions here, and those of us who go out of our way to try to help them have a few things we ask about that often pinpoint the problem

and hey, you're the one who started things off by saying "it's obviously something stupid"

and that crack about myisam not accepting foreign keys, you conveniently overlooked mentioning that these were myisam tables, and yet you lambaste me for talking about foreign keys?

dude, if you feel slighted, then i'm sorry, but you're obviously in the wrong forum

good luck with your issue

[r937]

p.s. storing a table name in a column (meta-data) is a sign of poor design skills

[Michael Morris]

Baiting someone is a sign of a poor human being, or was there another point to that bit of snark?

Knowing what the rules are is only the first step - knowing when they do and do not apply is the next and one it seems you haven't learned yet.

[SpacePhoenix]

In my scheme parentid is always a foreign key to a row on the parent table of the current table. It's an abstraction for the PHP table creation system.

For example, a table called companies has a child table employees. The parent id of the employees table refers here to the row holding the specific company.

Now in a non-abstract system the field would be companyid most likely.

Tables can have multiple parents. The parent tables for addresses are companies and persons. Hence a "parent" column indicates which table holds the row in the unusual event that I need to join one (or both) of those tables back to the addresses table (because of the structure of the program it's almost always going to be the other way around).

Anyway, thanks for your help - I'll remove the indexing call on primary keys but I need my code to index the parent and parentid fields when they get created (When the user declares that the table has one or more parents)

This particular error was frustrating because I've done this a long while now, and yet this rather simple error caught me off guard and cost me a day of work.

Can you show a "CREATE TABLE" for the queries? What do you mean by:

It's an abstraction for the PHP table creation system

Are you trying to use MVC?