After moving database queries are very slow

Hi

I have development server at home which i use to develop my PHP - MySQL
code. Recently I finished the database and some pages and I wanted to
move it to dedicated server at my provider. I exported whole database
using MySQL-Front and imported in on the server without problems using
mysql -u root -p < database.sql.

Everything seems to be OK but queries that take 2seconds on my machine
takes forever on my dedicated server.

Does anyone have an idea what could be the problem?
I'm using 5.0.24a-log at home and 5.0.27-log at dedicated server.

Thank you
Ralph
--

Ralph

Ralph wrote:

> Hi
>
> I have development server at home which i use to develop my PHP - MySQL
> code. Recently I finished the database and some pages and I wanted to
> move it to dedicated server at my provider. I exported whole database
> using MySQL-Front and imported in on the server without problems using
> mysql -u root -p < database.sql.
>
> Everything seems to be OK but queries that take 2seconds on my machine
> takes forever on my dedicated server.
>
> Does anyone have an idea what could be the problem?
> I'm using 5.0.24a-log at home and 5.0.27-log at dedicated server.
>
> Thank you
> Ralph

Define "forever".

Are you sure you are the only one on this "dedicated" server? Make sure the
indexes are in place. Also - since it appears that you have access "on the box",
can you get explain plans from both your dev and the prod servers?

--
Michael Austin.
Database Consultant

Michael Austin wrote:
> Ralph wrote:
>
>> Hi
>>
>> I have development server at home which i use to develop my PHP -
>> MySQL code. Recently I finished the database and some pages and I
>> wanted to move it to dedicated server at my provider. I exported whole
>> database using MySQL-Front and imported in on the server without
>> problems using mysql -u root -p < database.sql.
>>
>> Everything seems to be OK but queries that take 2seconds on my machine
>> takes forever on my dedicated server.
>>
>> Does anyone have an idea what could be the problem?
>> I'm using 5.0.24a-log at home and 5.0.27-log at dedicated server.
>>
>> Thank you
>> Ralph
>
> Define "forever".
>
> Are you sure you are the only one on this "dedicated" server? Make sure
> the indexes are in place. Also - since it appears that you have access
> "on the box", can you get explain plans from both your dev and the prod
> servers?
>

How can i check if my indexes are in place?

The explain gives me the same thing on both servers:

EXPLAIN SELECT rfc.radioid, rfc.fac_callsign, rfc.fac_frequency,
rfc.fac_service, rfc.comm_city, rfc.rank FROM radio_stations_tbl AS rfc,
zipcodes_tbl AS zipt WHERE zipt.zip=92627 AND
earth_distance_miles(zipt.latitude,zipt.longitude, rfc.lat,rfc.lon)<50
ORDER BY rank DESC;
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
| 1 | SIMPLE | rfc | ALL | NULL | NULL | NULL |
NULL | 14672 | Using temporary; Using filesort |
| 1 | SIMPLE | zipt | ALL | PRIMARY | NULL | NULL |
NULL | 43187 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
2 rows in set (0.00 sec)

But on the development one (the one I have at home) the query takes
about 3seconds. On dedicated one it takes forever.

Please help.
Ralph

--

Ralph

Ralph <onkr@remove.op.pl> wrote:
>
> The explain gives me the same thing on both servers:
>
> EXPLAIN SELECT rfc.radioid, rfc.fac_callsign, rfc.fac_frequency,
> rfc.fac_service, rfc.comm_city, rfc.rank FROM radio_stations_tbl AS rfc,
> zipcodes_tbl AS zipt WHERE zipt.zip=92627 AND
> earth_distance_miles(zipt.latitude,zipt.longitude, rfc.lat,rfc.lon)<50
> ORDER BY rank DESC;
> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>| id | select_type | table | type | possible_keys | key | key_len | ref
> | rows | Extra |
> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>| 1 | SIMPLE | rfc | ALL | NULL | NULL | NULL |
> NULL | 14672 | Using temporary; Using filesort |
>| 1 | SIMPLE | zipt | ALL | PRIMARY | NULL | NULL |
> NULL | 43187 | Using where |
> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+

