MySQL Forums
Forum List  »  Perl

Re: lenght variables
Posted by: Randy Clamons
Date: October 22, 2010 04:37PM

Just a suggestion. What you are trying to do is code a single field with more than one piece of data. Even when you do figure it out, it is going to be extremely inefficient because of indexing issues.

One way you could do this would be using a LIKE comparator in your select. Assuming you know the 'grandfather' id:

Select * from [tablename] WHERE id LIKE '12345_' ORDER BY id;

This statement will not use any index you have attached to the table. If the table is small it's not a real problem, but once your table reaches significant size your query will be slow.

Another way to go might be to create a table of relationships that includes 2 ids from your original table, one column for the 'parent' and one column for the 'child' and possibly a third column that describes the relationship between the to "wife", "child", etc. Maybe one more column for ordinal ('1', '2', etc). Then use a join in your select. Something like this:

FROM people as parent
LEFT OUTER JOIN relations ON relations.parentid =
LEFT OUTER JOIN people as child on relations.childid =
WHERE = 12345
ORDER BY relations.type, relations.ordinal;

Assuming you set indices on, relations.parentid and relations.childid this statement would use those indices.

Best of luck!

Options: ReplyQuote

Written By
October 22, 2010 02:56PM
Re: lenght variables
October 22, 2010 04:37PM
October 24, 2010 02:13PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.