1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Weird indexation problem

Discussion in 'MySQL' started by musicpanda, Mar 9, 2020.

0
  1. #1
    I have a database with classical music works. It has three relevant fields: composer, cataloga and shortname. Cataloga contains opus numbers - left padded with spaces for easy sorting. When there is no opus number the field is empty. It is the order of the empty fields that puzzles me. It is like there is some hidden sorting happening.

    When I do "select * from works where composer=X ORDER BY shortname" I get the records in perfect order.

    However, when I do "ORDER BY cataloga,shortname" it considers only the first character of shortname. Within each character the order is seemingly random - although always the same.

    Also strange is that when I ORDER BY just cataloga I get exactly the same result within the records with an empty cataloga - although it isn't stored that way in the database.

    What could cause this behavior?
     
    musicpanda, Mar 9, 2020 IP
    JEET likes this.
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #2
    I created a mock up of your table over at http://sqlfiddle.com/#!9/26ecef/1

    I'm musically illiterate so my data is what I think you're doing, but I'm not entirely sure.

    We'll start with the left padding of your data.

    upload_2020-3-10_12-3-57.png
    Have I understood that correctly?
    If it's text and you're going to be sorting why are you left padding?

    but even if you are, and you can't be dissuaded the sort is still working as expected

    upload_2020-3-10_12-5-15.png

    The best way forward is to have some sample data of your cataloga column and how you'd expect them to be sorted.
     
    sarahk, Mar 9, 2020 IP
  3. JEET

    JEET Notable Member

    Messages:
    3,825
    Likes Received:
    502
    Best Answers:
    19
    Trophy Points:
    265
    #3
    What is the data type of cataloga field?
    Since you are storing number in it, so you should use INT type for this, and store a zero when opus number is not available.
    Then sorting will be like this:
    1, 4, 7, 10, 11...


    If you store left padded numbers in a CHAR or VARCHAR field, sorting will not happen as expected.
    In this case it will be like this:
    1, 10, 11, 4, 7...

    Here are a couple of solutions, but neither is full proof. At some point, they will all give unexpected results.
    https://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly
     
    JEET, Mar 9, 2020 IP
  4. musicpanda

    musicpanda Greenhorn

    Messages:
    6
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    23
    #4
    The sorting of the cataloga field is not the problem. It is an alphanumeric field (there are opus "numbers" like 4a, 77II and WoO59) and the solution works for me. And have left padded values like
    " 1", " 5", " 15", " 62", "234" worksok.

    There is no unique id field. Instead composer, cataloga and shortname form together the unique primary key.

    When I export the structure I get the following definition for these three fields:
    `shortname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
    `composer` varchar(25) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    `cataloga` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
     
    musicpanda, Mar 10, 2020 IP
  5. musicpanda

    musicpanda Greenhorn

    Messages:
    6
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    23
    #5
    Thank you for your replies.
    I solved it. There was some stray data in the cataloga field.
     
    musicpanda, Mar 10, 2020 IP
    JEET likes this.