Index taking forever - MySQL problem?

mhalloran

I have a table that is just over 6 million rows. It holds general contact information. One of the queries that I need to perform is to have an operator retrieve a record entering a phone number.

After indexing the phone number field, retrieving a record using a phone number that exists in a table takes a 10th of a second. If I search for a number that doesn't exist in the table, however, the query takes just over 10 seconds every time.

I've only worked with tables this large on SQL Server and I'm wondering if this type of thing just isn't MySQL's cup of tea. There are other strange things that keep popping up as well due to the table size.

Despite the large number of rows, the layout and queries are extremely simple and there are only 3 tables.

Is PostgreSQL better at handling large amounts of data?
Is there a better way that I should index the fields?

Also, I notice that when you do a search on these boards, it seems to first search if your search phrase exists at all before doing a full search. Maybe this is something that I should be doing but I'm not sure how it is achieved

Thanks for the help.

laserlight

It makes sense that searching for a record that does not exist would normally take longer than searching for a record that exists.

Why dont you try with postgresql and see the difference?

tsinka

Hi,

how does the structure of your tables look like (column types, indexes ...) ?
You can use
EXPLAIN query
to get information about how MySQL optimizes you queries and which indexes it uses.
How do the SQL queries look like ?

Thomas

ultraslacker

~10 seconds is what I would expect for a sequential scan.

mhalloran

Thanks for the responses so far.

The structure:

solicitation_number int(10)
phone_number varchar(16)
first_name varchar(30),
last_name varchar(50),
address varchar(120),
city varchar(50),
state char(2),
zip varchar(5),
zip_4 varchar(4)

The solicitation number is the primary key and the phone number is indexed.

the query:
SELECT * FROM table_name WHERE phone_number='1234567890'

If I search by the solicitation number it takes .02 match or no match.
the phone number takes .02 match and 10.5 no match. There is no way that an indexed field should take that long.

One thing that I did notice is that the phone number is a varchar. I did this because the text file in which the table gets the data sometimes has dashes or parentheses to separate the numbers.

If switching it to an integer will make that much of a difference, than I can alter the export process so that it strips out everything but the integers.

Thanks for the help so far.

EDIT: I'm still curious on how this board operates as far as the searches. If no match is returned, the results are displayed instantly. If there is a match, however it seems to take you to another query where it actually fetches the results.

Also, I'll post my findings using the EXPLAIN statement.

Roger_Ramjet

Read this http://dev.mysql.com/doc/mysql/en/Tips.html for some interesting insights into how MySQL works and how to speed things up.

litebearer

perhaps using int for the phone numbers and doing a binary search may speed things abit

laserlight

Originally posted by litebearer
perhaps using int for the phone numbers and doing a binary search may speed things abit

It is a DBMS, so one cant just do binary search.