> But on the development one (the one I have at home) the query takes
> about 3seconds. On dedicated one it takes forever.

Again: define "forever". No query takes forever. It may take long,
even very long, but not forever. Depends on how much work is to be
done and how much ressorces you have. Seems your dedicated server is
somewhat slower or has a ressource limit (quite usual for shared
servers).

Also this is a *very* bad Query. No indexes used at all. MySQL has to
evaluate the earth_distance_miles() function for all 14.672 x 42.183
possible tuples. You should define an index on zipcodes_tbl.zip.

I guess the earth_distance_miles() gives the distance between two
coordinate points. So you're looking for all points within 50 miles
distance. This would be much faster if you drop bad candidates without
evaluating the distance function. I.e.

SELECT ...
FROM zipcodes_tbl AS zipt
JOIN radio_stations_tbl AS rfc
WHERE zipt.zip=92627
AND rfc.lat BETWEEN zipt.lat-xxx AND zipt.lat+xxx
AND rfc.lon BETWEEN zipt.lon-xxx AND zipt.lon+xxx
AND earth_distance_miles(...) < 50

where xxx is some suitable value to define a square 100x100 miles area
arround a given zip. You can even use an (expensive) function for that,
because it won't be called very often.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Ralph wrote:

> Michael Austin wrote:
>
>> Ralph wrote:
>>
>>> Hi
>>>
>>> I have development server at home which i use to develop my PHP -
>>> MySQL code. Recently I finished the database and some pages and I
>>> wanted to move it to dedicated server at my provider. I exported
>>> whole database using MySQL-Front and imported in on the server
>>> without problems using mysql -u root -p < database.sql.
>>>
>>> Everything seems to be OK but queries that take 2seconds on my
>>> machine takes forever on my dedicated server.
>>>
>>> Does anyone have an idea what could be the problem?
>>> I'm using 5.0.24a-log at home and 5.0.27-log at dedicated server.
>>>
>>> Thank you
>>> Ralph
>>
>>
>> Define "forever".
>>
>> Are you sure you are the only one on this "dedicated" server? Make
>> sure the indexes are in place. Also - since it appears that you have
>> access "on the box", can you get explain plans from both your dev and
>> the prod servers?
>>
>
> How can i check if my indexes are in place?
>
> The explain gives me the same thing on both servers:
>
> EXPLAIN SELECT rfc.radioid, rfc.fac_callsign, rfc.fac_frequency,
> rfc.fac_service, rfc.comm_city, rfc.rank FROM radio_stations_tbl AS rfc,
> zipcodes_tbl AS zipt WHERE zipt.zip=92627 AND
> earth_distance_miles(zipt.latitude,zipt.longitude, rfc.lat,rfc.lon)<50
> ORDER BY rank DESC;
> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>
> | id | select_type | table | type | possible_keys | key | key_len | ref
> | rows | Extra |
> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>
> | 1 | SIMPLE | rfc | ALL | NULL | NULL | NULL |
> NULL | 14672 | Using temporary; Using filesort |
> | 1 | SIMPLE | zipt | ALL | PRIMARY | NULL | NULL |
> NULL | 43187 | Using where |
> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>
> 2 rows in set (0.00 sec)
>
> But on the development one (the one I have at home) the query takes
> about 3seconds. On dedicated one it takes forever.
>
> Please help.
> Ralph
>

Based on the explain - there is no index.

see this example:
mysql> show index from a;
Empty set (0.53 sec)

mysql> describe a;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (1.45 sec)

