SEO

Hello,
A query that worked yesterday (against the mySQL db behind my web site)
fails today with this error:
"Can\'t use players: The SELECT would examine more than MAX_JOIN_SIZE rows;
check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if
the SELECT is okay"

The db structure:
f_id int(11)
sub_id int(11)
field_name varchar(100)
field_val text

The db has 135 rows.

The query:
$querystr = "
SELECT FN.sub_id, SEL.`sel`, FN.`First Name` , LN.`Last Name` , DOB.`dob` ,
EMAIL.`email` , PHONE.`phone`, CLUB.`club`, POS.`pos`
FROM ( SELECT sub_id, field_val AS 'First Name'
FROM $wpdb->cformsdata
WHERE `field_name` = 'First Name'
)FN, ( SELECT sub_id, field_val AS 'Last Name'
FROM $wpdb->cformsdata
WHERE `field_name` = 'Last Name'
)LN, ( SELECT sub_id, field_val AS `Team`
FROM $wpdb->cformsdata
WHERE `field_name` = 'Team'
)TEAM, ( SELECT sub_id, field_val AS `dob`
FROM $wpdb->cformsdata
WHERE `field_name` = 'DOB'
)DOB, ( SELECT sub_id, field_val AS `email`
FROM $wpdb->cformsdata
WHERE `field_name` = 'EMail'
)EMAIL, ( SELECT sub_id, field_val AS `phone`
FROM $wpdb->cformsdata
WHERE `field_name` = 'Telephone'
)PHONE, ( SELECT sub_id, field_val AS `pos`
FROM $wpdb->cformsdata
WHERE `field_name` = 'Positions Played'
)POS, ( SELECT sub_id, field_val as `sel`
FROM $wpdb->cformsdata
WHERE `field_name` = 'Selected'
)SEL, ( SELECT sub_id, field_val as `club`
FROM $wpdb->cformsdata
WHERE `field_name` = 'School or Club'
)CLUB
WHERE FN.sub_id = LN.sub_id
AND LN.sub_id = TEAM.sub_id
AND TEAM.sub_id = DOB.sub_id
and DOB.sub_id = EMAIL.sub_id
and EMAIL.sub_id = PHONE.sub_id
AND PHONE.sub_id = POS.sub_id
AND POS.sub_id = SEL.sub_id
AND SEL.sub_id = CLUB.sub_id
AND TEAM.Team = 'Boys'
ORDER BY LN.`Last Name`
";

How can I fix this? What would be the best way to simplify this query?
Thanks for any suggestions.

On 14 May, 14:36, "Schemer" <nowh...@man.com> wrote:
> Hello,
> A query that worked yesterday (against the mySQL db behind my web site)
> fails today with this error:
> "Can\'t use players: The SELECT would examine more than MAX_JOIN_SIZE rows;
> check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if
> the SELECT is okay"
>
> How can I fix this? What would be the best way to simplify this query?
> Thanks for any suggestions.
Errm, why are you asking us when you just posted the answer?

>> A query that worked yesterday (against the mySQL db behind my web site)
>> fails today with this error:
>> "Can\'t use players: The SELECT would examine more than MAX_JOIN_SIZE
>> rows;
>> check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=#
>> if
>> the SELECT is okay"
>>
>> How can I fix this? What would be the best way to simplify this query?
>> Thanks for any suggestions.
> Errm, why are you asking us when you just posted the answer?

Since I have little experience with SQL, I was hoping I could get some
pointers on how I might be able to break down this query in a way that is
useable in the php of a web page.

On 14 May, 15:04, "Schemer" <nowh...@man.com> wrote:
> >> A query that worked yesterday (against the mySQL db behind my web site)
> >> fails today with this error:
> >> "Can\'t use players: The SELECT would examine more than MAX_JOIN_SIZE
> >> rows;
> >> check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=#
> >> if
> >> the SELECT is okay"
>
> >> How can I fix this? What would be the best way to simplify this query?
> >> Thanks for any suggestions.
> > Errm, why are you asking us when you just posted the answer?
>
> Since I have little experience with SQL, I was hoping I could get some
> pointers on how I might be able to break down this query in a way that is
> useable in the php of a web page.

