[Swansea Hackspace] SQL Help
Graham Owens
grahamowensuk at googlemail.com
Wed May 20 20:35:10 BST 2015
Yeah deffo,
Im so glad we had that chat on Monday, there were some bits that I knew,
but some awesome additional info - you will also be please to not that I
have been through and fixed ALL the date usages in my code so that
everything is handled on the database end. :)
If you want a challenge, this is my next problem
http://stackoverflow.com/questions/30358486/mysql-query-using-id-in-subquery-or-join
Same as before, I have something that works, using PHP in the middle to
pass in variables to the sub-queries but its very inefficient and makes the
PHP almost unreadable.
Thanks,
G
On 20 May 2015 at 20:28, Emyr Morris <em at preseli.com> wrote:
> it is amazing how a bit of tidying up makes such a difference! Chuffed for
> you.
>
> Smashes that old saying "A problem shared is a problem halved"!
>
> Emyr
>
>
>
> On 20 May 2015 at 17:03, Graham Owens <grahamowensuk at googlemail.com>
> wrote:
>
>> Better??
>>
>> Column Type Null Default Comments xxxx_id int(11) No
>> xxxx_modified timestamp No CURRENT_TIMESTAMP xxxx_created timestamp
>> No 0000-00-00 00:00:00 xxxx_title varchar(16) No xxxx_first
>> varchar(50) No xxxx_last varchar(50) No xxxx_dob date No
>> xxxx_active int(4) No 0 xxxx_start_date date No
>> xxxx_exp_end_date date No xxxx_start_payment_value decimal(10,2)
>> No xxxx_start_payment_claim_date date Yes *NULL*
>> xxxx_outcome_payment_value decimal(10,2) No
>> xxxx_outcome_payment_claim_date date Yes *NULL*
>>
>> Column Type Null Default Comments ms_id int(11) No xxxx_id
>> int(11) No ms_weight tinyint(4) No ms_exp_date date No
>> ms_act_date date Yes *NULL* ms_claim_date date Yes *NULL*
>> ms_value decimal(6,2) No
>>
>>
>> SELECT EXTRACT(YEAR_MONTH FROM `calendar_month`.`calendar_month_date`) AS
>> period, COUNT(*) as ms_count, IFNULL(SUM(`milestone_value`),0) as ms_value,
>> (
>> SELECT COUNT(*)
>> FROM `xxxx`
>> WHERE EXTRACT(YEAR_MONTH FROM `xxxx_exp_end_date`)=EXTRACT(YEAR_MONTH
>> FROM `calendar_month`.`calendar_month_date`) AND
>> `xxxx_outcome_payment_claim_date` IS NULL
>> ) as fin_count,
>> IFNULL((
>> SELECT SUM(`xxxx_outcome_payment_value`)
>> FROM `xxxx`
>> WHERE EXTRACT(YEAR_MONTH FROM `xxxx_exp_end_date`)=EXTRACT(YEAR_MONTH
>> FROM `calendar_month`.`calendar_month_date`) AND
>> `xxxx_outcome_payment_claim_date` IS NULL
>> ),0) as fin_value
>> FROM `ms`
>> RIGHT JOIN `calendar_month`
>> ON EXTRACT(YEAR_MONTH FROM `ms`.`ms_exp_date`)=EXTRACT(YEAR_MONTH FROM
>> `calendar_month`.`calendar_month_date`)
>> WHERE `calendar_month`.`calendar_month_date` BETWEEN DATE_FORMAT(NOW()
>> ,'%Y-%m-01') AND DATE_ADD(DATE_FORMAT(NOW() ,'%Y-%m-01'),INTERVAL 12 MONTH)
>> AND `ms`.`ms_claim_date` IS NULL
>> GROUP BY EXTRACT(YEAR_MONTH FROM `calendar_month`.`calendar_month_date`)
>> LIMIT 12
>>
>> Managed to get it down from *35.6576* seconds to *0.0139* seconds [?]
>>
>> Thanks everyone. Can this be made any more efficient, table row counts
>> for 'ms' just now are 10's of thousands, but will be into the several
>> hundred thousand (possibly millions within a year)
>>
>>
>>
>> On 18 May 2015 at 16:00, Emyr Morris <em at preseli.com> wrote:
>>
>>> I can show you some real world examples later of working tables full of
>>> data. I find it much easier to explain that way
>>>
>>>
>>> On Monday, May 18, 2015, Graham Owens <grahamowensuk at googlemail.com>
>>> wrote:
>>>
>>>> Justin, I think you might be right - we can thrash it out a bit more
>>>> later
>>>>
>>>> Thanks for all the input, once we have a chat later - and formalize the
>>>> problem better (there are normalization rules????? - just kidding - i was
>>>> between a rock and a hard place, this works - just needs improved)
>>>>
>>>> If it still is giving me problems, we can pick up the conversation then.
>>>>
>>>> Thanks
>>>>
>>>> G
>>>>
>>>> On 18 May 2015 at 15:06, Justin Mitchell <justin at discordia.org.uk>
>>>> wrote:
>>>>
>>>>> On Mon, 2015-05-18 at 14:24 +0100, Graham Owens wrote:
>>>>>
>>>>>
>>>>> > table1
>>>>> > table1.id: autonum
>>>>> > active: bool
>>>>> > t2id_1: int
>>>>> > t2id_2: int
>>>>> > t2id_3: int
>>>>> > t2id_4: int
>>>>> > t2id_5: int
>>>>> > t2id_6: int
>>>>> >
>>>>> >
>>>>> >
>>>>> > table2
>>>>> > table2.id: autonum
>>>>> > expected_date: datetime
>>>>> > actual_date: datetime
>>>>> > claim_date: datetime
>>>>> > value: datetime
>>>>>
>>>>> There are several questions about the data structure that need to be
>>>>> asked here, will one table2 entry only be used once, or could it be
>>>>> referenced by multiple table1 entries ?
>>>>>
>>>>> if the former, then the t2id_* fields are unnessary
>>>>> if the latter, then they should be replaced with a link table.
>>>>>
>>>>> but the list might not be the place to thrash this out, will see you
>>>>> later.
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Hackspace mailing list
>>>>> Hackspace at swansea.hackspace.org.uk
>>>>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>>>>
>>>>
>>>>
>>>
>>> --
>>> This e-mail and any files transmitted with it are confidential and
>>> intended solely for the use of the individual or entity to whom they are
>>> addressed.
>>>
>>> Mae'r e-bost hwn ac unrhyw ffeiliau a drosglwyddir gydag ef yn
>>> gyfrinachol ac at ddefnydd yr unigolyn neu'r corff y cyfeiriwyd hwy atynt
>>> yn unig.
>>>
>>>
>>> _______________________________________________
>>> Hackspace mailing list
>>> Hackspace at swansea.hackspace.org.uk
>>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>>
>>>
>> On 18 May 2015 at 16:00, Emyr Morris <em at preseli.com> wrote:
>>
>>> I can show you some real world examples later of working tables full of
>>> data. I find it much easier to explain that way
>>>
>>>
>>> On Monday, May 18, 2015, Graham Owens <grahamowensuk at googlemail.com>
>>> wrote:
>>>
>>>> Justin, I think you might be right - we can thrash it out a bit more
>>>> later
>>>>
>>>> Thanks for all the input, once we have a chat later - and formalize the
>>>> problem better (there are normalization rules????? - just kidding - i was
>>>> between a rock and a hard place, this works - just needs improved)
>>>>
>>>> If it still is giving me problems, we can pick up the conversation then.
>>>>
>>>> Thanks
>>>>
>>>> G
>>>>
>>>> On 18 May 2015 at 15:06, Justin Mitchell <justin at discordia.org.uk>
>>>> wrote:
>>>>
>>>>> On Mon, 2015-05-18 at 14:24 +0100, Graham Owens wrote:
>>>>>
>>>>>
>>>>> > table1
>>>>> > table1.id: autonum
>>>>> > active: bool
>>>>> > t2id_1: int
>>>>> > t2id_2: int
>>>>> > t2id_3: int
>>>>> > t2id_4: int
>>>>> > t2id_5: int
>>>>> > t2id_6: int
>>>>> >
>>>>> >
>>>>> >
>>>>> > table2
>>>>> > table2.id: autonum
>>>>> > expected_date: datetime
>>>>> > actual_date: datetime
>>>>> > claim_date: datetime
>>>>> > value: datetime
>>>>>
>>>>> There are several questions about the data structure that need to be
>>>>> asked here, will one table2 entry only be used once, or could it be
>>>>> referenced by multiple table1 entries ?
>>>>>
>>>>> if the former, then the t2id_* fields are unnessary
>>>>> if the latter, then they should be replaced with a link table.
>>>>>
>>>>> but the list might not be the place to thrash this out, will see you
>>>>> later.
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Hackspace mailing list
>>>>> Hackspace at swansea.hackspace.org.uk
>>>>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>>>>
>>>>
>>>>
>>>
>>> --
>>> This e-mail and any files transmitted with it are confidential and
>>> intended solely for the use of the individual or entity to whom they are
>>> addressed.
>>>
>>> Mae'r e-bost hwn ac unrhyw ffeiliau a drosglwyddir gydag ef yn
>>> gyfrinachol ac at ddefnydd yr unigolyn neu'r corff y cyfeiriwyd hwy atynt
>>> yn unig.
>>>
>>>
>>> _______________________________________________
>>> Hackspace mailing list
>>> Hackspace at swansea.hackspace.org.uk
>>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>>
>>>
>>
>> _______________________________________________
>> Hackspace mailing list
>> Hackspace at swansea.hackspace.org.uk
>> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>>
>>
>
>
> --
> This e-mail and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they are
> addressed.
>
> Mae'r e-bost hwn ac unrhyw ffeiliau a drosglwyddir gydag ef yn gyfrinachol
> ac at ddefnydd yr unigolyn neu'r corff y cyfeiriwyd hwy atynt yn unig.
>
> _______________________________________________
> Hackspace mailing list
> Hackspace at swansea.hackspace.org.uk
> http://swansea.hackspace.org.uk/mailman/listinfo/hackspace
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://swansea.hackspace.org.uk/pipermail/hackspace/attachments/20150520/b71a96ff/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 360.gif
Type: image/gif
Size: 453 bytes
Desc: not available
URL: <http://swansea.hackspace.org.uk/pipermail/hackspace/attachments/20150520/b71a96ff/attachment.gif>
More information about the Hackspace
mailing list