Problem with sql query speed

I struggling with a MySQL query which performs very slowly. I hope some of you will be able to give me some advices about how to increase the speed of this query.

This is the table structure:

+---------------+------------+------+-----+-------------------+----------------+
| Field         | Type       | Null | Key | Default           | Extra          |
+---------------+------------+------+-----+-------------------+----------------+
| id            | int(11)    | NO   | PRI | NULL              | auto_increment |
| MedlemsID     | int(11)    | NO   | MUL | NULL              |                |
| Medlemsnummer | int(11)    | NO   |     | NULL              |                |
| Handling      | tinyint(1) | NO   |     | NULL              |                |
| KlubID        | int(11)    | NO   |     | NULL              |                |
| Klubtype      | varchar(5) | NO   |     | NULL              |                |
| Tidspunkt     | timestamp  | NO   | MUL | CURRENT_TIMESTAMP |                |
| Køn           | int(1)     | NO   |     | NULL              |                |
+---------------+------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)

This is the query:

        SELECT * FROM ( 
                    SELECT * 
                        FROM Handlinger 
                        AS a 
                        WHERE date(Tidspunkt) = curdate() 
                        AND Tidspunkt = ( 
                            SELECT max(Tidspunkt) 
                            FROM Handlinger 
                            AS b 
                            WHERE a.MedlemsID = b.MedlemsID 
                            AND a.Klubtype = b.Klubtype 
                            ) 
                            ) 
                            AS c 
                            WHERE Handling=1 
                            AND KlubID=1 
                            ORDER BY Medlemsnummer ASC

And this is the result:

+-------+-----------+---------------+----------+--------+----------+---------------------+-------+
| id    | MedlemsID | Medlemsnummer | Handling | KlubID | Klubtype | Tidspunkt           | Køn   |
+-------+-----------+---------------+----------+--------+----------+---------------------+-------+
| 24743 |       613 |            10 | 1        |      1 | UK       | 2011-08-10 12:14:10 | Pige  |
| 24733 |       572 |            11 | 1        |      1 | UK       | 2011-08-10 10:45:00 | Pige  |
| 24731 |       705 |            13 | 1        |      1 | FK       | 2011-08-10 10:31:35 | Dreng |
| 24740 |       409 |            51 | 1        |      1 | FK       | 2011-08-10 11:48:19 | Dreng |
| 24757 |       443 |            88 | 1        |      1 | FK       | 2011-08-10 15:01:43 | Dreng |
| 24730 |       723 |            89 | 1        |      1 | FK       | 2011-08-10 10:30:13 | Dreng |
| 24749 开发者_StackOverflow|       465 |           110 | 1        |      1 | FK       | 2011-08-10 13:29:40 | Dreng |
| 24720 |       483 |           129 | 1        |      1 | FK       | 2011-08-10 10:03:43 | Dreng |
| 24725 |       748 |           181 | 1        |      1 | FK       | 2011-08-10 10:12:11 | Dreng |
| 24748 |       540 |           207 | 1        |      1 | FK       | 2011-08-10 13:26:54 | Dreng |
+-------+-----------+---------------+----------+--------+----------+---------------------+-------+
10 rows in set (2.95 sec)

I advance thank you very much for your help.

EDIT some description:

The application itself works like a check-in system for members in a club. Every time a member logs in or out it will be registered in the DB. In order for the app to be useful it has to show who are online on any given time of the day. So my query is getting all the logins for the day and if the recent activity is login then it will be outputted to the app. All that works like a charm but it's not that user friendly if they have to wait multiple seconds in order to see who is online and who is not.

Hope that clears my objective a little. :)

Your query is WAYYYY overloaded... since you are expecting a specific date, I would have a compound index on

date( TidSpunkt ), Handling, KlubID, MedlemsID

Then, try the following... Since the Table uses auto-increment, the highest number would imply the most recent date... So, we can get the max ID per MedlemsID to allow a faster join on only those that qualify

Select STRAIGHT_JOIN
      H1.*
   from 
      ( select 
              H2.MedlemsID, 
              max( H2.ID ) LastID
           from 
              Handlinger H2
           where
                  date( H2.Tidspunkt ) = curdate()
              AND H2.Handling = 1
              AND H2.KlubID = 1
           group by 
              H2.MedlemsID ) PreQuery
      JOIN Handlinger H1
         On PreQuery.LastID = H1.ID
   order by 
      H1.Medlemsnummer ASC

Did you have indexed your table ?

If not, you should create two index :

• one on medlemsID
• and the other one on klubtype

Try first only on medlemsID, it should already be much better.

I don't know why you wrote these sub-queries:

            SELECT * 
                FROM Handlinger 
                AS a 
                WHERE date(Tidspunkt) = curdate() 
                AND Tidspunkt = ( 
                    SELECT max(Tidspunkt) 
                    FROM Handlinger 
                    AS b 
                    WHERE a.MedlemsID = b.MedlemsID 
                    AND a.Klubtype = b.Klubtype 
                    ) 

Pay attention: you're not using an index because of that date(Tidspunkt) phrase. Try:

SELECT `a`.* FROM Handlinger AS `a`
INNER JOIN (
    SELECT `id` FROM Handlinger WHERE
        Tidspunkt >= CONCAT(LEFT(NOW(), 10), ' 00:00:00')
        AND Handling=1 
        AND KlubID=1 
) AS jnd USING (`id`)
ORDER BY `a`.Medlemsnummer ASC

Try this. The correlated subqueries have always been pretty costly. You will probably need a key using the 3 Join fields to make it quick.

SELECT a.*
FROM Handlinger as a
JOIN (SELECT medlemsID, KlubType, max(Tidspunkt) as maxTidspunkt
        FROM Handlinger
        GROUP BY medlemsID, KlubType) as b
  ON a.MedlemsID = b.MedlemsID
    AND a.KlubType = b.KlubType
    AND a.Tidspunkt = b.maxTidspunkt
WHERE a.Handling = 1
  AND a.KlubID = 1
  AND a.Tidspunkt = curdate()
ORDER BY a.Medlemsnummer ASC