# Query Optimization (Large Results) **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [September 19, 2007, 6:38am UTC](https://forums.percona.com/t/query-optimization-large-results/455 "2007-09-19T06:38:39Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![MonkeyTech](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/monkeytech/32/765_2.png) [@MonkeyTech](https://forums.percona.com/u/MonkeyTech)\ **Post date:** [September 19, 2007, 6:38am UTC](https://forums.percona.com/t/query-optimization-large-results/455/1 "2007-09-19T06:38:39Z")
Hey, Basically what i have is several large tables (14mill records or more each), people send in smaller tables (usually around 20 - 50,000 records) and we flag their records that match ours. Here is an example of the largest table (16mill records): Table Layout: DROP TABLE IF EXISTS `dmd`.`gas`;CREATE TABLE `dmd`.`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, PRIMARY KEY `POSTCODE` (`POSTCODE`), KEY `SURNAME` (`SURNAME`), KEY `ADDR1` (`ADDR1`), KEY `FORENAME` (`FORENAME`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK\_KEYS=1;Keyname Type Cardinality FieldFORENAME INDEX 42992 FORENAMESURNAME INDEX 142554 SURNAMEADDR1 INDEX 2708531 ADDRESS1POSTCODE INDEX 902843 POSTCODE A quick note on this, the URNS are used by the company that supply the data, they’re effectively useless as our client data doesn’t usually have a URN and were required to search on: - POSTCODE - First letter of FORENAME - Complete SURNAME - First 5 letters of address Due to the quality of the data were sent. Anyway, right now the query im using is: CREATE TEMPORARY TABLE duplicates SELECT companytable.POSTCODE FROM companytable INNER JOIN GAS ON GAS.POSTCODE=companytable.POSTCODE AND (LEFT(GAS.FORENAME, 1)=LEFT(companytable.FORENAME, 1)) AND (GAS.SURNAME=companytable.SURNAME) AND (LEFT(GAS.ADDR1, 5)=LEFT(companytable.ADDRESS1, 5)); UPDATE jewelry SET DUPE=1 WHERE POSTCODE IN (SELECT POSTCODE FROM duplicates);DROP TABLE duplicates;id select\_type table type possible\_keys key key\_len ref rows Extra1 SIMPLE companytable ALL NULL NULL NULL NULL 80000 1 SIMPLE GAS ref POSTCODE,SURNAME POSTCODE 10 table.companytable.POSTCODE 11 Using where Any help to optimize this query/table would be greatly appreciated, I’ve been going through the seminars and a million different sites for optimization. The query does run fairly fast considering the records but it can hang “sending…” while selecting the data for the temp table and obviously i don’t feel its quite up to par with the standards that it could be. On another note about the data, were sent an amended version of the “suppression” tables monthly, they’re the same layout but they’re usually slightly bigger (around 20,000 or so records bigger). Anyway, recent ideas I’ve had are: - Replace LEFT() functions with INDEX(column()) - Partitioning the table (moving fields not checked against to another table for use when absolutely required) - Possibly splitting the tables down into smaller tables - Obviously tweaking server variables to fit such large queries - Changing Hard-drive to RAID5 (for the reading speed, when updating the smaller table its usually no more than 1000 records changed) Again, thanks for any suggestions/help in advance! ---
**Author:** ![allworknoplay](https://avatars.discourse-cdn.com/v4/letter/a/dbc845/32.png) [@allworknoplay](https://forums.percona.com/u/allworknoplay)\ **Post date:** [September 19, 2007, 8:38am UTC](https://forums.percona.com/t/query-optimization-large-results/455/2 "2007-09-19T08:38:09Z")
Although I’m not a pro, I think it would be useful to have the specs of your hardware, operating system and your current configuration so we can see how your buffers/variables are configured… ---
**Author:** ![MonkeyTech](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/monkeytech/32/765_2.png) [@MonkeyTech](https://forums.percona.com/u/MonkeyTech)\ **Post date:** [September 19, 2007, 9:34am UTC](https://forums.percona.com/t/query-optimization-large-results/455/3 "2007-09-19T09:34:59Z")
At the moment i’ve got it sat on a test server so im not expecting optimal performance from the hardware but eventualy when we upgrade to enterprise we’l be moving it into our main server room onto a IBM System x3850 and runing the sql with perl remotely. Same applies to the variables, theyre default right now. Im really looking more toward optimization of the tables and queries, im sure theres alot i can do, id just like advice from someone with perhaps more knowlage that me and a little direction - as i said it runs fairly efficiently as is but im always looking for ways to improve my work. ---
**Author:** ![allworknoplay](https://avatars.discourse-cdn.com/v4/letter/a/dbc845/32.png) [@allworknoplay](https://forums.percona.com/u/allworknoplay)\ **Post date:** [September 19, 2007, 10:02am UTC](https://forums.percona.com/t/query-optimization-large-results/455/4 "2007-09-19T10:02:36Z")
Well your company table seems to have a TYPE of ALL. So I don’t think you want that, you’d want to have that as specific as possible. Even with that, it’s only looking at 80,000 records so that’s not that bad, but from what I remember, you don’t want a Type=ALL since that will scan everything. What does yout companytable look like? ---
**Author:** ![MonkeyTech](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/monkeytech/32/765_2.png) [@MonkeyTech](https://forums.percona.com/u/MonkeyTech)\ **Post date:** [September 19, 2007, 10:08am UTC](https://forums.percona.com/t/query-optimization-large-results/455/5 "2007-09-19T10:08:41Z")
Its just the table im checking again, the format can and will change per client but the table is currently: CREATE TABLE `company` ( `BigBook` varchar(10) NOT NULL, `BBOOK` varchar(6) NOT NULL, `TAB` varchar(10) NOT NULL, `URN` int(30) NOT NULL auto\_increment, `Title` varchar(5) NOT NULL, `forename` varchar(25) NOT NULL, `surname` varchar(25) NOT NULL, `ADDRESS1` varchar(40) NOT NULL, `ADDRESS2` varchar(40) NOT NULL, `ADDRESS3` varchar(40) NOT NULL, `ADDRESS4` varchar(40) NOT NULL, `ADDRESS5` varchar(40) NOT NULL, `ADDRESS6` varchar(40) NOT NULL, `POSTCODE` varchar(8) NOT NULL, `DOB` varchar(20) NOT NULL, PRIMARY KEY (`URN`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO\_INCREMENT=80002 ; bad practice to put the primary on a URN i know, i just quickly wrote the statement before i stumbled on this forum. One obvious problem i can see is using LEFT() is stopping mysql from using the indexes on ADDR1 and FORENAME, i’ve thought about restricting the size of the index on the column to the equivilent values that im using in LEFT() at the moment but i wonder if it will just search the whole word rather than the index.