MAX_JOIN_SIZE error

Michael Austin wrote:
> Schemer wrote:
>>> Unfortunately, I have no control over the table structure.
>>> When this query is used, the error quoted in the original message is
>>> thrown.
>>> What might a query with explicit JOINs look like?
>>
>> More detail on table structure and content (subset of data):
>> f_id sub_id field_name field_val
>> 1 1 First John
>> 2 1 Last Jones
>> 3 1 Team Boys
>> 4 1 DOB 01/01/1990
>> 5 2 First Molly
>> 6 2 Last Malloy
>> 7 2 Team Girls
>> 8 2 DOB 02/02/1990
>>
>> For each sub_id, select all field_vals, if the "Team" field_name for
>> that sub_id has the field_val "Boys".
>> Regarding the JOINs, I thought they were for querying more than one
>> table.
>>
>>
>>
>>
>
> Ahhh.. someone created a sideways database where you have a column in a
> row that describes the next column in the row. Whoever created this
> mess should be shot. There are very few reasons to create a database in
> this manner.
>
> What you need is something along the lines of
> mysql> select sid, first, last, team, dob from
> -> (select a.sid,
> -> case a.fname when 'First' then a.fval end as First,
> -> case b.fname when 'Last' then b.fval end as Last,
> -> case c.fname when 'Team' then c.fval end as Team,
> -> case d.fname when 'DOB ' then d.fval end as DOB
> -> from aaa a right outer join aaa b on a.sid=b.sid
> -> right outer join aaa c on a.sid=c.sid
> -> right outer join aaa d on a.sid=d.sid) e
> -> where sid is not null
> -> and First is not null
> -> and Last is not null
> -> and Team is not null
> -> and DOB is not null;
> +------+-------+--------+-------+------------+
> | sid | first | last | team | dob |
> +------+-------+--------+-------+------------+
> | 1 | John | Jones | Boys | 01/01/1990 |
> | 2 | Molly | Malloy | Girls | 02/02/1990 |
> +------+-------+--------+-------+------------+


hit the send button too soon...

Also this method makes it very difficult to do date arithmetic or date
range searches. It can be done, you will first need to force the date
formats you have chosen into something the database can use - you just
need to be aware that it is not currently in a form for doing date
range searches.

You can create a set of views to make extraction of the data a bit
easier - although due to the fact that MySQL is not yet a real database
engine (at least not the 5.0.22-Debian_0ubuntu6.06-log version I have),
you cannot use the previous select statement in a view, you must do
multiple views like:

mysql> create view subtable as
-> select a.sid,
-> case a.fname when 'First' then a.fval end as First,
-> case b.fname when 'Last' then b.fval end as Last,
-> case c.fname when 'Team' then c.fval end as Team,
-> case d.fname when 'DOB ' then d.fval end as DOB
-> from aaa a left outer join aaa b on a.sid=b.sid
-> left outer join aaa c on a.sid=c.sid
-> left outer join aaa d on a.sid=d.sid
-> ;
Query OK, 0 rows affected (0.01 sec)

mysql> create view masterview as
-> select sid, first, last, team, dob from subtable
-> where First is not null
-> and Last is not null
-> and Team is not null
-> and DOB is not null;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from masterview;
+------+-------+--------+-------+------------+
| sid | first | last | team | dob |
+------+-------+--------+-------+------------+
| 1 | John | Jones | Boys | 01/01/1990 |
| 2 | Molly | Malloy | Girls | 02/02/1990 |
+------+-------+--------+-------+------------+
2 rows in set (0.00 sec)

