Professional Web Applications Themes

speed penalties? - MySQL

Hi everyone, I'm planning on using MySql/Php on either a Windows or, more likely, a Linux base. However, my database will be several thousands of records, in many hundreds of tables, searched using a "Search" query with a regex expression included. I'm wary about the speed penalties, and I would be very grateful if some metrics or timings could be provided....? Many thanks Paul -- http://www.paullee.com...

  1. #1

    Default speed penalties?

    Hi everyone,
    I'm planning on using MySql/Php on either a Windows or, more likely, a
    Linux base. However, my database will be several thousands of records,
    in many hundreds of tables, searched using a "Search" query with a
    regex expression included. I'm wary about the speed penalties, and I
    would be very grateful if some metrics or timings could be
    provided....?


    Many thanks

    Paul
    --
    http://www.paullee.com

    Paul.Lee.1971 Guest

  2. #2

    Default Re: speed penalties?

    Paul.Lee.1971 wrote: 

    Paul,

    Thousands of rows is no problem. But "Hundreds of tables"? Even the
    most sophisticated system I ever designed didn't require that many tables!

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

  3. #3

    Default Re: speed penalties?

    On 30 Jun, 00:56, Jerry Stuckle <net> wrote: 


    >
    > Paul,
    >
    > Thousands of rows is no problem. But "Hundreds of tables"? Even the
    > most sophisticated system I ever designed didn't require that many tables!
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    The problem I see is either hundreds of tables, or a table containing
    a huge amount of rows/columns!

    Paul.Lee.1971 Guest

  4. #4

    Default Re: speed penalties?

    Paul.Lee.1971 wrote: 
    >> Paul,
    >>
    >> Thousands of rows is no problem. But "Hundreds of tables"? Even the
    >> most sophisticated system I ever designed didn't require that many tables!
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================[/ref]
    >
    > The problem I see is either hundreds of tables, or a table containing
    > a huge amount of rows/columns!
    >[/ref]

    If you properly normalize your database, you should need neither.

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

  5. #5

    Default Re: speed penalties?

    On 30 Jun, 01:29, Jerry Stuckle <net> wrote: [/ref]
    > [/ref]
    > [/ref]

    >
    > If you properly normalize your database, you should need neither.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Ulp! I need help!

    Paul.Lee.1971 Guest

  6. #6

    Default Re: speed penalties?

    Paul.Lee.1971 wrote: 
    >> If you properly normalize your database, you should need neither.
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================[/ref]
    >
    > Ulp! I need help!
    >[/ref]

    Start with "Database Normalization". There's some good info on the
    MySQL website, or you can google for some topics on it.

    Proper database design is not a hit-and-miss proposition. The
    procedures are well defined to limit duplicate data while providing good
    performance.

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

  7. #7

    Default Re: speed penalties?

    >>> The problem I see is either hundreds of tables, or a table containing 
    >> If you properly normalize your database, you should need neither.[/ref]
    > Ulp! I need help![/ref]

    A "huge" amount of rows is not a problem. That is what databases are
    designed for. A huge amount of columns or a huge amount of tables
    usually is a smell of a poor design.

    As you stated earlier, you want to search with a regular expression. Now
    that can very well be a really heavy search, because MySQL has to check
    all rows to do this. In "normal" searches (that is, search for a given
    value of a field) the database does not check all rows but does a binary
    search if you have indexed the column properly.
    While a regular expression search does take some time, it cannot really
    be optimized without knowing more on the searches.

    Best regards
    Dikkie Guest

Similar Threads

  1. Replies: 2
    Last Post: August 20th, 02:54 PM
  2. Replies: 1
    Last Post: August 15th, 11:50 AM
  3. Please HELP! ISO speed set to wrong speed.
    By Mick in forum Photography
    Replies: 4
    Last Post: July 27th, 02:44 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