[nycphp-talk] MySQL: count()children on 2 related tables in 1 query
David Mintz
david at davidmintz.org
Tue Jan 4 15:56:31 EST 2011
On Tue, Jan 4, 2011 at 3:44 PM, David Mintz <david at davidmintz.org> wrote:
>
>
> On Tue, Jan 4, 2011 at 1:33 PM, Rob Marscher <rmarscher at beaffinitive.com>wrote:
>
>> Looks like you need an index on events.event_type_id
>> and requests.event_type_id. I'm not seeing those in your indexes. I see
>> event_type_id is part of the uniqueRequest index, but it can't use it unless
>> it's the first column in the index or you specify the columns that come
>> before it in your index in your where clause.
>>
>> Sometimes queries like this are better off being split into multiple
>> queries... but I think in this case if you just add the two event_type_id
>> indexes, you should be fine.
>>
>>
>>
Guess what. The logic of my SQL must be wrong. I finally let it think for
nearly 12 minutes and the results were f***ed up.
+----+--------------------------------+------------------+--------------------+
| id | name | count(events.id) | count(requests.id)
|
+----+--------------------------------+------------------+--------------------+
| 1 | probation interview | 7159702 |
7159702 |
| 2 | conference | 23027697 |
23027697 |
| 3 | plea | 2980259 |
2980259 |
| 4 | atty/client interview | 8565 |
0 |
| 5 | sentence | 12243452 |
12243452 |
| 6 | pretrial services | 2887 |
0 |
| 7 | trial | 239148 |
239148 |
| 8 | presentment | 3729 |
3729 |
| 9 | bond | 780 |
0 |
| 10 | detention hearing | 2930 |
2930 |
| 11 | identity hearing | 30 |
0 |
all those count() values that are the same in both columns are way way too
large. mysql does not understand what I mean.
--
David Mintz
http://davidmintz.org/
It ain't over:
http://www.healthcare-now.org/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20110104/fabfeea9/attachment.html>
More information about the talk
mailing list