Querys Bug

[dispy]

Good day :)

<<== new, please excuse If I post shit :)

I've one query which doesn't work:

SELECT
      conquer.timestamp AS time,
      conquer.new_owner AS new_owner_id
 
      FROM de12_conquer AS conquer
      INNER JOIN de12_tribe AS conqueror ON conqueror.id=conquer.new_owner
      INNER JOIN de12_tribe AS loser ON loser.id=conquer.old_owner
 
      WHERE (conquer.timestamp>0 && conquer.timestamp<1213647029734) AND (conqueror.ally=37038 OR loser.ally=37038 )
      ORDER BY conquer.timestamp DESC LIMIT 50

This query doesn't work .... mysql server has gone away is the return or nothing... just loading :(

I've located the problem with a second query which works:

SELECT
      conquer.timestamp AS time,
      conquer.new_owner AS new_owner_id
 
      FROM de12_conquer AS conquer
      INNER JOIN de12_tribe AS conqueror ON conqueror.id=conquer.new_owner
 
      WHERE (conquer.timestamp>0 && conquer.timestamp<1213647029734) AND (conqueror.ally=37038  )
      ORDER BY conquer.timestamp DESC LIMIT 50

As you can see the both query have only 1 different INNER JOIN (ignore the additional where-condition):

INNER JOIN de12_tribe AS loser ON loser.id=conquer.old_owner

This line is "crashing" MySQl... but why ????

Structures of the tables:

CREATE TABLE IF NOT EXISTS `de12_tribe` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL default '',
`ally` int(11) NOT NULL,
`villages` int(5) NOT NULL,
`points` int(11) NOT NULL,
`rank` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `de12_conquer` (
`villageid` int(11) NOT NULL,
`timestamp` int(11) NOT NULL,
`new_owner` int(11) NOT NULL,
`old_owner` int(11) NOT NULL,
KEY `villageid` (`villageid`),
KEY `timestamp` (`timestamp`),
KEY `new_owner` (`new_owner`),
KEY `old_owner` (`old_owner`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I hope you can help me, thanks !!

dispy

[tesuji]

Hi

What exactly do you mean by:
"This query doesn't work .... mysql server has gone away is the return or nothing... just loading" ?

Do you get any error message?
Does the query never ends?
Is the result set empty? (because of improper data you want to join together. for example: if old_owner of table del2_conquer and id of del2_tribe do not have same values, your result set would be empty.)

How large are both tables, current number of rows per table? Depending on size and features needed sometimes innodb is more useful than myisam.

Second table does not have primary key, also no foreign keys (sorry, fk not possible for myisam). I am trying to figure out which relationship exists between both tables, and I have got the feeling that the table's structure wouldn't that be optimal. As for example villages of del2_tribe could appear several times for same tribe. On the other hand there is villageid of second table, how is it related to first table (via villages)?

ok, maybe above will get you some ideas to proceed solving your problem.

krs,
tesu

[varmadba]

if you run a explain on your query it shows

"Impossible WHERE noticed after reading const tables"

that's because

WHERE conquer.timestamp between 0 and 1213647029734

In table de12_conquer ,timestamp columns is a integer data type and this number cant be inserted into it

The value you are using (1213647029734) is worng,it not a UNIXTIME or a timestamp value

let me know if it helps....

[dispy]

"This query doesn't work .... mysql server has gone away is the return or nothing... just loading" ?
Do you get any error message?
Does the query never ends?

One time testing it I waited ~5 minutes and then I got the "mysql server has gone away"

Is the result set empty? (because of improper data you want to join together. for example: if old_owner of table del2_conquer and id of del2_tribe do not have same values, your result set would be empty.)

Possible, but there are records passing the conditions, I'm sure ^^
I've tried LEFT JOIN instead of INNER JOIN -that didn't work too.

Second table does not have primary key, also no foreign keys (sorry, fk not possible for myisam). I am trying to figure out which relationship exists between both tables, and I have got the feeling that the table's structure wouldn't that be optimal. As for example villages of del2_tribe could appear several times for same tribe. On the other hand there is villageid of second table, how is it related to first table (via villages)?

de12_conquer can't have a primary key - there isn't an id existing just one time

de12_tribe contains the tribe -there is a primary key cause the id of every player exists only one time.
A tribe can have multiple conquers logged in the table de12_conquer - new_owner and old_owner are the fields which contain the id of the tribe ( one the looser and one the winner).
It's for a tool for the browsergame tribalwars (to understand the relations ).

the villageid contains the ID (primary key) of the village (in de12_villages- for this problem not necessary).

Don't worry about the structure of the relations -they're correct :)

f you run a explain on your query it shows

"Impossible WHERE noticed after reading const tables"

that's because WHERE conquer.timestamp between 0 and 1213647029734

Wrong -I already tried explain and I didn't get an error.
Here the explain-output:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  loser   ALL     PRIMARY     NULL    NULL    NULL    7616    Using temporary; Using filesort
1   SIMPLE  conquer     ref     timestamp,new_owner,old_owner   old_owner   4   dispy.loser.id  8   Using where
1   SIMPLE  conqueror   ALL     PRIMARY     NULL    NULL    NULL    7616    Range checked for each record (index map: 0x1)

In table de12_conquer ,timestamp columns is a integer data type and this number cant be inserted into it
The value you are using (1213647029734) is worng,it not a UNIXTIME or a timestamp value

You're right: I'll fix that.

Thanks for trying to help me :icon_smile:
dispy

EDIT: I forgot:
records in de12_conquer: 625,687
reconrds in de12_tribe:7,616

[dispy]

No one has an idea? -.-
I know a toolsite solved this problem by using ORM (PHP, MySQL) but I need a "real" Query ^^

dispy