Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts

    MySQL Database Structure help

    Hi All,

    I have a Windows 2008 R2 server on which I host my clients websites, web applications and their associated MySQL databases. The applications and associated databases work fine, but I want to ensure that I have got the MySQL databases as optimally designed as possible for reliability and speed of searching. For example, correct choice of table type, field types for the various data types stored, etc. If this is done for one database, I can use that as a good worked example to review my other databases.

    I have read through a number of tutorials, but these databases are used daily so I can't get this wrong.

    If I upload a .sql dump of a current database, is there a MySQL Database Structure expert willing to advise me?

    Cheers

    Gary

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    We don't need to see a mysqldump. That includes much more information than is needed.

    Just doing SHOW CREATE TABLE xxx (where xxx is the table name) is sufficient. And you don't need to show all tables. For example, if you have a "lookup table" of categoryid/categoryname pairs, that's obvious enough that you don't need to show it.

    But I'm a little skeptical that you can show us one set of tables in one database and expect to be able to use the same schema in other databases, especially in other databases designed for other (as you called them) web applications.

    Typically, each application will have unique needs.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi OP,

    No, that wasn't my intention. I was looking for a 'good worked example' to help me interpret some of the tutorials on the net that (to me) appear to contradict each other. For example - indexes. One tutorial recommended using the auto increment record id as the index, another said no, don't include the auto increment record id as an index as it will just index all the records anyway.

    It is more your decision making regarding optimising the database/tables that I need to get a grip on.

    Cheers

    Gary

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    With MySQL, you have no choice: auto_increment fields *MUST* be UNIQUELY indexed. Meaning they are most commonly the PRIMARY KEY of the table. Not true of other DBs, but MySQL makes this mandatory. You can't even *create* the table if the auto_increment field isn't properly indexed.

    But a bigger question is whether you should even *have* a field that is auto_increment.

    If there is a *natural* primary key (e.g., phone number, social security number, anything guaranteed unique per record in the given table), you might be better off using it as the primary key and omitting the auto_increment field.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    Also, MySQL is not the strongest DB in the world when it comes to index usage. It has many limitations not found in the professional products (in particular, in SQL Server and Oracle). You often have to pick and choose your indexes VERY carefully with MySQL in order to get optimal performance.

    One set of rules:
    -- If you have a composite index (example: an index on both LastName and FirstName), then MySQL can only take advantage of it IN THE ORDER that the fields of the index appear. In this example, if you used WHERE FirstName = 'John' and did not mention LastName in the WHERE clause, then MySQL could *NOT* use that index.
    -- MySQL can not use LIKE with an index unless you use WHERE field LIKE 'startswith%'. That is, you can only use the % wildcard on the END of the LIKE. If you did WHERE field LIKE '%contains%' then MySQL won't even try to use the index.

    Both of these can put severe restraints on your index usage, and there are more quirky MySQL-only rules. So about the best you can do is create a design and then test it. Luckily, MySQL provides the EXPLAIN statement to help you understand what it does when it runs a query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Gary Williams (08-19-2013)

  • #6
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi OP,

    That is the best bit of MySQL information re indexes. I have not found that anywhere else. I have some re-coding to do as I have used some 'likes' in my statements.

    I have not used the 'explain' statement. I'll read up on that.

    Most often, my users have to search the main database tables by a number of fields simultanuously, ie, ToDate, FromDate, Name, OrderType, etc, to generate the report they need to see. If I created an index to cover all fields that can appear in a requested search, I would end up indexing virtually every field. What rules of thumb are their to help me decide what to index? Sorry if this is such a noobie question, but I have never managed to get my head around this.

    Cheers

    Gary

  • #7
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi OP,

    I have been looking at many dB table maintainance tutorials. I can follow them all OK. I came across one the refered to the change of default table type from MyISAM to InnoDB which improves heavy read/write performance. Most of my tables are MyISAM. Unless you say otherwise, I'll plan some update/conversion maintainance on my dB's.

    Cheers

    Gary

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    MyISAM is best for heavy writing. By a long ways. For reading, it's much closer to being a toss-up. For mixed usage: Ehhh...I think there is a big fat "it depends..." in there.

    But MyISAM does *NOT* enforce referential integrity (that is, even if you use Foreign Key constraints, they are ignored). For 90% of normal DB usage, you are better off with INNODB. But only if you then also actually code and use foreign keys.

    Unless you are trying to add gigabytes of data per hour, I'd stick with INNODB.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Gary Williams (08-20-2013)

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    Quote Originally Posted by Gary Williams View Post
    That is the best bit of MySQL information re indexes. I have not found that anywhere else.
    Consider just using the MySQL docs, instead of relying on outside sources.

    For example:
    http://dev.mysql.com/doc/refman/5.5/...n-indexes.html
    MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on.
    The bit about using indexes with LIKE is also in there somewhere, though I didn't find it just now with a quick search. Read it many moons ago.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Regular Coder
    Join Date
    Sep 2002
    Location
    South East UK. 35 miles east of London, in sight of the River Thames.
    Posts
    300
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi OP,

    I think that the database usage of my applications is quite modest compared with the big boys apps, but I still want to do a 'best practice' design job of the db's. I'm definately not adding gigabytes of data per hour so I'll plan on converting my db tables to INNODB after reading up on the foreign keys.

    I feel as if I am now getting to grips with mysql db's.

    Many thanks for your help.

    Gary

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,216
    Thanks
    75
    Thanked 4,344 Times in 4,310 Posts
    And given that of all the storage engines supported by MySQL, INNODB is the only one that has both power *and* enforces referential integrity, I would definitely go with it. After all, any other major DB is going to do all that. MyISAM is really best reserved for what I would call "real time" work. Where you need to squeeze out ever last drop of performance.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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