Professional Web Applications Themes

Select unique based on 4 field values - MySQL

I have 'inherited' a database that has a lot of redundant info. The (only) table consists of 10 char(1) fields followed by 4 unsigned floats. There's over 3 million records in it and a quick study learned that while there are no full doubles in it several of the char combinations have the same related float values. Average selections take between 2 and 5 seconds at the moment, which leads to user complaints. The char fields can be reduced to a more comprehensive form. Before I remove the data I have to assert my theory is correct. How can I ...

  1. #1

    Default Select unique based on 4 field values

    I have 'inherited' a database that has a lot of redundant info. The
    (only) table consists of 10 char(1) fields followed by 4 unsigned
    floats. There's over 3 million records in it and a quick study learned
    that while there are no full doubles in it several of the char
    combinations have the same related float values. Average selections
    take between 2 and 5 seconds at the moment, which leads to user
    complaints.

    The char fields can be reduced to a more comprehensive form. Before I
    remove the data I have to assert my theory is correct.

    How can I make a selection that leaves those records for which all of
    the 4 floats are equal to those in other records out?

    Maybe I am not explaining this very clearly, I am not that familiar
    with this material. Perhaps an example helps explain better:

    row1 a b c d e f g h i j 0.123 0.456 0.78 0.90
    row2 k l m n o p q r s t 0.123 0.456 0.78 0.90 <-- leave out

    The table is MyISAM, no indices, no primary key. There are no rows for
    which all 10 char fields are equal. I am aware their are additional
    ways to improve performance, I'd first like to get rid of (what I
    think amounts to) 70 to 80% redundancy. The floats have up to 6
    decimals, they all are in the range 0.0 - 1.0
    Thanks for your kind assistance.

    semi_evil@inbox.com Guest

  2. #2

    Default Re: Select unique based on 4 field values

    == Quote from semi_evil ( com)'s article 


    what do you want to do with them? delete them or leave them out of your result sets!!!
    --
    POST BY: lark with PHP News Reader
    lark Guest

  3. #3

    Default Re: Select unique based on 4 field values

    com wrote: 

    Can you add a primary key? If so add an auto-increment primary key column
    and then you can use the Strawberry Query to do what you want. You haven't
    given any names for the columns so I am going to assume that the table is
    called bad_table the float columns are f1, f2, f3, f4 and the primary key
    you will add is called id:

    SELECT
    `b2`.`*`
    FROM `bad_table` `b1`
    LEFT JOIN `bad_table` `b2` ON `b1`.`f`1` = `b2`.`f`1` AND `b1`.`f`2` =
    `b2`.`f`2` AND `b1`.`f`3` = `b2`.`f`3` AND `b1`.`f`4` = `b2`.`f`4` AND
    `b1`.`id` < `b2`.`id`
    WHERE `b1`.`id` IS NULL


    Paul Guest

  4. #4

    Default Re: Select unique based on 4 field values

    On Jun 26, 10:41 pm, "Paul Lautman" <com>
    wrote:
     

    That worked! Funny name. The table shrunk about 75%, I've combined all
    the char fields into a single field now and added some PHP logic to
    map user selections to a corresponding db row. Performance is
    acceptable now.

    Thanks for the help.

    semi_evil@inbox.com Guest

  5. #5

    Default Re: Select unique based on 4 field values

    On Jun 28, 6:36 am, com wrote: 

    >
    > That worked! Funny name. The table shrunk about 75%, I've combined all
    > the char fields into a single field now and added some PHP logic to
    > map user selections to a corresponding db row. Performance is
    > acceptable now.
    >
    > Thanks for the help.[/ref]


    What's 'funny' about it? ;-)

    strawberry Guest

Similar Threads

  1. Replies: 2
    Last Post: March 13th, 05:37 PM
  2. Select, Join & field values - 2 tables
    By Justin Koivisto in forum MySQL
    Replies: 3
    Last Post: February 13th, 08:06 PM
  3. Unique Index Based on two fields
    By Hatzigiannakis in forum FileMaker
    Replies: 4
    Last Post: January 21st, 04:43 PM
  4. SQL SELECT Based on *PART* a text Field?
    By wetchman in forum ASP
    Replies: 3
    Last Post: September 15th, 07:54 PM
  5. Replies: 0
    Last Post: July 13th, 08:08 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