tricky MySql problem

MrRosary

I have a very large (2 million record) MySQL database of addresses.

I want to search on the first 2 characters of the postcode / zipcode.

Obviously this field is part of the index on the table but which should be the fastest way of searching

... where left ( postcode , 2 ) = '$search_string'

... where postcode like '$search_string'%

or is there a way of only indexing the first 2 characters of the post code field ignoring the rest?

MrRosary

Pig

not to my knowledge, but you could create another column for those 2 items, index it, and make a script to populate it.

MCANDREY

I "feel" that this is the quickest version :
... where postcode like '$search_string'% because the first one
extract the first 2 characters and, after, compare the strings.

Let me know your test results.

MrRosary

Well I did some testing.

I chose 5 random postcodes and ran them through a select statment run from the command line.

The 'like' statement was faster than the 'left' statement by over 2 orders of magnitude.

On average the 'left' statement took 10.8 seconds on my machine (which is none too speedy for this sort of thing). The 'like' statement took an average of 0.46 seconds for the same data.

Its definately something to bear in mind when working with large ammounts of data. Even for smaller databases its worth remebering.

There is also a size attribute for an index.... not sure how this works yet.

MrRosary

MCANDREY

Thx a lot for results.

Sxooter

That's called a partial or functional index. While Postgresql supports such, I'm not sure MySQL does. partial indexes rock. They make for very fast lookup when there's only a few things that meet the where clause part of the index.

csn

Can you give some examples of when you'd use partial indexes?

With MySQL, I think you can create indexes on x number of chars in a column. I think it applies to char types only, and I'm pretty sure MySQL doesn't have partial indexes.

MrRosary

from the MySQL documentation

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax. (On BLOB and TEXT columns the length is required.) The statement shown here creates an index using the first 10 characters of the name column:

mysql> CREATE INDEX part_of_name ON customer (name(10));

Because most names usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column. Also, using partial columns for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations!

For my particular application inserts are not really an issue. But for the type of search I am performing I think that partial indexes should be a very good idea indeed.

MrRosary

Sxooter

Originally posted by csn
Can you give some examples of when you'd use partial indexes?

With MySQL, I think you can create indexes on x number of chars in a column. I think it applies to char types only, and I'm pretty sure MySQL doesn't have partial indexes.

Sure, a common example is in a system where 99% of all the content is approved, and 1% isn't. I.e. there's a boolean flag called approved, and with 150,000 online documents, maybe 100 or so are unapproved. In normal operation you'd put a bool in a small table and join it to whatever you need. If the average width of records in the table was say 10 or 20 bytes, then it'd be just about as fast to brute force it as it would be to read in an index on all those elements if it's not a partial.

create table test (article text, <bunches of other fields>, id serial);
create table test2 (status bool, tid int8 refernces test (id));
create index test2_status on test2 (status);

This would be a waste, as it would be not much faster than just scanning the small test2 table. but, this one would make a much smaller index:

create index test2_status on test2 (status) where status is false;

Since it will only have pointers to the false fields, it will be a fraction of the size of the index on every row in the column.

It's also useful if you have timely versus non-timely data. I.e. data that is in the archive state is a. I.e. if 90% of all you access are on data that is less than a week old, then you can create an index every night that only has the latest weeks or two worth of articles. so you could store 15,000,000 articles, but only index some tiny percentage that get hit the most. Heck you could even analyze frequencies of where clauses in the logs and see which things need partial indexing.