Hi CPU usage after database restore

By the way, it's not like there was anything wrong with the old copy of the database before it was restored. It seems that it is restoring it which causes the problems.

I have tracked down the query that is taking so long:
SELECT COUNT(*) FROM (SELECT node.nid AS nid FROM node node LEFT JOIN content_type_provider node_data_field_work_address ON node.vid = node_data_field_work_address.vid LEFT JOIN location location_node_data_field_work_address ON node_data_field_work_address.field_work_address_lid = location_node_data_field_work_address.lid LEFT JOIN location_phone location_node_data_field_work_address__location_phone ON location_node_data_field_work_address.lid = location_node_data_field_work_address__location_phone.lid LEFT JOIN location_instance location_instance ON node.vid = location_instance.vid LEFT JOIN location location ON location_instance.lid = location.lid WHERE (location_node_data_field_work_address.latitude > 6.1841078828785 AND location_node_data_field_work_address.latitude < 78.579750117121 AND location_node_data_field_work_address.longitude > -124.18274484734 AND location_node_data_field_work_address.longitude < -18.015415152657) AND (node.status <> 0) AND (node.type in ('facility', 'provider')) ) count_alias;

As I mentioned before, it is only slow when I run it after restoring from mysqldump file. Weird...

Actually, it's looking like this has to do with the location module, specifically the location_instance table. That is what is slowing things down in the query. I guess I'll go poke around over there.

Perhaps. How do I rebuild them? with the repair command?
I have narrowed it down even further:
SELECT COUNT(*)
FROM node node
LEFT JOIN location_instance location_instance ON node.vid = location_instance.vid

Here is another piece of information:
I have two servers, one health and the other not. When I run explain on that query the key difference seems to be that in the healthy server the location_instance type is "ref", whereas in the unhealthy server it is index.

I just managed to fix this. In the end what worked was loading the dump file into a separate database, exporting it with phpMyAdmin and then importing that into the live database. Comparing the mysqldump file against the phpMyAdmin, it looks like the mysqldump file has some extra keys.