Professional Web Applications Themes

varchar(8) but want to group by only first 4 - MySQL

I have database table with varchar(8) column that is not key. It has value for example axF12b!h, axF17F!h, axF12893, etc... axF1 is always the same and I want to goup by this so I can count. How to do this? I have more than 3 million record. This is not index key. Will this be too slow and mysql timeout?...

  1. #1

    Default varchar(8) but want to group by only first 4

    I have database table with varchar(8) column that is not key. It has
    value for example axF12b!h, axF17F!h, axF12893, etc...

    axF1 is always the same and I want to goup by this so I can count. How
    to do this?

    I have more than 3 million record. This is not index key. Will this be
    too slow and mysql timeout?

    moleskyca1@yahoo.com Guest

  2. #2

    Default Re: varchar(8) but want to group by only first 4


    com wrote: 

    SELECT substr(
    column_name
    FROM 1
    FOR 4
    ) AS alias_name
    FROM table_name
    GROUP BY alias_name

    on 3 million records it can be slow - can't you add some other
    limitations to reduce number of rows to group?? or split it to parts??

    lain Guest

  3. #3

    Default Re: varchar(8) but want to group by only first 4

    com wrote: 

    Yes, it will be slow.

    The fact you don't have an index on this column means MySQL will need to
    do a table scan. And the fact you're doing a string vs. integer
    comparison means it will be even slower.

    Adding an index for the column should help.
    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: varchar(8) but want to group by only first 4

    > Adding an index for the column should help.

    And you can give a string length with the index, so is you want to group
    on the first 4 characters, you can try to add an index with length 4. If
    this does not speed up the query, you might try the FORCE clause to
    force MySQL using that index, or else the HANDLER statement.

    Good luck.
    Willem Guest

Similar Threads

  1. varchar vs text
    By Sim Zacks in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: June 10th, 01:17 PM
  2. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  3. text and varchar
    By Mage in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 23rd, 09:16 PM
  4. VARCHAR or not ?
    By Dirk Moolman in forum Informix
    Replies: 3
    Last Post: September 9th, 08:07 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