Query combination

[TheMagician]

TABLE #1 STRUCTURE - SCORES

 

CREATE TABLE `scores` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `song_id` int(11) NOT NULL,
  `game` int(11) NOT NULL,
  `diff` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  `stars` int(11),
  `percentage` int(11),
  `notestreak` int(11),
  `date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
);

 

#1 EXAMPLE DATA

 

INSERT INTO scores (user_id, song_id, game, diff, score, stars, percentage, notestreak)
VALUES (2, 1, 4, 4, 255255, 5, 100, 551),
(2, 1, 4, 4, 255655, 5, 100, 551),
(3, 1, 4, 4, 256255, 5, 100, 551),
(2, 2, 4, 4, 255255, 5, 100, 772),
(3, 2, 4, 4, 256255, 5, 100, 772);

 

TABLE #2 STRUCTURE - SONGS

 

CREATE TABLE `list` (
  `id` int(11) NOT NULL,
  `song` varchar(64) NOT NULL,
  `game` int(11) NOT NULL,
  `diff` int(11) NOT NULL,
  `notestreak` int(11) NOT NULL,
  `venue` int(11) NOT NULL
);

 

#2 EXAMPLE DATA

 

INSERT INTO `list` VALUES (1, 'Slow Ride', 4, 4, 551, -1);
INSERT INTO `list` VALUES (2, 'Talk Dirty To Me', 4, 4, 772, -1);

 

QUERY #1 - RETRIEVE SCORES AND SONG NAMES

 

SELECT s.*, l.song
FROM scores s, list l
WHERE s.user_id = 2
AND s.game = 4
AND s.diff = 4
AND l.game = 4
AND l.id = s.song_id
AND l.is_dlc = 0
GROUP BY s.song_id
ORDER BY s.song_id ASC

 

QUERY #2 - RETRIEVE RANK FOR EACH SCORE

 

SELECT COUNT( score ) + 1 AS rank
FROM (

SELECT MAX( score ) AS score
FROM scores
WHERE game = 4
AND diff = 4
AND song_id = _ # this id is retrieved from the above query
AND user_id > 1
AND score > ______ # this score is retrieved from the above query
GROUP BY user_id
ORDER BY score DESC

) AS rank_table

 

Question #1: How could I combine these queries?

 

Temporary solution: PHP array that has other arrays inside, where the rank is also added so that I can also sort the output by "rank" field. I have a feeling that this is very slow though. And if I could combine those queries I could get rid of a lot of code.

 

Question #2: Could I make the queries run faster ie. by changing them or adding indexes?

 

I have tried to set some indexes, but they're probably not good, so I'm asking experts' advice here.

 

Thanks!

 

edit:

 

MySQL version: 5.0.41-community-nt

[fenway]

There are some other issues first, like mixing * and GROUP BY

[TheMagician]

There are some other issues first, like mixing * and GROUP BY

 

Ok, so do you suggest replacing the * with the column names?

 

Query #1 might actually be an older query, my code seems to have another query instead of that (this is an old project). The one in the first post probably gave wrong results.

 

It looks like this:

 

QUERY #2 ALTERNATE VERSION

 

SELECT l.id AS l_id, l.song AS l_song, l.notestreak AS l_ns, l.venue AS l_venue, p.*
FROM list l LEFT JOIN (

SELECT DISTINCT song_id, score, id, game, diff, stars, percentage, notestreak, date
FROM scores 
WHERE game=4 AND diff=4 AND user_id=2
ORDER BY song_id ASC, score DESC

) AS p
ON (p.game=l.game) AND (p.diff=l.diff) AND (p.song_id=l.id)
WHERE l.game=4 AND l.diff=4
GROUP BY l_id
ORDER BY l_id

 

What I'm trying to achieve here is:

 

- Select highest score for each song_id from 'scores' table for user_id=2 (users can submit more than 1 score for each song_id)

- Select proper song name etc. from the table 'list'

[TheMagician]

Sorry for double post, but I found this from the FAQs: http://www.oreillynet.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html

 

I'm gonna read it tomorrow, but it probably provides a precise answer to my problem.

[fenway]

The issue is that GROUP BY returns almost always absolute nonesense for any columns other than the ones you specify in the group by clause.