Re: Query with > 500000 entities in in where clause crashes server
open files (-n) 1024 -- This could be raised (probably did not cause the issue at hand)
1.) Do we hit potential mysql server limits? -- well, could you show us the MySQL limits you have; especially
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%size';
This index should help:
INDEX(CustomerId, NetworkPlacementId, SubNetworkPlacementId, Date, PlacementId)
And this could possibly be removed (since the above index handles the case):
KEY `IDX_CustomerId` (`CustomerId`),
and ( a.Date >= 20100430 and a.Date <= 20100430)
-->
and ( a.Date = 20100430 )
so that my suggested index will work better. You can do that by conditionally building that part of the WHERE clause in your programming language.
group by WebsiteId, CustomerId, CampaignId, PlacementId -- Why do you have this? You don't have any aggregates; you could use DISTINCT instead; you don't have an ORDER BY.
`PlacementId` int(11) NOT NULL default '0', -- are there potentially 2 billion "placements"? (Or do I not understand what a placement is?) Consider a TINYINT UNSIGNED (1 byte, max of 255) or SMALLINT UNSIGNED (2 bytes, max of 65K) instead of a 4-byte INT.
"only way out is then to reboot the whole server" -- No. You could connect via the commandline 'mysql', do SHOW PROCESSLIST, spot the 'pid', then execute 'kill' with that pid.
Other info that would help:
* SHOW TABLE STATUS LIKE 'NetworkData'\G -- size
* EXPLAIN SELECT ...\G -- clues of inefficiencies
and surround them with [ code ] and [ / code ]
Are you running 64-bit OS? 64-bit MySQL?