I am trying a simple conversion - a mysql varchar column which contains longitude values (eg "-1.234" to -1.234) to enhance optimisation.
I have read and read on this and nothing works, last tried was:
UPDATE latlng_data SET lng_copy = CAST(lng AS DECIMAL(6,4));
met with:
Error Code: 1366. Incorrect decimal value: '' for column '' at row -1
Target column, created to recover from errors is:
FLOAT(6,4)
and null allowed.
Why is mysql error messages so useless.
Following 'dognose' advice (below) I have used:
UPDATE latlng_data SET lng='999.9999' where lng='';-- to give invalid lng in this case
then increase the copy data field (with the aim to rename it orig) and then:
UPDATE latlng_data SET lng_copy = CAST(lng AS DECIMAL(7,4));
this appears to be required in mysql cmd as Workbench timesout but using:
show full processlist;
still shows it as running - so here the best monitor is cmd.
Checked this with:
SELECT * FROM t31data_happyenergy.latlng_data where lng_copy <> CAST(lng AS DECIMAL(7,4));
which now returns no results and can be better optimised as all are numerical, with bad data fully valued for a longitude.
I also recommend using a mysql command window as most apps timeout when extensive queries are used with this large data.
So basically the process went [ALWAYS CHECK BETWEEN EACH QUERY AS I GOT LAZY AND HAD TO START AGAIN AS ONE QUERY WAS WRONG):
-- convert all mostly read only tables to myisam:
-- dropped old lat and lng after checking same data by
SELECT count(*) FROM latlng_postcode where CAST(lat AS DECIMAL(7,4))!=latcopy;
SELECT count(*) FROM latlng_postcode where CAST(lng AS DECIMAL(7,4))!=lngcopy;
ALTER TABLE `latlng_postcode`
CHANGE COLUMN `latcopy` `lat` FLOAT(7,4) NULL DEFAULT NULL,
CHANGE COLUMN `lngcopy` `lng` FLOAT(7,4) NULL DEFAULT NULL;
-- then index (are these the best settings ??
ALTER TABLE `latlng_postcode`
DROP INDEX `pcode` ,
ADD INDEX `pcode` USING BTREE (`postcode`(5) ASC),
ADD INDEX `lat` USING BTREE (`lat`(4) ASC),
ADD INDEX `lng` USING BTREE (`lng`(4) ASC);