The reason my query failed is because there was a comma after the last column. Remove that, add the semicolon and you should be right to go. I would be interested in the performance difference between my query and r937's with the inner join. There probably won't be any perceptible difference unless the data set is massive, but you can check out the execution plan and/or statistics.

Cheers,D.

risoknop
—
2009-10-08T10:38:18Z —
#13

Performance:

My query: 0.02s, 0.01s, 0.02s, 0.02s, 0.02s

rudy's query: 0.02s, 0.02s, 0.02s, 0.02s, 0.01s

disgracian's query: 0.01s, 0.01s, 0.00s, 0.02s, 0.02s

EDIT: I have run each query 5 times.

EDIT2: Sorry, now it's correct.

risoknop
—
2009-10-08T10:43:56Z —
#14

Seem's like yours is the fastest, disgracian

risoknop
—
2009-10-08T10:55:52Z —
#15

Interesting thing is that all three queries return the rows in different order. But they all return the same rows.

disgracian
—
2009-10-09T23:03:59Z —
#16

I would say that the performance results are inconclusive overall. You would either need 5-10 times more data before performance started to really diverge.

That is quite interesting about the order, and I'm not sure why that would happen. You can simply use an ORDER BY clause on any of the SELECT fields if you want to enforce a particular order.

Cheers,D.

risoknop
—
2009-10-09T23:59:48Z —
#17

The different order is probably caused by different programming of subselects and joins in MySQL. Subselects must be using different algorithm.

Mittineague
—
2014-09-24T03:23:28Z —
#18

This topic is now archived. It is frozen and cannot be changed in any way.