[SOLVED] LIKE

[jaymc]

I have a zip code table with 1.7 million rows

 

If I search for a zip code I get a result in less than a second, the zip code is indexed so thats why

 

However, if I search for a zip code using LIKE '$123%' LIMIT 0,1 it is taking 40 seconds +

 

Here is the explain

 

Is there a reason why when using LIKE it takes a long time?

 

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"

"1","SIMPLE","postcodes","index","","PRIMARY","12","","1754783","Using where; Using index"

[corbin]

What is the primary key?  What's your table structure?  Can you show us your index information too?  It says it's using an index, but I thought indexes showed up in the key column of explain (hence me wondering what the primary key is).

[jaymc]

This should help

 

CREATE TABLE `postcodes` (
  `postcode` varchar(10) character set latin1 collate latin1_general_ci NOT NULL,
  `street` varchar(255) character set latin1 collate latin1_general_ci default NULL,
  `city` varchar(255) character set latin1 collate latin1_general_ci default NULL,
  `county` varchar(255) character set latin1 collate latin1_general_ci default NULL,
  `street2` varchar(255) character set latin1 collate latin1_general_ci default NULL,
  `town` varchar(50) NOT NULL,
  `x` varchar(50) NOT NULL,
  `y` varchar(50) NOT NULL,
  `longitude` varchar(50) NOT NULL,
  `latitude` varchar(50) NOT NULL,
  PRIMARY KEY  (`postcode`),
  KEY `postcode` (`postcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

This is fine LIKE '123%' LIMIT 0,1

This takes time LIKE '%123%' LIMIT 0,1

[corbin]

So errr....  What country are these postal codes in?  USA?  International?  Are they fully numeric?  If so, I would make the postcode column numeric.  What are x and y?  Seems like they could probably be numeric too, but no idea what they are, so....  Longitude/latitude?  Those could be numeric, but then I guess you would need other columns or something.

 

 

 

Why do you have a primary key and a normal key on postcode?  That's redundant.

 

As for your problem, MySQL doesn't use indexes with LIKE '%<anything here>'.

 

I don't remember the exact reason, but logic tells me it's this:

 

MySQL indexes things in trees (b-trees usually if I remember right), meaning that if you're looking for something that starts with ABC, it will go down the A branch, then the B branch, then the C branch.  The problem with wildcards at the start is that it doesn't know which tree to follow as it would have to follow all of them to follow subsequent things, and since % can mean any amount of characters, it would have to follow all branches and sub branches.  In other words, using indexes would essentially be the same as a table scan.

 

 

 

 

The way around this little problem?  I don't know to be honest.  Maybe someone will have run into this problem before.

[fenway]

It's using the index... there are just a lot of matching rows.

[corbin]

It's using the index... there are just a lot of matching rows.

 

 

I remembered wrong?  Maybe it's MSSQL that doesn't use indexes on like '%

[fenway]

Well, as long as the wildcard is at the end, you're ok -- but why the dollar sign?

[jaymc]

the dollar sign was a typo, it was meant to be %

 

So if its using the index.. why so long with a wild card?

[fenway]

If you start with a %, you're doing a substring search -- which means you can't use an index, EVER.

[jaymc]

How can you optimize this type of query?

 

There are a few cases where I need to perform thie type of 'loose' search

[fenway]

You can't, really.... there's no way to find these values without inspecting each row.