Ad:

MySQL Discussion Thread
Unsolved
Views: 10572

Jul 19th, 2007

Query on a large table too slow

Expand Post »

Hi there,

My knowledge on query optimization ends here. I am stuck!

My table with following structure has more than 1 million records.
CREATE TABLE `dreambank`.`db_ip_data_buffer` (
`ip_from` bigint(20) unsigned NOT NULL default '0',
`ip_to` bigint(20) unsigned NOT NULL default '0',
`country_code` varchar(2) default NULL,
`country_name` varchar(64) default NULL,
`region_name` varchar(128) default NULL,
`city_name` varchar(128) default NULL,
`latitude` double default NULL,
`longitude` double default NULL,
`is_cash` tinyint(3) unsigned default NULL,
PRIMARY KEY (`ip_to`,`ip_from`),
KEY `Index_2` (`ip_from`),
KEY `Index_3` (`ip_to`),
KEY `index` (`is_cash`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1;

The query I am running is:
SELECT ip_from FROM db_ip_data_buffer WHERE ip_from <= 1089969676 and ip_to >= 1089969676;

Result:
1 row fetched in 0.0211s (0.814 s)

On a site with 45,000 users this would put quite a bit of stress on mysql and I think it's slow for an index page. Is there a way to index this for better performance?

I really appreciate any help I can get!

Thanks,
Tim

Similar Threads

Slow directory listing in Networking
MySQL: index for a very large table in MySQL
PHP/SQL query help in PHP

timhysniu

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

46 posts
since Jun 2004

Permalink

Jul 24th, 2007

Re: Query on a large table too slow

If you have your indexes set and it's still too slow, you may have to look at the way you're extracting data. Perhaps there is a way to trim your resultset and still get what you want.

In your case I'd try setting an index to ip_from and ip_to combined

Last edited by pritaeas; Jul 24th, 2007 at 6:18 am.

pritaeas

Sponsor

Featured Poster

Reputation Points: 550

Solved Threads: 728

Bite my shiny metal ass!

Offline

4,167 posts
since Jul 2006

Permalink

Jul 26th, 2007

Re: Query on a large table too slow

Click to Expand / Collapse Quote originally posted by pritaeas ...

If you have your indexes set and it's still too slow, you may have to look at the way you're extracting data. Perhaps there is a way to trim your resultset and still get what you want.

In your case I'd try setting an index to ip_from and ip_to combined

Hi there,
thanks for the response. Setting an index combined didn't exactly help much. Can you please give an example on trimming the resultset?

Are you maybe suggesting that I normalize the table to remove excess fields/long varchars from search table? I tried this and this only reduced the wait time from 0.814 s to 0.550 s which is good, but not exactly efficient considering the possible load on server.

Another strategy I used was adding a is_cashed BOOLEAN field. Query against set is_cashed seems to be quick, however, if the record is not in cashed data set then I must use the same old slow query again.

Any ideas?

Basically all I'm trying to do is identify the location of visitors, but having a homepage delay this much is not a good starting point.

Thanks very much,
Tim

timhysniu

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

46 posts
since Jun 2004

Permalink

Jul 27th, 2007

Re: Query on a large table too slow

Is there anything you can change to the <= and >= operators. Can you specify a range for the ip_from and the ip_to ? It seems the table has to access a lot of records it doesn't need.

Can't you do:

SELECT ip_from FROM db_ip_data_buffer WHERE ip_from >= xxx AND ip_from <= 1089969676 AND ip_to >= 1089969676 AND ip_to <= yyy;

Second option could perhaps be a stored procedure to return the resultset to you.

pritaeas

Sponsor

Featured Poster

Reputation Points: 550

Solved Threads: 728

Bite my shiny metal ass!

Offline

4,167 posts
since Jul 2006

Permalink

Jul 27th, 2007

Re: Query on a large table too slow

Why is it not ip_from and ip_to = ?

I have not dealt with using a composite primary key and then adding in >= or <=. I have always set them equal.

While it may not be the use scenerio you are wanting, does that speed up the query time?

Stylish

Reputation Points: 44

Solved Threads: 19

Junior Poster

Offline

148 posts
since May 2007

Permalink

Aug 2nd, 2007

Re: Query on a large table too slow

Stylish, your solution works very well. I was able to increase response about 10 times.

Thanks very much!

Tim

timhysniu

Reputation Points: 10

Solved Threads: 1

Light Poster

Offline

46 posts
since Jun 2004

Permalink

Aug 3rd, 2007

Re: Query on a large table too slow

Glad to hear.

Good luck with your project.

Stylish

Reputation Points: 44

Solved Threads: 19

Junior Poster

Offline

148 posts
since May 2007

Permalink

Aug 8th, 2011

Re: Query on a large table too slow

Sorry for the bump, but I feel it is necessary. This thread came high up in Google for a relevant phrase, but just to help anyone else who comes across this, consider using BETWEEN, which is more efficient than >= and <=.

gypsywitchcraft

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Aug 2011

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: query to retrieve data from 3 tables

Next Thread in MySQL Forum Timeline: mysql time calculations

DaniWeb Message

Cancel Changes

User Name
Password