On 14 May, 22:25, Michael Austin <maus...@firstdbasource.com> wrote:
> Michael Austin wrote:
> > Schemer wrote:
> >>> Unfortunately, I have no control over the table structure.
> >>> When this query is used, the error quoted in the original message is
> >>> thrown.
> >>> What might a query with explicit JOINs look like?
>
> >> More detail on table structure and content (subset of data):
> >> f_id sub_id field_name field_val
> >> 1 1 First John
> >> 2 1 Last Jones
> >> 3 1 Team Boys
> >> 4 1 DOB 01/01/1990
> >> 5 2 First Molly
> >> 6 2 Last Malloy
> >> 7 2 Team Girls
> >> 8 2 DOB 02/02/1990
>
> >> For each sub_id, select all field_vals, if the "Team" field_name for
> >> that sub_id has the field_val "Boys".
> >> Regarding the JOINs, I thought they were for querying more than one
> >> table.
>
> > Ahhh.. someone created a sideways database where you have a column in a
> > row that describes the next column in the row. Whoever created this
> > mess should be shot. There are very few reasons to create a database in
> > this manner.
>
> > What you need is something along the lines of
> > mysql> select sid, first, last, team, dob from
> > -> (select a.sid,
> > -> case a.fname when 'First' then a.fval end as First,
> > -> case b.fname when 'Last' then b.fval end as Last,
> > -> case c.fname when 'Team' then c.fval end as Team,
> > -> case d.fname when 'DOB ' then d.fval end as DOB
> > -> from aaa a right outer join aaa b on a.sid=b.sid
> > -> right outer join aaa c on a.sid=c.sid
> > -> right outer join aaa d on a.sid=d.sid) e
> > -> where sid is not null
> > -> and First is not null
> > -> and Last is not null
> > -> and Team is not null
> > -> and DOB is not null;
> > +------+-------+--------+-------+------------+
> > | sid | first | last | team | dob |
> > +------+-------+--------+-------+------------+
> > | 1 | John | Jones | Boys | 01/01/1990 |
> > | 2 | Molly | Malloy | Girls | 02/02/1990 |
> > +------+-------+--------+-------+------------+
>
> hit the send button too soon...
>
> Also this method makes it very difficult to do date arithmetic or date
> range searches. It can be done, you will first need to force the date
> formats you have chosen into something the database can use - you just
> need to be aware that it is not currently in a form for doing date
> range searches.
>
> You can create a set of views to make extraction of the data a bit
> easier - although due to the fact that MySQL is not yet a real database
> engine (at least not the 5.0.22-Debian_0ubuntu6.06-log version I have),
> you cannot use the previous select statement in a view, you must do
> multiple views like:
>
> mysql> create view subtable as
> -> select a.sid,
> -> case a.fname when 'First' then a.fval end as First,
> -> case b.fname when 'Last' then b.fval end as Last,
> -> case c.fname when 'Team' then c.fval end as Team,
> -> case d.fname when 'DOB ' then d.fval end as DOB
> -> from aaa a left outer join aaa b on a.sid=b.sid
> -> left outer join aaa c on a.sid=c.sid
> -> left outer join aaa d on a.sid=d.sid
> -> ;
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> create view masterview as
> -> select sid, first, last, team, dob from subtable
> -> where First is not null
> -> and Last is not null
> -> and Team is not null
> -> and DOB is not null;
> Query OK, 0 rows affected (0.02 sec)
>
> mysql> select * from masterview;
> +------+-------+--------+-------+------------+
> | sid | first | last | team | dob |
> +------+-------+--------+-------+------------+
> | 1 | John | Jones | Boys | 01/01/1990 |
> | 2 | Molly | Malloy | Girls | 02/02/1990 |
> +------+-------+--------+-------+------------+
> 2 rows in set (0.00 sec)

You don't actually require all the case statements. Just code the
criteria on the JOIN condition thus:

CREATE VIEW subtable AS
SELECT
aa.sid,
a.fval First,
b.fval Last,
c.fval Team,
d.fval DOB
FROM aaa aa
LEFT JOIN aaa a ON aa.sid = a.sid AND a.fname = 'First'
LEFT JOIN aaa b ON aa.sid = b.sid AND b.fname = 'Last'
LEFT JOIN aaa c ON aa.sid = c.sid AND c.fname = 'Team'
LEFT JOIN aaa d ON aa.sid = d.sid AND d.fname = 'DOB'
;

