Results 1 to 17 of 17
  1. #1
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342

    [MYSQL] Handling Large Databases

    Hi guys,

    I am working on a new website I made. It requires the list of all countries/cities in the world+longs+lats+gmt times..

    Now I found a database consisting of almost 3 million records..

    It took me some time to upload it (i had to divide it into 5 parts)..

    Now whenever I search that database it takes a loooong time (I qould say 15 seconds to return results...

    Now i know its a big database but come one, I am sure ebay got more than that?!

    How would I do to make it faster?

    I was thinking of two solutions (divide the database into 3-5 parts, by alphapetical order (a-f)(f-l)(l-q)(q-v)(v-z)...

    It would be a little hard to implement but I think it would make it faster..

    Thanks for your enlightment,

    Peace,
    Aziz
    Testing 1.. Testing 1..2.. Testing 1..2..3...

  2. #2
    Have you optimized the use of indexes?

    How are you querying the database?

    I would be willing to bet there is a lot you can do to speed things up.
    "The only difference between a poor person and a rich person is what they do in their spare time."
    "If youth is wasted on the young, then retirement is wasted on the old"

  3. #3
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342
    I actually do not have an indexes nor primaries... I dont see how that would help with my queries..

    I query the table two ways..

    1) First by selecting * where country=$country
    2) The other is where `city`='$city' (can be with more than one country)..

    Do you think that if I do add an index or a primary key it would help.. but how since really wont need to be searching the database via them.

    Thanks alot,
    Aziz
    Testing 1.. Testing 1..2.. Testing 1..2..3...

  4. #4
    Originally posted by azizny
    I actually do not have an indexes nor primaries... I dont see how that would help with my queries..

    I query the table two ways..

    1) First by selecting * where country=$country
    2) The other is where `city`='$city' (can be with more than one country)..

    Do you think that if I do add an index or a primary key it would help.. but how since really wont need to be searching the database via them.

    Thanks alot,
    Aziz
    Yes, you definately need to add an index on the country field, and one for the city field.

    And if you do searches based on both, then you should also add a joint index.

    I can assure you that your searches will be faster with proper indices.
    Adnan.
    FusionRays.com
    Shared and Reseller Plans

  5. #5
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342
    Originally posted by fusionrays
    Yes, you definately need to add an index on the country field, and one for the city field.

    And if you do searches based on both, then you should also add a joint index.

    I can assure you that your searches will be faster with proper indices.
    Even if I have thos fields country/city repeated more than once?

    Peace,
    Testing 1.. Testing 1..2.. Testing 1..2..3...

  6. #6
    Join Date
    Jan 2003
    Posts
    1,715
    Indexes are implicitly used on where and join queries that can use them (generally = < > BETWEEN and IN). In MySQL, only one index per table may be used in a query. You may have multiple indexes, and none need to be primary or unique.

    Try adding an index on the city column and one on the country column. If the city query always includes the country, you only need one index on (country, city). The improvement will be... significant.
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  7. #7
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342
    Wow... thanks guys....

    it did really make it faster.....

    thanks alot for your help,

    Peace,
    Testing 1.. Testing 1..2.. Testing 1..2..3...

  8. #8
    Originally posted by azizny
    Wow... thanks guys....

    it did really make it faster.....

    thanks alot for your help,

    Peace,
    Just curious,

    What were the performance results after the indices?
    Adnan.
    FusionRays.com
    Shared and Reseller Plans

  9. #9
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342
    It took me like to minutes to add the index for the countries via phpmyadmin... searches take split second..

    I am tryint to add an index for city via command line (wasnt able via phpmyadmin).. the city still slow with the searches..

    Peace,
    Testing 1.. Testing 1..2.. Testing 1..2..3...

  10. #10
    Join Date
    Dec 2003
    Location
    Miami, FL
    Posts
    3,262
    I'm sure ebay doesn't have 1 server handling queries. They have a cluster of 5, 10, 15+ servers handling their requests.

  11. #11
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342
    Originally posted by Rob83
    I'm sure ebay doesn't have 1 server handling queries. They have a cluster of 5, 10, 15+ servers handling their requests.
    I am not that big yet... But I actually implemeted a system like that (by dividing up the table into alph. order..

    but I dont think 3 million rows is alot in mysql...

    but after an index to both fields the system is running smoothly... thanks to all of you guys...

    Peace,
    Testing 1.. Testing 1..2.. Testing 1..2..3...

  12. #12
    Join Date
    May 2003
    Posts
    1,198
    Hey,

    I don't understand what you mean by Indexes in MYSQL? Can you give me example so I will learn
    Quote, "Google is your best friend!"

  13. #13
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342
    I think its some thing that MySQL uses to index rows to make it easier and faster to search...

    I found this (on creating indexes):
    http://dev.mysql.com/doc/mysql/en/create-index.html

    not much of an explanation on what it is exactly..

    Peace,
    Testing 1.. Testing 1..2.. Testing 1..2..3...

  14. #14
    Join Date
    Jun 2003
    Location
    USA
    Posts
    27
    Speaking database theory, make sure you place your indices and entries on a separate tablespace. Neglecting to do so will pretty much negate the effects of row indexing. However, I'm not sure if this is handled automatically by MySQL or not.

    The sql syntax is something along the lines of
    Code:
    CONSTRAINT constraint_name 
        PRIMARY KEY (or OTHER CONSTRAINT) field_name
        USING INDEX [TABLESPACE alternative_table_space_name] index_name 
        [STORAGE ...]
    ;
    
    * [ ] -- optional
    Brad Liang
    please check out LifeType

  15. #15
    Join Date
    Apr 2005
    Posts
    42
    If you are reading a book on mysql and want to find all references to "Optimising Tables" without a table of contents section you are going to have to scan through the entire book to find what you want. But with a table of contents section you get what you want immediately.

    That’s how index works in relational databases. Think of an index like the table of contents of a book.

    But there are different types of indexes but the main two are primary and foreign keys. There should be loads of tutorials on the web to explain this. But basically a primary key is a unique identifier for each row. No two rows in your table are allowed to have the same primary key.

    So if country_name was the primary key in my table and I ran a query like:

    Select * from my_table where country_name = “free_beer_land”

    The query would execute very fast, because country_name is indexed for me.

    If country_name was not indexed it would have to scan through my entire table for a match. And if you are working with medium to large databases which will contain millions if not billions of rows then searching through all that will take ages and probably kill the cpu as well.

  16. #16
    Even if I have thos fields country/city repeated more than once?
    This grabbed my attention.

    It sounds to me that another problem here is that the table structure is not normalized properly (or at all?). Is it possible for you to reorganize the data into its purest 3rd normal form?

    Also, on your queries -

    Do you really need to select * the columns in the database? Does your application really use all the columns?

    Asking mysql only what you need and nothing more will yield some benefits in speed. If you are finding that you have to perform string operations or sorting on the scripting side you may wish to re-examine what you really are asking of mysql and try to optimize/minimize that.
    "The only difference between a poor person and a rich person is what they do in their spare time."
    "If youth is wasted on the young, then retirement is wasted on the old"

  17. #17
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342

    Thumbs up

    Originally posted by innova
    This grabbed my attention.

    It sounds to me that another problem here is that the table structure is not normalized properly (or at all?). Is it possible for you to reorganize the data into its purest 3rd normal form?

    Also, on your queries -

    Do you really need to select * the columns in the database? Does your application really use all the columns?

    Asking mysql only what you need and nothing more will yield some benefits in speed. If you are finding that you have to perform string operations or sorting on the scripting side you may wish to re-examine what you really are asking of mysql and try to optimize/minimize that.
    To tell you the truth, I did not bother to normalize it..

    Its kinda big and will take me some time to do that.. but thats a big must in the future, since I am planning on publishing it as an open source script..

    and yes, it does use all the columns for each listing.

    The database is moving pretty fast now (like .5 seconds)..

    thanks alot to all of you guys,

    Peace,
    Testing 1.. Testing 1..2.. Testing 1..2..3...

Posting Permissions

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