[MySQL] Query

Databases
1

Hello.

I am working with a website with statistics to online game.
During this time , I’ve got some problems.

Could anyone look at this query?
I would like to get output with a records from last day in database.
This query \/ showing all the records from a table.(like 5k records)

SELECT olympa.name, olympa.experience, olympa_gained.gained 
    FROM olympa, olympa_gained 
    WHERE (olympa.rank = olympa_gained.rank AND olympa_gained.date =(SELECT MAX(date) from olympa_gained))

I need something in this kind :

SELECT olympa.name, olympa.experience, olympa_gained.gained 
    FROM olympa, olympa_gained 
    WHERE ( IF(olympa_gained.date =(SELECT MAX(date) from olympa_gained) THEN olympa.rank = olympa_gained.rank  )

because atm it assign all the records from (olympa.rank = olympa_gained.rank)

Any help apreciated.

2 
SELECT olympa.name
     , olympa.experience
     , olympa_gained.gained   
  FROM olympa
INNER
  JOIN ( SELECT rank
              , MAX(date) AS latest_date
           FROM olympa_gained
         GROUP
             BY rank ) AS m
    ON m.rank = olympa.rank 
INNER
  JOIN olympa_gained   
    ON olympa_gained.rank = olympa.rank 
   AND olympa_gained.date = m.latest_date
3

I am getting same results with this code like with this one upper.
MySQL gave an output with 3,3k rows ( I Need only 300 rows ) every 300 rows have another date of a day.

4

e.g
Table: olympa_gained

table olympa_gained have a 3,3k records and each 300 rows have repeatedly rank from 1-300 , it’s why I need to select records with MAX(date).

Any Solution?

5

Problem solved.
Thanks for your code above , that’s helped after a few amendments.

6

That’s selecting first 300 rows instead of 300 with current date. [ all the code except a bold font statement ]

Is there possibility to add a WHERE STATEMENT before inner join?


SELECT olympa.name
, olympa.rank
	, olympa.experience
	, olympa_gained.gained
	, olympa_weekly.weekly
  , MAX( olympa.date ) as date
	FROM olympa           
[B]----- WHERE date = (SELECT MAX( date ) from Olympa)------[/B]
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS latest_date
	FROM olympa_gained
	  GROUP
		BY rank
	  ) AS m ON m.rank = olympa.rank
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS wlatest_date
	FROM olympa_weekly
	  GROUP
		BY rank
	  ) AS n ON n.rank = olympa.rank
INNER
JOIN olympa_gained
ON olympa_gained.rank = olympa.rank AND olympa_gained.date = m.latest_date
INNER
JOIN olympa_weekly
ON olympa_weekly.rank = olympa.rank AND olympa_weekly.date = n.wlatest_date

GROUP BY olympa_gained.rank ORDER BY olympa.experience DESC
7

no

8

is there another way to do it?

9

try putting it before the last GROUP BY

10

Thanks.

11

I have a 2 queries.

This one took ( 300 rows fetched in 0,0035s(0,6423s) )


SELECT olympa.rank
, olympa.name
  , olympa.level
	, olympa.experience
	, olympa_gained.gained
	, olympa_weekly.weekly
  , MAX( olympa.date ) as date
	FROM olympa
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS latest_date
	FROM olympa_gained
	  GROUP
		BY rank
	  ) AS m ON m.rank = olympa.rank
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS wlatest_date
	FROM olympa_weekly
	  GROUP
		BY rank
	  ) AS n ON n.rank = olympa.rank

INNER
JOIN olympa_gained
ON olympa_gained.rank = olympa.rank AND olympa_gained.date = m.latest_date
INNER
JOIN olympa_weekly
ON olympa_weekly.rank = olympa.rank AND olympa_weekly.date = n.wlatest_date
WHERE olympa.date = m.latest_date
GROUP BY olympa_gained.rank ORDER BY olympa.experience DESC

Just added - olympa_voc.voc and this took much more , more than 3 minutes … 300 rows fetched in 0,0340s (181,2258s)