Captain Paralytic wrote:
> On 14 May, 22:25, Michael Austin <maus...@firstdbasource.com> wrote:
>> Michael Austin wrote:
>>> Schemer wrote:
>>>>> Unfortunately, I have no control over the table structure.
>>>>> When this query is used, the error quoted in the original message is
>>>>> thrown.
>>>>> What might a query with explicit JOINs look like?
>>>> More detail on table structure and content (subset of data):
>>>> f_id sub_id field_name field_val
>>>> 1 1 First John
>>>> 2 1 Last Jones
>>>> 3 1 Team Boys
>>>> 4 1 DOB 01/01/1990
>>>> 5 2 First Molly
>>>> 6 2 Last Malloy
>>>> 7 2 Team Girls
>>>> 8 2 DOB 02/02/1990
>>>> For each sub_id, select all field_vals, if the "Team" field_name for
>>>> that sub_id has the field_val "Boys".
>>>> Regarding the JOINs, I thought they were for querying more than one
>>>> table.
>>> Ahhh.. someone created a sideways database where you have a column in a
>>> row that describes the next column in the row. Whoever created this
>>> mess should be shot. There are very few reasons to create a database in
>>> this manner.
>>> What you need is something along the lines of
>>> mysql> select sid, first, last, team, dob from
>>> -> (select a.sid,
>>> -> case a.fname when 'First' then a.fval end as First,
>>> -> case b.fname when 'Last' then b.fval end as Last,
>>> -> case c.fname when 'Team' then c.fval end as Team,
>>> -> case d.fname when 'DOB ' then d.fval end as DOB
>>> -> from aaa a right outer join aaa b on a.sid=b.sid
>>> -> right outer join aaa c on a.sid=c.sid
>>> -> right outer join aaa d on a.sid=d.sid) e
>>> -> where sid is not null
>>> -> and First is not null
>>> -> and Last is not null
>>> -> and Team is not null
>>> -> and DOB is not null;
>>> +------+-------+--------+-------+------------+
>>> | sid | first | last | team | dob |
>>> +------+-------+--------+-------+------------+
>>> | 1 | John | Jones | Boys | 01/01/1990 |
>>> | 2 | Molly | Malloy | Girls | 02/02/1990 |
>>> +------+-------+--------+-------+------------+
>> hit the send button too soon...
>>
>> Also this method makes it very difficult to do date arithmetic or date
>> range searches. It can be done, you will first need to force the date
>> formats you have chosen into something the database can use - you just
>> need to be aware that it is not currently in a form for doing date
>> range searches.
>>
>> You can create a set of views to make extraction of the data a bit
>> easier - although due to the fact that MySQL is not yet a real database
>> engine (at least not the 5.0.22-Debian_0ubuntu6.06-log version I have),
>> you cannot use the previous select statement in a view, you must do
>> multiple views like:
>>
>> mysql> create view subtable as
>> -> select a.sid,
>> -> case a.fname when 'First' then a.fval end as First,
>> -> case b.fname when 'Last' then b.fval end as Last,
>> -> case c.fname when 'Team' then c.fval end as Team,
>> -> case d.fname when 'DOB ' then d.fval end as DOB
>> -> from aaa a left outer join aaa b on a.sid=b.sid
>> -> left outer join aaa c on a.sid=c.sid
>> -> left outer join aaa d on a.sid=d.sid
>> -> ;
>> Query OK, 0 rows affected (0.01 sec)
>>
>> mysql> create view masterview as
>> -> select sid, first, last, team, dob from subtable
>> -> where First is not null
>> -> and Last is not null
>> -> and Team is not null
>> -> and DOB is not null;
>> Query OK, 0 rows affected (0.02 sec)
>>
>> mysql> select * from masterview;
>> +------+-------+--------+-------+------------+
>> | sid | first | last | team | dob |
>> +------+-------+--------+-------+------------+
>> | 1 | John | Jones | Boys | 01/01/1990 |
>> | 2 | Molly | Malloy | Girls | 02/02/1990 |
>> +------+-------+--------+-------+------------+
>> 2 rows in set (0.00 sec)
>
> You don't actually require all the case statements. Just code the
> criteria on the JOIN condition thus:
>
> CREATE VIEW subtable AS
> SELECT
> aa.sid,
> a.fval First,
> b.fval Last,
> c.fval Team,
> d.fval DOB
> FROM aaa aa
> LEFT JOIN aaa a ON aa.sid = a.sid AND a.fname = 'First'
> LEFT JOIN aaa b ON aa.sid = b.sid AND b.fname = 'Last'
> LEFT JOIN aaa c ON aa.sid = c.sid AND c.fname = 'Team'
> LEFT JOIN aaa d ON aa.sid = d.sid AND d.fname = 'DOB'
> ;