Well, whilst I agree that the query is rather ghastly (it should be
written as explicit JOINS, instead of multiple sub selects and it also
looks as though the underlying table structure could do with
changing), I don't see what stops it already being used in a php
script?

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:5276825c-e6ea-4fc1-b76a-5621584d344c@c58g2000hsc.googlegroups.com...
> On 14 May, 15:04, "Schemer" <nowh...@man.com> wrote:
>> >> A query that worked yesterday (against the mySQL db behind my web
>> >> site)
>> >> fails today with this error:
>> >> "Can\'t use players: The SELECT would examine more than MAX_JOIN_SIZE
>> >> rows;
>> >> check your WHERE and use SET SQL_BIG_SELECTS=1 or SET
>> >> SQL_MAX_JOIN_SIZE=#
>> >> if
>> >> the SELECT is okay"
>>
>> >> How can I fix this? What would be the best way to simplify this
>> >> query?
>> >> Thanks for any suggestions.
>> > Errm, why are you asking us when you just posted the answer?
>>
>> Since I have little experience with SQL, I was hoping I could get some
>> pointers on how I might be able to break down this query in a way that is
>> useable in the php of a web page.
>
> Well, whilst I agree that the query is rather ghastly (it should be
> written as explicit JOINS, instead of multiple sub selects and it also
> looks as though the underlying table structure could do with
> changing), I don't see what stops it already being used in a php
> script?

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?
Thanks for the reply.

> 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.

On 14 May, 15:36, "Schemer" <nowh...@man.com> wrote:
> "Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
>
> news:5276825c-e6ea-4fc1-b76a-5621584d344c@c58g2000hsc.googlegroups.com...
>
>
>
> > On 14 May, 15:04, "Schemer" <nowh...@man.com> wrote:
> >> >> A query that worked yesterday (against the mySQL db behind my web
> >> >> site)
> >> >> fails today with this error:
> >> >> "Can\'t use players: The SELECT would examine more than MAX_JOIN_SIZE
> >> >> rows;
> >> >> check your WHERE and use SET SQL_BIG_SELECTS=1 or SET
> >> >> SQL_MAX_JOIN_SIZE=#
> >> >> if
> >> >> the SELECT is okay"
>
> >> >> How can I fix this? What would be the best way to simplify this
> >> >> query?
> >> >> Thanks for any suggestions.
> >> > Errm, why are you asking us when you just posted the answer?
>
> >> Since I have little experience with SQL, I was hoping I could get some
> >> pointers on how I might be able to break down this query in a way that is
> >> useable in the php of a web page.
>
> > Well, whilst I agree that the query is rather ghastly (it should be
> > written as explicit JOINS, instead of multiple sub selects and it also
> > looks as though the underlying table structure could do with
> > changing), I don't see what stops it already being used in a php
> > script?
>
> Unfortunately, I have no control over the table structure.
> When this query is used, the error quoted in the original message is thrown.
You have already posted how to overcome the "error". The message tells
you that.

"Schemer" <nowhere@man.com> wrote in message
news:cq2dnSL9v4V0d7fVnZ2dnUVZ_judnZ2d@posted.green mountainaccess...
> Hello,
> A query that worked yesterday (against the mySQL db behind my web site)
> fails today with this error:
> "Can\'t use players: The SELECT would examine more than MAX_JOIN_SIZE
> rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET
> SQL_MAX_JOIN_SIZE=# if the SELECT is okay"
>

$querySetBig = "SET SESSION SQL_BIG_SELECTS=1";
mysql_query($querySetBig);

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.
To do self joins, you give the table many aliases.

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 |
+------+-------+--------+-------+------------+