SELECT olympa.rank
, olympa.name
  , olympa_voc.voc
  , olympa.level
	, olympa.experience
	, olympa_gained.gained
	, olympa_weekly.weekly
  , MAX( olympa.date ) as date
	FROM olympa
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS latest_date
	FROM olympa_gained
	  GROUP
		BY rank
	  ) AS m ON m.rank = olympa.rank
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS wlatest_date
	FROM olympa_weekly
	  GROUP
		BY rank
	  ) AS n ON n.rank = olympa.rank
INNER
JOIN olympa_voc
ON olympa_voc.name = olympa.name AND olympa.date = m.latest_date
INNER
JOIN olympa_gained
ON olympa_gained.rank = olympa.rank AND olympa_gained.date = m.latest_date
INNER
JOIN olympa_weekly
ON olympa_weekly.rank = olympa.rank AND olympa_weekly.date = n.wlatest_date
WHERE olympa.date = m.latest_date
GROUP BY olympa_gained.rank ORDER BY olympa.experience DESC

What’s wrong here?

12

INNER
JOIN olympa_voc
ON olympa_voc.name = olympa.name AND olympa.date = m.latest_date

You can eliminate that AND condition here.

13

Yes, I do but with no effect at all.
Time execution doesn’t changed. ( ± 10 seconds , that’s not much | 172 seconds execution)

14

Have you done an EXPLAIN? That should show you what indexes are being used and also, where to add some if needed.

bazz

15

try not to do any GROUP BY in the outer query

instead, for the voc table, use a subquery like you did for the gained and weekly tables

16

Thanks thats worked.

SELECT olympa.rank
, olympa.name
  , olympa_voc.voc
  , olympa.level
	, olympa.experience
	, olympa_gained.gained
	, olympa_weekly.weekly
  , MAX( olympa.date ) as date
	FROM olympa
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS latest_date
	FROM olympa_gained
	  GROUP
		BY rank
	  ) AS m ON m.rank = olympa.rank
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS wlatest_date
	FROM olympa_weekly
	  GROUP
		BY rank
	  ) AS n ON n.rank = olympa.rank
INNER
JOIN (
	SELECT name
	FROM olympa_voc
	  GROUP
		BY name
    	  ) AS z ON z.name = olympa.name
INNER
JOIN olympa_voc
ON olympa_voc.name = olympa.name

INNER
JOIN olympa_gained
ON olympa_gained.rank = olympa.rank 
AND olympa_gained.date = m.latest_date

INNER
JOIN olympa_weekly
ON olympa_weekly.rank = olympa.rank 
AND olympa_weekly.date = n.wlatest_date

WHERE olympa.date = m.latest_date
ORDER BY olympa.experience DESC
17

don’t forget to remove the MAX from the outer query’s SELECT clause, and add it to the z subquery

and what about that WHERE condition? are you sure you want the olympa row that matches the latest gained date?

18

Yes , that’s what I’ve expected.

I am getting right now something like this one.


But instead of this , I want to get all the 300 rows from current day.

GROUP BY?

19

all rows from current day?

in that case, you want somecolumn = CURRENT_DATE in your WHERE clause, yes?

20

hmm I can’t specify a date = current date , cause not all tables have a current date.

Cause of kinda slow speed of update rows in one of my table , there’s no current date.

that’s olympa_voc.voc , all others have a current date.

That’s what I 've got if I do a query without olympa_voc.voc


SELECT olympa.rank
, olympa.name
  , olympa.level
	, olympa.experience
	, olympa_gained.gained
	, olympa_weekly.weekly
  , MAX( olympa.date ) as date
	FROM olympa
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS latest_date
	FROM olympa_gained
	  GROUP
		BY rank
	  ) AS m ON m.rank = olympa.rank
INNER
JOIN (
	SELECT rank
	, MAX( date ) AS wlatest_date
	FROM olympa_weekly
	  GROUP
		BY rank
	  ) AS n ON n.rank = olympa.rank
INNER
JOIN olympa_gained
ON olympa_gained.rank = olympa.rank 
AND olympa_gained.date = m.latest_date

INNER
JOIN olympa_weekly
ON olympa_weekly.rank = olympa.rank 
AND olympa_weekly.date = n.wlatest_date

WHERE olympa.date = m.latest_date
GROUP BY olympa_gained.rank ORDER BY olympa.experience DESC