I will reiterate, that creating a DB such as this, while can be
"flexible", it breaks every rule of normalization. This information
should be stored in a single row. Joining this information with another
table will always be problematic. There is also no really good way to
index this beast either. You would almost need to index every single
field_name and field_value. What this means to your system is that it
will require 2-5 times the amount of space. So, you better hope it
doesn't grow too big... As to performance, without good indexing do not
expect performance on very large data set to be stellar.

Thanks for the catch CP... The good thing about SQL is there are many
many ways to achieve the same goal. The BAD thing about SQL is there
are many mnay ways to achieve the same goal. Yes, a lot cleaner, but
would just need to add "select distinct". Without it, you get as many
results for each sid as you have columns (in this case without distinct
would return 4 of each), however - upon reflection, my original query
creates basically a Cartesian product of all rows for each sid with lots
of null values - not an ideal query especially if there are thousands of
records...


mysql> select aa.sid,
-> a.fval First,
-> b.fval Last,
-> c.fval Team,
-> d.fval DOB
-> FROM aaa aa
-> LEFT outer JOIN aaa a ON aa.sid = a.sid AND a.fname = 'First'
-> LEFT outer JOIN aaa b ON aa.sid = b.sid AND b.fname = 'Last'
-> LEFT outer JOIN aaa c ON aa.sid = c.sid AND c.fname = 'Team'
-> LEFT outer JOIN aaa d ON aa.sid = d.sid AND d.fname = 'DOB';
+------+-------+--------+-------+------------+
| sid | First | Last | Team | DOB |
+------+-------+--------+-------+------------+
| 1 | John | Jones | Boys | 01/01/1990 |
| 1 | John | Jones | Boys | 01/01/1990 |
| 1 | John | Jones | Boys | 01/01/1990 |
| 1 | John | Jones | Boys | 01/01/1990 |
| 2 | Molly | Malloy | Girls | 02/02/1990 |
| 2 | Molly | Malloy | Girls | 02/02/1990 |
| 2 | Molly | Malloy | Girls | 02/02/1990 |
| 2 | Molly | Malloy | Girls | 02/02/1990 |
+------+-------+--------+-------+------------+
8 rows in set (0.01 sec)


mysql> SELECT DISTINCT
-> aa.sid,
-> a.fval First,
-> b.fval Last,
-> c.fval Team,
-> d.fval DOB
-> FROM aaa aa
-> LEFT JOIN aaa a ON aa.sid = a.sid AND a.fname = 'First'
-> LEFT JOIN aaa b ON aa.sid = b.sid AND b.fname = 'Last'
-> LEFT JOIN aaa c ON aa.sid = c.sid AND c.fname = 'Team'
-> LEFT JOIN aaa d ON aa.sid = d.sid AND d.fname = 'DOB'
-> ;
+------+-------+--------+-------+------------+
| sid | First | Last | Team | DOB |
+------+-------+--------+-------+------------+
| 1 | John | Jones | Boys | 01/01/1990 |
| 2 | Molly | Malloy | Girls | 02/02/1990 |
+------+-------+--------+-------+------------+

On 15 May, 15:25, Michael Austin <maus...@firstdbasource.com> wrote:
> I will reiterate, that creating a DB such as this, while can be
> "flexible", it breaks every rule of normalization. This information
> should be stored in a single row. Joining this information with another
> table will always be problematic. There is also no really good way to
> index this beast either. You would almost need to index every single
> field_name and field_value. What this means to your system is that it
> will require 2-5 times the amount of space. So, you better hope it
> doesn't grow too big... As to performance, without good indexing do not
> expect performance on very large data set to be stellar.

I had to do a very similar thing with a table. There was a good reason
for the table to be like it was, but it had been "abused". I had to
extract so many fields, that I needed to create a temporary table to
hold an interim result set since I ran up to the JOIN limit. However
the user didn't have CREATE TEMP... privileges at their host, so I had
to do it with table locks.

Once I had done it, it took 30 seconds to run. Adding correct indexes
brought that run time down to under a second.