How can i improve query for efficiency in mysql

How can i improve query for efficiency in mysql

Senario: I'm working with two tables, (structure below)
expenses_tb person_expenses_tb
+----+-----------+ +----+------+-------------+-----------+--------+
| id | expenses | | id | year | expenses_id | person | amount |
+----+-----------+ +----+------+-------------+-----------+--------+
| 1 | Bus | | 1 | 2007 | 1 | Will | 20 |
| 2 | Food | | 2 | 2007 | 2 | Will | 200 |
| 3 | Clothes | | 3 | 2007 | 4 | Will | 1000 |
| 4 | Girlfriend| | 4 | 2007 | 5 | Will | 20 |
| 5 | Taxi | | 5 | 2008 | 3 | Will | 500 |
+----+-----------+ | 6 | 2008 | 5 | Will | 100 |
| 7 | 2008 | 2 | Holly | 200 |
| 8 | 2007 | 5 | Holly | 850 |
| .. | 2013 | ... | .... | ... |
I have tried two different queries.
SELECT person, expenses,
CASE expense_id WHEN 1 THEN amount END AS 'bus',
CASE expense_id WHEN 2 THEN amount END AS 'food',
CASE expense_id WHEN 3 THEN amount END AS 'clothes',
CASE expense_id WHEN 4 THEN amount END AS girlfriend',
CASE expense_id WHEN 5 THEN amount END AS 'taxi'
FROM person_expenses_tb p
JOIN expenses e ON e.id=p.expenses_id
WHERE p.year = 2008
GROUP BY p.person
The query above runs fast but does not produce the desired output.
Second query i tried was
SELECT person, expenses,
(SELECT amount FROM person_expenses_tb p_bus WHERE expense_id = 1 AND
p_bus.person = p.person AND year=2008) AS 'bus',
(SELECT amount FROM person_expenses_tb p_bus WHERE expense_id = 2 AND
p_bus.person = p.person AND year=2008) AS 'food',
(SELECT amount FROM person_expenses_tb p_bus WHERE expense_id = 3 AND
p_bus.person = p.person AND year=2008) AS 'clothes',
(SELECT amount FROM person_expenses_tb p_bus WHERE expense_id = 4 AND
p_bus.person = p.person AND year=2008) AS girlfriend',
(SELECT amount FROM person_expenses_tb p_bus WHERE expense_id = 5 AND
p_bus.person = p.person AND year=2008) AS 'taxi'
FROM person_expenses_tb p
JOIN expenses e ON e.id=p.expenses_id
WHERE p.year = 2008
GROUP BY p.person
this query produced the right result but its extremely slow when the
[person_expenses_tb] has over 2000 records.
the desired result for 2007: I have a i pass the requested year to the query.
+--------+------+-----+------+---------+------------+------+
| person | Year | Bus | Food | Clothes | Girlfriend | Taxi |
+--------+------+-----+------+---------+------------+------+
| Will | 2007 | 20 | 20 | 0 | 1000 | 20 |
| Holly | 2007 | 0 | 0 | 0 | 0 | 850 |
| ... | ... | ... | ... | ... | ... | ... |
i would love help on how to improve the query for it to run faster and if
there is another way of getting the desired output i would appreciate the
help.
Thank you.