0

I have a database ~800k records showing ticket purchases. All tables are InnoDB. The slow query is:

SELECT e.id AS id, e.name AS name, e.url AS url, p.action AS action, gk.key AS `key`
FROM event AS e
    LEFT JOIN participation AS p ON p.event=e.id
    LEFT JOIN goldenkey AS gk ON gk.issuedto=p.person
WHERE p.person='139160'
    OR p.person IS NULL;

This query is coming from PDO hence quoting of p.person. All columns used in JOINs and WHERE are indexed. p.event is foreign key constrained to e.id and gk.issuedto and p.person are foreign key constrained to an unmentioned table, person.id. All these are INTs. The table e is small - only 10 rows. Table p is ~500,000 rows and gk is empty at this time.

This query runs on a person's details page. We want to get a list of all events, then if there is a participation row their participation and if there is a golden key row then their golden key.

Slow query log gives:

Query_time: 12.391201  Lock_time: 0.000093 Rows_sent: 2  Rows_examined: 466104

EXPLAIN SELECT gives:

+----+-------------+-------+------+---------------+----------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref            | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+----------------+------+-------------+
|  1 | SIMPLE      | e     | ALL  | NULL          | NULL     | NULL    | NULL           |   10 |             |
|  1 | SIMPLE      | p     | ref  | event         | event    | 4       | msadb.e.id     |  727 | Using where |
|  1 | SIMPLE      | gk    | ref  | issuedto      | issuedto | 4       | msadb.p.person |    1 |             |
+----+-------------+-------+------+---------------+----------+---------+----------------+------+-------------+

This query runs at 7~12 seconds on first run for a given p.person then <0.05s in future. Dropping the OR p.person IS NULL does not improve query time. This query slowed right down when the size of p was increased from ~20k to ~500k (import of old data).

Does anyone have any suggestions on how to improve performance? Remembering overall aim is to retrieve a list of all events, then if there is a participation row their participation and if there is a golden key row then their golden key. If multiple queries will be more efficient I can do that.

Improve this question
2
  • 1
    Seeing that you're doing a join on p.event and e.id, gk.issuedto and p.person, it might help to build indices on those columns. ... slowness tag... you made my day, sir. Commented Jul 25, 2011 at 1:25
  • Thanks @bdares, glad you enjoyed the tag ;). I didn't list it but in fact e.id, gk.issuedto and p.person are all already indexed. Commented Jul 25, 2011 at 1:29

4 Answers 4

Reset to default
1

If you can do away with p.person IS NULL try the following and see if it helps:

SELECT e.id AS id, e.name AS name, e.url AS url, p.action AS action, gk.key AS `key`
FROM event AS e
    LEFT JOIN participation AS p ON (p.event=e.id AND p.person='139160')
    LEFT JOIN goldenkey AS gk ON gk.issuedto=p.person
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

won't that will give a possibly different result set?
@Mitch I just took the where condition into the join condition and removed the IS NULL. This way the filtering happens at join time instead of after all the records have been matched. At least thats the idea :)
: moving where criteria into a join condition can lead to different result sets
@Mitch can you elaborate that. My understanding is that the join without AND p.person='139160' brings in all matching records from the participation table and then the where would filter on p.person='139160', so if i move to join, MySQL matches now on 2 filters instead of one (fewer rows) , so it should be faster.
This does return a slightly different result set (in that it doesn't return e.* where there's no matching p.person) but it runs about 30x faster. I'll use this plus a SELECT .. FROM event, then PHP to get the union, all up will run in <0.5s instead of 10s. Perfect for my case.
|
0

For grins... Add the keyword "STRAIGHT_JOIN" to your select...

SELECT STRAIGHT_JOIN   ... rest of query...
Improve this answer

Comments

0

I'm not sure how many indexes you have and schema of your table, but try avoid using null values by default, it can slow down your queries dramatically.

Improve this answer

Comments

0

If you are doing a lookup for one particular person, which I'm guessing you are since you have the person id filter in there. I would try and reverse the query, so you are first searching though the person table and then making a union to and additional query which gives you all the events. 

SELECT 
     e.id AS id, e.name AS name, e.url AS url, 
     p.action AS action, gk.key AS `key`
FROM person AS p
    JOIN event AS e ON p.event=e.id
    LEFT JOIN goldenkey AS gk ON gk.issuedto=p.person

UNION

SELECT
    e.id AS id, e.name AS name, e.url AS url,
    NULL, NULL
FROM event AS e

This would obviously mean you have a duplicate event in case the first query matches, but thats easily solved by wrapping a select around the whole thing, or maybe by using a variable and selecting the e.id into that in the first query and using that variable in the second query (not sure if this will work though, haven't tested it, cant see why not though).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.