Hey,
I was just wondering if anyone could take a look at these queries and drop me some tips on improvements/suggestions on improving the method.
Heres the first:
UPDATE tablea INNER JOIN tableb ON (LEFT(tablea.forename, 1)=LEFT(tableb.forename, 1) AND tablea.surname=tableb.surname AND LEFT(tablea.addr1, 5)=LEFT(tableb.addr1, 5) AND tablea.postcode=tableb.postcode)SET tablea.dupe=“1”;
It’s to compare two tables and find the duplicates between them then flag a boolen in the “dupe” column of tablea. Both tables are structured the same,
DROP TABLE IF EXISTS gas; CREATE TABLE gas ( FORENAME varchar(20) NOT NULL, SURNAME varchar(20) NOT NULL, ADDR1 varchar(30) NOT NULL, ADDR2 varchar(30) NOT NULL, ADDR3 varchar(30) NOT NULL, TOWN varchar(30) NOT NULL, COUNTY varchar(30) NOT NULL, POSTCODE varchar(8) NOT NULL, URN varchar(10) NOT NULL, KEY Index (FORENAME,SURNAME,ADDR1,POSTCODE) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
At the moment this is just running on my machine so the computer spec’s are fairly irrelivant as it could end up running on any machine.
I actualy don’t have too many problems with this query, it’s just a little slow; i was wondering what the most efficient index’s would be (i’ve tried quite a few combinations of all the fields with very little improvement) and if anyone has suggestions for improving the efficiencey/performance…
Heres the other:
SELECT * FROM (tablea LEFT JOIN tableb ON (tablea.lastname=tableb.lastname AND LEFT(CONCAT(tablea.premises, " ", tablea.street), 5)=LEFT(tableb.addr1, 5) AND tablea.postcode=tableb.postcode)) LEFT JOIN tablec ON tableb.postcode = tablec.postcode WHERE tableb.lastname=“LASTNAME”;
The idea is to join 3 tables together (the data within all the tables is constantly changing), all of which contain different information on people then look up a particular person within the result set.
Again, not a huge amount of problems with the query, its just another general performance question but sometimes the tables being joined (tableb/tablec) have “null” for all their fields.
Also considering the address in one table is split between house number (tablea.premises) and street (tablea.street) and the other table with address info is in one field (tablea.addr1) can anyone suggest the best fields to index?
I’m not a MYSQL DBA or anything, these are queries for a couple of friends (and so sadly i don’t have control over table structures, just index’s) so i’d appreciate someone with a little more knowlage than me giving them a quick look.
Thanks in advance.