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.
Members
Search
Help
Register
Login
Home
Query Optimization (2 queries)
