Hi everybody,
MySQL 5.0.21 running on RedHat EL4, 2GHz CPU, 2,5GB RAM, RAID5/128MB
RAM. At one point I had to issue the following query on a 1.8GB 42mil
records table:
SELECT COUNT(*) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ;
As it took forever to complete (I mean more than 20 minutes) I've
stopped the client and attempted to work around.
The table looks like this:
EXPLAIN geoRecord;
+-----------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+----------------+
| georecordkey | int(11) | NO | PRI | NULL | auto_increment |
| geobiorecordkey | int(11) | NO | MUL | 0 | |
| geolocationtext | varchar(85) | YES | MUL | NULL | |
| geoprecision | int(11) | YES | MUL | NULL | |
| geolatitude | float | YES | MUL | NULL | |
| geolongitude | float | YES | MUL | NULL | |
+-----------------+-------------+------+-----+---------+----------------+
The indexes on it look like this:
SHOW INDEX FROM geoRecord ;
+-----------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+-----------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| geoRecord | 0 | PRIMARY | 1 |
georecordkey | A | 47808182 | NULL | NULL | |
BTREE | |
| geoRecord | 1 | geobiorecordkey | 1 |
geobiorecordkey | A | 47808182 | NULL | NULL | |
BTREE | |
| geoRecord | 1 | geolocationtext | 1 |
geolocationtext | A | 11952045 | NULL | NULL | YES |
BTREE | |
| geoRecord | 1 | geoprecision | 1 |
geoprecision | A | 23904091 | NULL | NULL | YES |
BTREE | |
| geoRecord | 1 | geolatitude | 1 |
geolatitude | A | 5976022 | NULL | NULL | YES |
BTREE | |
| geoRecord | 1 | geolongitude | 1 |
geolongitude | A | 5976022 | NULL | NULL | YES |
BTREE | |
+-----------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
Also:
EXPLAIN SELECT COUNT(*) FROM geoRecord WHERE geoRecord.geolatitude IS
NOT NULL ;
+----+-------------+-----------+-------+---------------+-------------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-------------+---------+------+----------+--------------------------+
| 1 | SIMPLE | geoRecord | range | geolatitude | geolatitude |
5 | NULL | 44891097 | Using where; Using index |
+----+-------------+-----------+-------+---------------+-------------+---------+------+----------+--------------------------+
I've run a CHECK TABLE on it, it came OK too. I've run "ANALYZE TABLE
geoRecord " already. Attempting to solve the problem I've come up with this:
SELECT COUNT(georecordkey) FROM geoRecord WHERE geoRecord.geolatitude IS
NOT NULL ;
This query takes 1 min and 20 seconds and gives the right answer (still
way too long IMO) but then why on earth is the COUNT(*) behaving so bad?
If there's a mistake it's got to be so obvious that it eludes me. :-|
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
