Professional Web Applications Themes

After moving database queries are very slow - MySQL

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 ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: After moving database queries are very slow

    Ralph wrote:
     

    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 Guest

  3. #3

    Default Re: After moving database queries are very slow

    Michael Austin wrote: 
    >
    > 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?
    >[/ref]

    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 Guest

  4. Moderated Post

    Default Re: After moving database queries are very slow

    Removed by Administrator
    Axel Guest
    Moderated Post

  5. #5

    Default Re: After moving database queries are very slow

    Ralph wrote:
     
    >>
    >>
    >> 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?
    >>[/ref]
    >
    > 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
    >[/ref]

    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 Guest

  6. #6

    Default Re: After moving database queries are very slow

    Michael Austin wrote: 
    >>
    >> 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
    >>[/ref]
    >
    > 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)
    >
    >
    >[/ref]
    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
    Ralph Guest

  7. Moderated Post

    Default Re: After moving database queries are very slow

    Removed by Administrator
    Ralph Guest
    Moderated Post

  8. Moderated Post

    Default Re: After moving database queries are very slow

    Removed by Administrator
    Axel Guest
    Moderated Post

  9. #9

    Default Re: After moving database queries are very slow

    Ralph wrote: 
    >>
    >> 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)
    >>
    >>
    >>[/ref]
    > 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
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  10. Moderated Post

    Default Re: After moving database queries are very slow

    Removed by Administrator
    Ralph Guest
    Moderated Post

  11. #11

    Default Re: After moving database queries are very slow

    Jerry Stuckle wrote:
     

    Thank you I rewrote the query and now it's fine :)

    Thank you again

    --

    Ralph
    Ralph Guest

  12. Moderated Post

    Default Re: After moving database queries are very slow

    Removed by Administrator
    Gary Guest
    Moderated Post

Similar Threads

  1. Replies: 5
    Last Post: February 10th, 07:37 AM
  2. slow queries from CF 5 to CF 7
    By dr_bozak in forum Coldfusion Database Access
    Replies: 19
    Last Post: November 23rd, 05:02 AM
  3. CF 5 generating slow DB queries
    By AdamK in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 15th, 04:48 PM
  4. slow when moving paths
    By Doshi Levien in forum Macromedia Freehand
    Replies: 2
    Last Post: May 30th, 03:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139