[SOLVED] Big table, bad query

[jaymc]

I have a table with 1.7million rows in it

 

I am running this query which is taking 100+ seconds

 

SELECT * FROM postcodes WHERE replace(postcode,' ','') = 'l12 8uj' OR postcode = 'l12 8uj' LIMIT 0,1

 

postcode has an index, I think the issue is the REPLACE, perhaps it has to analyze each row to do the REPLACE where as it would normally skip through it

 

Any ideas on optimizing the table/query to help with this request

 

Thanks

[friedemann_bach]

Submitting your table structure (fields, data types and indexes) would help.

 

[jaymc]

	Field  	Type  	Collation  	Attributes  	Null  	Default  	Extra  	Action
postcode 	varchar(10) 	latin1_general_ci 		No 			Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
street 	varchar(255) 	latin1_general_ci 		Yes 	NULL 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
city 	varchar(255) 	latin1_general_ci 		Yes 	NULL 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
county 	varchar(255) 	latin1_general_ci 		Yes 	NULL 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
street2 	varchar(255) 	latin1_general_ci 		Yes 	NULL 		Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
town 	varchar(50) 	latin1_swedish_ci 		No 			Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
x 	varchar(50) 	latin1_swedish_ci 		No 			Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
y 	varchar(50) 	latin1_swedish_ci 		No 			Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
longitude 	varchar(50) 	latin1_swedish_ci 		No 			Browse distinct values 	Change 	Drop 	Primary 	Unique 	Index 	Fulltext
latitude 	varchar(50) 	latin1_swedish_ci 		No 	

[jaymc]

Indexes: 
Documentation    Keyname  	Type  	Cardinality  	Action  	Field
postcode 	INDEX 	1794204  	Edit 	Drop 	postcode 

[friedemann_bach]

Questions:

- Which field is your primary key? Do you have a PK at all? If not, add a field like 'id', make it an integer(9), set it to auto_increment and make it your PRIMARY KEY.

- Your query (replace(postcode,' ','') = 'l12 8uj') : what sense does it make to replace ' ' with '' and then search for a string that contains ' ' ? This won't return any result at all. Please explain your idea.

 

General advice:

- Disable the NULL values for the fields 'street', 'city', 'county' and 'street2'. Probably you don't require to distinct between NULL and an empty string.

- Fields containing arithmetic information (x,y,longitude,latitude) do not need to be VARCHAR, make them INTEGER if possible or convert the values to integers if possible.

 

[jaymc]

Questions:

- Which field is your primary key? Do you have a PK at all? If not, add a field like 'id', make it an integer(9), set it to auto_increment and make it your PRIMARY KEY.

I DONT HAVE A PRIMARY KEY, WHY DO I NEED ONE? AN INDEX ON POSTCODE IS ENOUGH SURELY? OR DOES A PRIMARY KEY INDEX BETTER

 

- Your query (replace(postcode,' ','') = 'l12 8uj') : what sense does it make to replace ' ' with '' and then search for a string that contains ' ' ? This won't return any result at all. Please explain your idea.

BECAUSE PEOPLE ENTER A POSTCODE LIKE "l12 8uj" OR "l128uj". THE QUERY WILL MATCH EITHER

[unkwntech]

My suggestion here ( not an SQL genius) I would just format the data how YOU want it before you insert it.

[jaymc]

Yeh, I think I'll have to as it looks like the REPLACE screws the effeciency INDEX up

[friedemann_bach]

BECAUSE PEOPLE ENTER A POSTCODE LIKE "l12 8uj" OR "l128uj". THE QUERY WILL MATCH EITHER

 

Nope. Quite wrong.

 

replace(postcode,' ','') = 'l12 8uj' does never match anything: the postcode 'l12 8uj' contains ' ' which would be replaced by replace(postcode,' ',''). 'l128uj' will probably never match 'l12 8uj'.

 

I think it should read replace(postcode,' ','') = 'l128uj', without ' '. Then it might work as you thought.

 

As proposed by unkwntech, try to optimize your data entries but also try to optimize the database structure as I proposed above. I did a test on a large database (~200.000 rows) and even queries containing a replace statement took less than 1 sec.