mysql> create index a_idx on a (a);
Query OK, 2 rows affected (2.48 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from a;
+--------------+
| a |
+--------------+
| 1 193 456,87 |
| 193 456,78 |
+--------------+
2 rows in set (0.07 sec)

mysql> explain select * from a where a > '1 ';
+----+-------------+-------+-------+---------------+-------+---------+------+---
---+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | ro
ws | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+---
---+--------------------------+
| 1 | SIMPLE | a | index | a_idx | a_idx | 23 | NULL |
2 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+---
---+--------------------------+
1 row in set (0.11 sec)



--
Michael Austin.
Database Consultant

Michael Austin wrote:
> Ralph wrote:
>
>> Michael Austin wrote:
>>
>>> Ralph wrote:
>>>
>>>> Hi
>>>>
>>>> I have development server at home which i use to develop my PHP -
>>>> MySQL code. Recently I finished the database and some pages and I
>>>> wanted to move it to dedicated server at my provider. I exported
>>>> whole database using MySQL-Front and imported in on the server
>>>> without problems using mysql -u root -p < database.sql.
>>>>
>>>> Everything seems to be OK but queries that take 2seconds on my
>>>> machine takes forever on my dedicated server.
>>>>
>>>> Does anyone have an idea what could be the problem?
>>>> I'm using 5.0.24a-log at home and 5.0.27-log at dedicated server.
>>>>
>>>> Thank you
>>>> Ralph
>>>
>>>
>>> Define "forever".
>>>
>>> Are you sure you are the only one on this "dedicated" server? Make
>>> sure the indexes are in place. Also - since it appears that you have
>>> access "on the box", can you get explain plans from both your dev and
>>> the prod servers?
>>>
>>
>> How can i check if my indexes are in place?
>>
>> The explain gives me the same thing on both servers:
>>
>> EXPLAIN SELECT rfc.radioid, rfc.fac_callsign, rfc.fac_frequency,
>> rfc.fac_service, rfc.comm_city, rfc.rank FROM radio_stations_tbl AS
>> rfc, zipcodes_tbl AS zipt WHERE zipt.zip=92627 AND
>> earth_distance_miles(zipt.latitude,zipt.longitude, rfc.lat,rfc.lon)<50
>> ORDER BY rank DESC;
>> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>>
>> | id | select_type | table | type | possible_keys | key | key_len |
>> ref | rows | Extra |
>> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>>
>> | 1 | SIMPLE | rfc | ALL | NULL | NULL | NULL |
>> NULL | 14672 | Using temporary; Using filesort |
>> | 1 | SIMPLE | zipt | ALL | PRIMARY | NULL | NULL |
>> NULL | 43187 | Using where |
>> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>>
>> 2 rows in set (0.00 sec)
>>
>> But on the development one (the one I have at home) the query takes
>> about 3seconds. On dedicated one it takes forever.
>>
>> Please help.
>> Ralph
>>
>
> Based on the explain - there is no index.
>
> see this example:
> mysql> show index from a;
> Empty set (0.53 sec)
>
> mysql> describe a;
> +-------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+-------------+------+-----+---------+-------+
> | a | varchar(20) | YES | | NULL | |
> +-------+-------------+------+-----+---------+-------+
> 1 row in set (1.45 sec)
>
> mysql> create index a_idx on a (a);
> Query OK, 2 rows affected (2.48 sec)
> Records: 2 Duplicates: 0 Warnings: 0
>
> mysql> select * from a;
> +--------------+
> | a |
> +--------------+
> | 1 193 456,87 |
> | 193 456,78 |
> +--------------+
> 2 rows in set (0.07 sec)
>
> mysql> explain select * from a where a > '1 ';
> +----+-------------+-------+-------+---------------+-------+---------+------+---
>
> ---+--------------------------+
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | ro
> ws | Extra |
> +----+-------------+-------+-------+---------------+-------+---------+------+---
>
> ---+--------------------------+
> | 1 | SIMPLE | a | index | a_idx | a_idx | 23 |
> NULL |
> 2 | Using where; Using index |
> +----+-------------+-------+-------+---------------+-------+---------+------+---
>
> ---+--------------------------+
> 1 row in set (0.11 sec)
>
>
>
Hi

First of all thank you very much for response!

I dropped all indexes on both tables then i recreate them now tables
look like this:

mysql> describe radio_stations_tbl;
+---------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| radioid | int(11) | NO | PRI | 0 | |
| comm_city | char(30) | YES | | NULL | |
| comm_state | char(2) | YES | | NULL | |
| fac_address1 | char(50) | YES | | NULL | |
| fac_address2 | char(50) | YES | | NULL | |
| fac_callsign | char(8) | YES | UNI | NULL | |
| fac_city | char(30) | YES | | NULL | |
| fac_country | char(2) | YES | | NULL | |
| fac_frequency | float | YES | MUL | NULL | |
| fac_service | char(2) | YES | | NULL | |
| fac_state | char(2) | YES | | NULL | |
| fac_zip1 | char(5) | YES | | NULL | |
| station_type | char(1) | YES | | NULL | |
| power | float | YES | | NULL | |
| lat | float | YES | MUL | NULL | |
| lon | float | YES | MUL | NULL | |
| lat_lon_zip | varchar(5) | YES | | NULL | |
| url | char(100) | YES | | NULL | |
| audio_feed | char(100) | YES | | NULL | |
| votes | smallint(6) | YES | | NULL | |
| rank | float | YES | | NULL | |
| format | char(15) | YES | | NULL | |
+---------------+-------------+------+-----+---------+-------+

mysql> describe zipcodes_tbl;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| zip | varchar(16) | NO | PRI | 0 | |
| city | varchar(30) | NO | | | |
| county | varchar(30) | YES | | NULL | |
| state | varchar(30) | NO | | | |
| latitude | float | NO | MUL | 0 | |
| longitude | float | NO | MUL | 0 | |
| timezone | tinyint(2) | NO | | 0 | |
| dst | tinyint(1) | NO | | 0 | |
| country | char(2) | NO | | | |
+-----------+-------------+------+-----+---------+-------+

The explain on query gives me:

mysql> explain SELECT rfc.radioid, rfc.fac_callsign, rfc.fac_frequency,
rfc.fac_service, rfc.comm_city, rfc.rank FROM radio_stations_tbl AS rfc,
zipcodes_tbl AS zipt WHERE zipt.zip=92627 AND
earth_distance_miles(zipt.latitude,zipt.longitude, rfc.lat,rfc.lon)<50
ORDER BY rank DESC;
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
| 1 | SIMPLE | rfc | ALL | NULL | NULL | NULL |
NULL | 14672 | Using temporary; Using filesort |
| 1 | SIMPLE | zipt | ALL | PRIMARY | NULL | NULL |
NULL | 43187 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+

I don't know what else i can do :( to make it work faster. It takes 3s
on my home machine with 2.5GHz Celeron and 768Mb RAM. The dedicated
server is: Celeron 1.7Ghz and 512MB RAM

Please Help;
Ralph

--

Ralph

Axel Schwenke wrote:
[...]
> Again: define "forever". No query takes forever. It may take long,
> even very long, but not forever. Depends on how much work is to be
> done and how much ressorces you have. Seems your dedicated server is
> somewhat slower or has a ressource limit (quite usual for shared
> servers).

It takes 3s on my home machine with 2.5GHz Celeron and 768Mb RAM. The
dedicated server is: Celeron 1.7Ghz and 512MB RAM and it takes about
10minutes.

> Also this is a *very* bad Query. No indexes used at all. MySQL has to
> evaluate the earth_distance_miles() function for all 14.672 x 42.183
> possible tuples. You should define an index on zipcodes_tbl.zip.
>
> I guess the earth_distance_miles() gives the distance between two
> coordinate points. So you're looking for all points within 50 miles
> distance. This would be much faster if you drop bad candidates without
> evaluating the distance function. I.e.
>
> SELECT ...
> FROM zipcodes_tbl AS zipt
> JOIN radio_stations_tbl AS rfc
> WHERE zipt.zip=92627
> AND rfc.lat BETWEEN zipt.lat-xxx AND zipt.lat+xxx
> AND rfc.lon BETWEEN zipt.lon-xxx AND zipt.lon+xxx
> AND earth_distance_miles(...) < 50
>
> where xxx is some suitable value to define a square 100x100 miles area
> arround a given zip. You can even use an (expensive) function for that,
> because it won't be called very often.

Thank you very much for suggestions. Right now I'm trying to understand
why the query is taking so long. Maybe my mysql setap on dedicated
server is somehow corrupted. I even moved my my.cnf file to dedicated
server and restarted it. Did not help...

Is there anything else i could do to find the cause of this?

Ralph

Ralph <onkr@remove.op.pl> wrote:
> Axel Schwenke wrote:

>> Seems your dedicated server is
>> somewhat slower or has a ressource limit (quite usual for shared
>> servers).
>
> It takes 3s on my home machine with 2.5GHz Celeron and 768Mb RAM. The
> dedicated server is: Celeron 1.7Ghz and 512MB RAM and it takes about
> 10minutes.

This type of query puts load on:
- the cpu for calculating the distance function
- the disk for reading the dependent table (zipcodes_tbl)
over and over again.

Clearly your dedicated server has a slower cpu and less memory
(memory lessens I/O stress). But this does not explain a factor
of 200 in performance. Probably the disk in the server is very slow?


But again: this query is very bad. With proper indexing it should
complete in fractions of a second. Databases are built to manage
much more data than you have. OTOH this requires indexes!

>> SELECT ...
>> FROM zipcodes_tbl AS zipt
>> JOIN radio_stations_tbl AS rfc
>> WHERE zipt.zip=92627
>> AND rfc.lat BETWEEN zipt.lat-xxx AND zipt.lat+xxx
>> AND rfc.lon BETWEEN zipt.lon-xxx AND zipt.lon+xxx
>> AND earth_distance_miles(...) < 50

An index on zipt.zip would cut just a few rows out of the zipt table.
How much rows with the same zipcode are there in zipt? Lets assume
there are 5 such rows on average. Then your query would run 3000 times
faster with this index (on average).

With another index on rfc (lat, lon) MySQL could use a partial index
scan to find matching rows for the other conditions, giving you another
performance boost. Probably the best option would be using the R-tree
indexes for spatial data (available for MyISAM tables only):

http://dev.mysql.com/doc/refman/5.0/...xtensions.html

> Is there anything else i could do to find the cause of this?

Learn RDBMS basics. Learn about proper indexing.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Ralph wrote:
> Michael Austin wrote:
>
>> Ralph wrote:
>>
>>> Michael Austin wrote:
>>>
>>>> Ralph wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> I have development server at home which i use to develop my PHP -
>>>>> MySQL code. Recently I finished the database and some pages and I
>>>>> wanted to move it to dedicated server at my provider. I exported
>>>>> whole database using MySQL-Front and imported in on the server
>>>>> without problems using mysql -u root -p < database.sql.
>>>>>
>>>>> Everything seems to be OK but queries that take 2seconds on my
>>>>> machine takes forever on my dedicated server.
>>>>>
>>>>> Does anyone have an idea what could be the problem?
>>>>> I'm using 5.0.24a-log at home and 5.0.27-log at dedicated server.
>>>>>
>>>>> Thank you
>>>>> Ralph
>>>>
>>>>
>>>>
>>>> Define "forever".
>>>>
>>>> Are you sure you are the only one on this "dedicated" server? Make
>>>> sure the indexes are in place. Also - since it appears that you have
>>>> access "on the box", can you get explain plans from both your dev
>>>> and the prod servers?
>>>>
>>>
>>> How can i check if my indexes are in place?
>>>
>>> The explain gives me the same thing on both servers:
>>>
>>> EXPLAIN SELECT rfc.radioid, rfc.fac_callsign, rfc.fac_frequency,
>>> rfc.fac_service, rfc.comm_city, rfc.rank FROM radio_stations_tbl AS
>>> rfc, zipcodes_tbl AS zipt WHERE zipt.zip=92627 AND
>>> earth_distance_miles(zipt.latitude,zipt.longitude, rfc.lat,rfc.lon)<50
>>> ORDER BY rank DESC;
>>> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>>>
>>> | id | select_type | table | type | possible_keys | key | key_len |
>>> ref | rows | Extra |
>>> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>>>
>>> | 1 | SIMPLE | rfc | ALL | NULL | NULL | NULL |
>>> NULL | 14672 | Using temporary; Using filesort |
>>> | 1 | SIMPLE | zipt | ALL | PRIMARY | NULL | NULL |
>>> NULL | 43187 | Using where |
>>> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>>>
>>> 2 rows in set (0.00 sec)
>>>
>>> But on the development one (the one I have at home) the query takes
>>> about 3seconds. On dedicated one it takes forever.
>>>
>>> Please help.
>>> Ralph
>>>
>>
>> Based on the explain - there is no index.
>>
>> see this example:
>> mysql> show index from a;
>> Empty set (0.53 sec)
>>
>> mysql> describe a;
>> +-------+-------------+------+-----+---------+-------+
>> | Field | Type | Null | Key | Default | Extra |
>> +-------+-------------+------+-----+---------+-------+
>> | a | varchar(20) | YES | | NULL | |
>> +-------+-------------+------+-----+---------+-------+
>> 1 row in set (1.45 sec)
>>
>> mysql> create index a_idx on a (a);
>> Query OK, 2 rows affected (2.48 sec)
>> Records: 2 Duplicates: 0 Warnings: 0
>>
>> mysql> select * from a;
>> +--------------+
>> | a |
>> +--------------+
>> | 1 193 456,87 |
>> | 193 456,78 |
>> +--------------+
>> 2 rows in set (0.07 sec)
>>
>> mysql> explain select * from a where a > '1 ';
>> +----+-------------+-------+-------+---------------+-------+---------+------+---
>>
>> ---+--------------------------+
>> | id | select_type | table | type | possible_keys | key | key_len |
>> ref | ro
>> ws | Extra |
>> +----+-------------+-------+-------+---------------+-------+---------+------+---
>>
>> ---+--------------------------+
>> | 1 | SIMPLE | a | index | a_idx | a_idx | 23 |
>> NULL |
>> 2 | Using where; Using index |
>> +----+-------------+-------+-------+---------------+-------+---------+------+---
>>
>> ---+--------------------------+
>> 1 row in set (0.11 sec)
>>
>>
>>
> Hi
>
> First of all thank you very much for response!
>
> I dropped all indexes on both tables then i recreate them now tables
> look like this:
>
> mysql> describe radio_stations_tbl;
> +---------------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +---------------+-------------+------+-----+---------+-------+
> | radioid | int(11) | NO | PRI | 0 | |
> | comm_city | char(30) | YES | | NULL | |
> | comm_state | char(2) | YES | | NULL | |
> | fac_address1 | char(50) | YES | | NULL | |
> | fac_address2 | char(50) | YES | | NULL | |
> | fac_callsign | char(8) | YES | UNI | NULL | |
> | fac_city | char(30) | YES | | NULL | |
> | fac_country | char(2) | YES | | NULL | |
> | fac_frequency | float | YES | MUL | NULL | |
> | fac_service | char(2) | YES | | NULL | |
> | fac_state | char(2) | YES | | NULL | |
> | fac_zip1 | char(5) | YES | | NULL | |
> | station_type | char(1) | YES | | NULL | |
> | power | float | YES | | NULL | |
> | lat | float | YES | MUL | NULL | |
> | lon | float | YES | MUL | NULL | |
> | lat_lon_zip | varchar(5) | YES | | NULL | |
> | url | char(100) | YES | | NULL | |
> | audio_feed | char(100) | YES | | NULL | |
> | votes | smallint(6) | YES | | NULL | |
> | rank | float | YES | | NULL | |
> | format | char(15) | YES | | NULL | |
> +---------------+-------------+------+-----+---------+-------+
>
> mysql> describe zipcodes_tbl;
> +-----------+-------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+-------------+------+-----+---------+-------+
> | zip | varchar(16) | NO | PRI | 0 | |
> | city | varchar(30) | NO | | | |
> | county | varchar(30) | YES | | NULL | |
> | state | varchar(30) | NO | | | |
> | latitude | float | NO | MUL | 0 | |
> | longitude | float | NO | MUL | 0 | |
> | timezone | tinyint(2) | NO | | 0 | |
> | dst | tinyint(1) | NO | | 0 | |
> | country | char(2) | NO | | | |
> +-----------+-------------+------+-----+---------+-------+
>
> The explain on query gives me:
>
> mysql> explain SELECT rfc.radioid, rfc.fac_callsign, rfc.fac_frequency,
> rfc.fac_service, rfc.comm_city, rfc.rank FROM radio_stations_tbl AS rfc,
> zipcodes_tbl AS zipt WHERE zipt.zip=92627 AND
> earth_distance_miles(zipt.latitude,zipt.longitude, rfc.lat,rfc.lon)<50
> ORDER BY rank DESC;
> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>
> | id | select_type | table | type | possible_keys | key | key_len | ref
> | rows | Extra |
> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>
> | 1 | SIMPLE | rfc | ALL | NULL | NULL | NULL |
> NULL | 14672 | Using temporary; Using filesort |
> | 1 | SIMPLE | zipt | ALL | PRIMARY | NULL | NULL |
> NULL | 43187 | Using where |
> +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
>
>
> I don't know what else i can do :( to make it work faster. It takes 3s
> on my home machine with 2.5GHz Celeron and 768Mb RAM. The dedicated
> server is: Celeron 1.7Ghz and 512MB RAM
>
> Please Help;
> Ralph
>

Ralph,

Listen to the others here. Don't calculate the earth_distance_miles for
every item in your database. It is not only causing a table scan, it is
calculating this for every row in your table. That's a huge amount of
unnecessary overhead. Even 2 seconds is too long.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Axel Schwenke wrote:
> Ralph <onkr@remove.op.pl> wrote:
>> Axel Schwenke wrote:
>
>>> Seems your dedicated server is
>>> somewhat slower or has a ressource limit (quite usual for shared
>>> servers).
>> It takes 3s on my home machine with 2.5GHz Celeron and 768Mb RAM. The
>> dedicated server is: Celeron 1.7Ghz and 512MB RAM and it takes about
>> 10minutes.
>
> This type of query puts load on:
> - the cpu for calculating the distance function
> - the disk for reading the dependent table (zipcodes_tbl)
> over and over again.
>
> Clearly your dedicated server has a slower cpu and less memory
> (memory lessens I/O stress). But this does not explain a factor
> of 200 in performance. Probably the disk in the server is very slow?
>
>
> But again: this query is very bad. With proper indexing it should
> complete in fractions of a second. Databases are built to manage
> much more data than you have. OTOH this requires indexes!
>
>>> SELECT ...
>>> FROM zipcodes_tbl AS zipt
>>> JOIN radio_stations_tbl AS rfc
>>> WHERE zipt.zip=92627
>>> AND rfc.lat BETWEEN zipt.lat-xxx AND zipt.lat+xxx
>>> AND rfc.lon BETWEEN zipt.lon-xxx AND zipt.lon+xxx
>>> AND earth_distance_miles(...) < 50
>
> An index on zipt.zip would cut just a few rows out of the zipt table.
> How much rows with the same zipcode are there in zipt? Lets assume
> there are 5 such rows on average. Then your query would run 3000 times
> faster with this index (on average).
>
> With another index on rfc (lat, lon) MySQL could use a partial index
> scan to find matching rows for the other conditions, giving you another
> performance boost. Probably the best option would be using the R-tree
> indexes for spatial data (available for MyISAM tables only):
>
> http://dev.mysql.com/doc/refman/5.0/...xtensions.html
>
>> Is there anything else i could do to find the cause of this?
>
> Learn RDBMS basics. Learn about proper indexing.

Hi

Thank you for all suggestions I rewrote the query with BETWEEN - AND
statements and now its giving me the response within 0.12seconds.

For me basically this problem boiled down to finding the cause why the
query is so slow on the dedicated server even though the computers seem
to be teary close in speed.

Thanks again

--

Ralph