NYCPHP Meetup

NYPHP.org

[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