<div dir="ltr">Better??<div><br></div><div><table style="width:100%">
<thead>
<tr>
<th>Column</th>
<th>Type</th>
<th>Null</th>
<th>Default</th>
<th>Comments</th>
</tr>
</thead>
<tbody>
<tr><td> xxxx_id
</td>
<td>int(11)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_modified
</td>
<td>timestamp<bdo dir="ltr"></bdo></td>
<td>No </td>
<td>CURRENT_TIMESTAMP </td>
<td> </td>
</tr>
<tr><td> xxxx_created
</td>
<td>timestamp<bdo dir="ltr"></bdo></td>
<td>No </td>
<td>0000-00-00 00:00:00 </td>
<td> </td>
</tr>
<tr><td> xxxx_title
</td>
<td>varchar(16)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_first
</td>
<td>varchar(50)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_last
</td>
<td>varchar(50)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_dob
</td>
<td>date<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_active
</td>
<td>int(4)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td>0 </td>
<td> </td>
</tr>
<tr><td> xxxx_start_date
</td>
<td>date<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_exp_end_date
</td>
<td>date<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_start_payment_value
</td>
<td>decimal(10,2)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_start_payment_claim_date
</td>
<td>date<bdo dir="ltr"></bdo></td>
<td>Yes </td>
<td><i>NULL</i> </td>
<td> </td>
</tr>
<tr><td> xxxx_outcome_payment_value
</td>
<td>decimal(10,2)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_outcome_payment_claim_date
</td>
<td>date<bdo dir="ltr"></bdo></td>
<td>Yes </td>
<td><i>NULL</i> </td>
<td> </td>
</tr>
</tbody>
</table><br></div><div><br></div><div><table style="width:100%">
<thead>
<tr>
<th>Column</th>
<th>Type</th>
<th>Null</th>
<th>Default</th>
<th>Comments</th>
</tr>
</thead>
<tbody>
<tr><td> ms_id
</td>
<td>int(11)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> xxxx_id
</td>
<td>int(11)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> ms_weight
</td>
<td>tinyint(4)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> ms_exp_date
</td>
<td>date<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
<tr><td> ms_act_date
</td>
<td>date<bdo dir="ltr"></bdo></td>
<td>Yes </td>
<td><i>NULL</i> </td>
<td> </td>
</tr>
<tr><td> ms_claim_date
</td>
<td>date<bdo dir="ltr"></bdo></td>
<td>Yes </td>
<td><i>NULL</i> </td>
<td> </td>
</tr>
<tr><td> ms_value
</td>
<td>decimal(6,2)<bdo dir="ltr"></bdo></td>
<td>No </td>
<td> </td>
<td> </td>
</tr>
</tbody>
</table><br></div><div><br></div><div><div><br></div><div><div>SELECT EXTRACT(YEAR_MONTH FROM `calendar_month`.`calendar_month_date`) AS period, COUNT(*) as ms_count, IFNULL(SUM(`milestone_value`),0) as ms_value,</div><div><span style="white-space:pre"> </span>(</div><div><span style="white-space:pre"> </span>SELECT COUNT(*)</div><div><span style="white-space:pre"> </span>FROM `xxxx`</div><div><span style="white-space:pre"> </span>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</div><div><span style="white-space:pre"> </span>) as fin_count,</div><div><span style="white-space:pre"> </span>IFNULL((</div><div><span style="white-space:pre"> </span>SELECT SUM(`xxxx_outcome_payment_value`)</div><div><span style="white-space:pre"> </span>FROM `xxxx`</div><div><span style="white-space:pre"> </span>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</div><div><span style="white-space:pre"> </span>),0) as fin_value</div><div>FROM `ms`</div><div>RIGHT JOIN `calendar_month`</div><div>ON EXTRACT(YEAR_MONTH FROM `ms`.`ms_exp_date`)=EXTRACT(YEAR_MONTH FROM `calendar_month`.`calendar_month_date`)</div><div>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</div><div>GROUP BY EXTRACT(YEAR_MONTH FROM `calendar_month`.`calendar_month_date`)</div><div>LIMIT 12</div></div></div><div><br></div><div>Managed to get it down from <font size="6"><b>35.6576</b></font> seconds to <b><font size="6">0.0139</font></b> seconds <img src="cid:360@goomoji.gmail" goomoji="360" style="margin: 0px 0.2ex; vertical-align: middle;"></div><div><br></div><div>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)</div><div class="gmail_extra"><br></div><div class="gmail_extra"><br></div><div class="gmail_extra"><br><div class="gmail_quote">On 18 May 2015 at 16:00, Emyr Morris <span dir="ltr"><<a href="mailto:em@preseli.com" target="_blank">em@preseli.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">I can show you some real world examples later of working tables full of data. I find it much easier to explain that way<div class=""><div class="h5"><br><br>On Monday, May 18, 2015, Graham Owens <<a href="mailto:grahamowensuk@googlemail.com" target="_blank">grahamowensuk@googlemail.com</a>> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div dir="ltr">Justin, I think you might be right - we can thrash it out a bit more later<div><br></div><div>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)</div><div><br></div><div>If it still is giving me problems, we can pick up the conversation then.</div><div><br></div><div>Thanks</div><div><br></div><div>G</div></div><div class="gmail_extra"><br><div class="gmail_quote">On 18 May 2015 at 15:06, Justin Mitchell <span dir="ltr"><<a>justin@discordia.org.uk</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">On Mon, 2015-05-18 at 14:24 +0100, Graham Owens wrote:<br><br><br>> table1<br>> <a href="http://table1.id/" target="_blank">table1.id</a>: autonum<br>> active: bool<br>> t2id_1: int<br>> t2id_2: int<br>> t2id_3: int<br>> t2id_4: int<br>> t2id_5: int<br>> t2id_6: int<br>><br>><br>><br>> table2<br>> <a href="http://table2.id/" target="_blank">table2.id</a>: autonum<br>> expected_date: datetime<br>> actual_date: datetime<br>> claim_date: datetime<br>> value: datetime<br><br>There are several questions about the data structure that need to be<br>asked here, will one table2 entry only be used once, or could it be<br>referenced by multiple table1 entries ?<br><br>if the former, then the t2id_* fields are unnessary<br>if the latter, then they should be replaced with a link table.<br><br>but the list might not be the place to thrash this out, will see you<br>later.<br><div><br><br><br>_______________________________________________<br>Hackspace mailing list<br><a>Hackspace@swansea.hackspace.org.uk</a><br><a href="http://swansea.hackspace.org.uk/mailman/listinfo/hackspace" target="_blank">http://swansea.hackspace.org.uk/mailman/listinfo/hackspace</a><br></div></blockquote></div><br></div></blockquote><br><br></div></div><div class=""><div class="h5">-- <br><div dir="ltr">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.<br> <br>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.</div><br></div></div><br>_______________________________________________<br>Hackspace mailing list<br><a href="mailto:Hackspace@swansea.hackspace.org.uk">Hackspace@swansea.hackspace.org.uk</a><br><a href="http://swansea.hackspace.org.uk/mailman/listinfo/hackspace" target="_blank">http://swansea.hackspace.org.uk/mailman/listinfo/hackspace</a><br><br></blockquote></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On 18 May 2015 at 16:00, Emyr Morris <span dir="ltr"><<a href="mailto:em@preseli.com" target="_blank">em@preseli.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I can show you some real world examples later of working tables full of data. I find it much easier to explain that way<div class="HOEnZb"><div class="h5"><span></span><br><br>On Monday, May 18, 2015, Graham Owens <<a href="mailto:grahamowensuk@googlemail.com" target="_blank">grahamowensuk@googlemail.com</a>> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Justin, I think you might be right - we can thrash it out a bit more later<div><br></div><div>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)</div><div><br></div><div>If it still is giving me problems, we can pick up the conversation then.</div><div><br></div><div>Thanks</div><div><br></div><div>G</div></div><div class="gmail_extra"><br><div class="gmail_quote">On 18 May 2015 at 15:06, Justin Mitchell <span dir="ltr"><<a>justin@discordia.org.uk</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span>On Mon, 2015-05-18 at 14:24 +0100, Graham Owens wrote:<br>
<br>
<br>
</span><span>> table1<br>
> <a href="http://table1.id" target="_blank">table1.id</a>: autonum<br>
> active: bool<br>
> t2id_1: int<br>
> t2id_2: int<br>
> t2id_3: int<br>
> t2id_4: int<br>
> t2id_5: int<br>
> t2id_6: int<br>
><br>
><br>
><br>
> table2<br>
> <a href="http://table2.id" target="_blank">table2.id</a>: autonum<br>
> expected_date: datetime<br>
> actual_date: datetime<br>
> claim_date: datetime<br>
> value: datetime<br>
<br>
</span>There are several questions about the data structure that need to be<br>
asked here, will one table2 entry only be used once, or could it be<br>
referenced by multiple table1 entries ?<br>
<br>
if the former, then the t2id_* fields are unnessary<br>
if the latter, then they should be replaced with a link table.<br>
<br>
but the list might not be the place to thrash this out, will see you<br>
later.<br>
<div><div><br>
<br>
<br>
_______________________________________________<br>
Hackspace mailing list<br>
<a>Hackspace@swansea.hackspace.org.uk</a><br>
<a href="http://swansea.hackspace.org.uk/mailman/listinfo/hackspace" target="_blank">http://swansea.hackspace.org.uk/mailman/listinfo/hackspace</a><br>
</div></div></blockquote></div><br></div>
</blockquote><br><br></div></div><div class="HOEnZb"><div class="h5">-- <br><div dir="ltr"><div>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.<br> <br>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.</div></div><br>
</div></div><br>_______________________________________________<br>
Hackspace mailing list<br>
<a href="mailto:Hackspace@swansea.hackspace.org.uk">Hackspace@swansea.hackspace.org.uk</a><br>
<a href="http://swansea.hackspace.org.uk/mailman/listinfo/hackspace" target="_blank">http://swansea.hackspace.org.uk/mailman/listinfo/hackspace</a><br>
<br></blockquote></div><br></div>