I would consider using a date range as the partitioning scheme, and splitting by week which you can do using a function against the timestamp field.
Using days would create too many partitions, and months would not help your queries much especially when the range spans two months.
Using the range partition, you can create mutliple partitions covering your active time period, and out into the future for a year, then nearer the end of that range you can add partitions to the schema as required.
If necessary, you can also drop the old partitions when they are no longer needed.
I can't prepare a sample partition schema for you, as I'm running short of time at the moment, but the MySQL docs have pretty good coverage for this type of partitioning.
Hope that helps,
Dave
I would need more information about your business restrictions to write an ideal query, but here's my advice given my interpretation of what you've said so far.
First thing you need to do is more clearly define what you are trying to achieve.
So in cases where an article node is related to another node twice, both as a section and as a department, I would like to COUNT that only once.
Since your final result set will be grouped by departments and sections, "counting it only once" means you need to pick where they should be counted. For example:
content_field_department content_field_section
+-----+----------------------+ +-----+-------------------+
| nid | field_department_nid | | nid | field_section_nid |
+-----+----------------------+ +-----+-------------------+
| 1 | 2 | | 1 | 3 |
+-----+----------------------+ +-----+-------------------+
should nid
1 be counted in the list for department nid
2 or counted for section nid
3? Based on your question, you may want to only count it for departments. In your current query drafts, you are trying to exclude the issue case from both counts, meaning you are effectively counting it 0 times instead of 1 (your goal) or 2 (what you are trying to avoid).
Here's a draft query as a starting point:
select
DeptOrSect,
nid,
title,
count(1)
from (
select
'DEPT' as DeptOrSect,
dept.nid,
dept.title,
link.nid as article_nid
from node dept
join content_field_department link on link.field_department_nid = dept.nid
union all
select
'SECT' as DeptOrSect,
sect.nid,
sect.title,
link.nid as article_nid
from node sect
join content_field_section link on link.field_section_nid = sect.nid
where not exists (
select 1
from content_field_department dept
where dept.nid = link.nid
)
) as x
group by DeptOrSect,
nid,
title;
Best Answer
@Abdul's answer just needs
HAVING COUNT(*) > 1
The query shold look like this now
Here is the execution of it:
If you do not care for the count, remove it from the SELECT clause
Give it a Try !!!