How to manage ~ 2,000.000.00 records?

Anon

Hi guys,

I just need a suggestion - I have one table, which has about 2 million records in it, when I do a search against it, even when trying to be very specific, it takes a very long time to pull data out of it (I'd say 1-3 minutes).

The table has a column with years in it (e.g. 2001, 2000, 1999) there are over 300.000.00 records for each year. Also, it has a column with "types" in it - for example "Male, Female".

Here is what I'm thinking of doing, but need your suggestions...

if I divide the table by year, into several tables (1 year and data with this year for 1 table)
then create another table with separate types in it - 1 for male, 1 for female
And index it all

Then use joins to join "type" with "year" will this speed up the search??

Thanks!

Anon

Two things:

1) What database are you running? If you are dealing with multi-million record tables, then hopefully it is one designed to handle large tables. For example: Oracle, M$ SQL Server, DB2, and so on.

2) If you haven't properly indexed your table(s), then performance will suck no matter what database you use. When performance tuning, at least 80% of the improvement usually comes from tuning the application -- which includes properly indexing your tables.

Give us information about your table structure(s), existing indexes, and SQL you want tuned, and someone should be able to recommend the best indexes to build.

-- Michael
Darkstreak Consulting
www.darkstreak.com

toma42

If you're using Oracle, you can have it partition the table based on a column, such as your year...that way searches are quickly limited to just the partition with the year you want.

This is pretty complex....if you don't have indexes, however, you're probably not using Oracle.

Anon

I'm using MySQL.
The database file is 300 MB.
Pretty small records, 1 text line in 1 column (about 7 columns total)

I need to be able to search it by these columns:

type
name
organization
state
year

In other words these are the most useable.
How should I index it more effective?

Anon

Query I'm using:

select * from table_name where type=\"some-type\" and name like \"%_some_name\" and organization like \"%some_org\" and state = \"some_state\" and year = \"some_year\";

As you can see - I know type, state and year for sure (they are 100% in the table), but name and organization are might not be in the table...

Anon

Hopefully someone can help out more comprehensively in terms of building proper indexes, but one thing I noticed right off the bat is that you have \"where something like \'%something\'\" which is very very slow. Not sure if it is possible for you to do, but if you can turn that into \\'where something like \'something%\' it will run MUCH faster under mysql. Having the % at the beginning is death in Mysql.

kparker

LIKE %... is (relative) death to any sql dbms because you're asking it to search that column for every single row looking for matches; an index will do no good if the target string is not anchored to the beginning of the column.

kparker

Do not split the table into multiple tables by year. This is all the same type of data, and you will complicate everything else you do with it. Instead, consider a basic normalization. Instead of:

type
name
organization
state
year

use this organization:

person:
   id
   name

organization:
   id
   name

entry (not sure what to call this table):
   type
   person_id
   org_id
   state
   year

Now your wildcard queries will be much, much faster (assuming that the there are far fewer than 2million organizations, of course!)

select type, person.name, organization.name, state, year
from entry, person, organization
where
entry.person_id = person.id
and entry.organization_id = organization.id
and entry.type="some-type"
and person.name like "%_some_name"
and organization.name like "%some_org"
and entry.state = "some_state"
and entry.year = "some_year";

You will need indexes on most of the columns here if you intend to support a wide range of queries; this will of course have a negative impact on speed of INSERT and UPDATE queries.

kparker

Before we scold him to much for use of LIKE %, let's hammer on mysql a little more. Look at all the non-wildcard expressions in the WHILE:

  select * from table_name where
    type="some-type" and
    state = "some_state" and
    year = "some_year" and
    name like "%_some_name" and
    organization like "%some_org"

Surely not even MySql has a query optimizer^{H^{H^{H^{H^{H^{H^H}}}}}} pessemizer so lame that, given indexes on type, state, and year, together with exact-equivalence expressions on these columns, it would still pick the option of doing a full sequential scan for the LIKE expressions first, would it?

toma42

FYI, I wasn't suggesting breaking the table up. I was recommending a oracle-only(?) way to partition the table to make searches faster....it still appears as one table to the user.

Anon

Assuming that MySQL's optimizer is having trouble with this query, you might try the following to see if it helps:

Index the table with the most unique fields first. For example if you have 30 states, 4 years and 2 sexes, index it in State, Year, Type order so that it will be able to narrow down to the smallest subset of data as quickly as possible.

You might want to try indexing all 5 fields in your Where clause to see if it helps any.

Order the items in your Where clause to exactly match the order of your index to try and coerce the optimizer to use the index.

Another option that might work depends on how much data gets returned. Select your data based on State, Year and Type (this will return more data than you want). Then use client-side logic to only display records from the cursor that have Organization and Name like the two other search variables. This option stinks if the Like clauses are filtering out a large number of records. You'll get your results quicker with this option but the cursor will be prohibitively large.

kparker

Has anybody mentioned EXPLAIN yet? This can help you see how MySQL is going about the row-selection process.