Optimize MYSQL Select query in large table



  • Given the table:

    CREATE TABLE `sample` (
        `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `vendorid` VARCHAR(45) NOT NULL,
        `year` INT(10) NOT NULL,
        `title` TEXT NOT NULL,
        `description` TEXT NOT NULL
        PRIMARY KEY (`id`) USING BTREE
    )
    

    Table size: over 7 million. All fields are not unique, except id.

    Simple query:

    SELECT * FROM sample WHERE title='milk'
    

    Takes over 45s-60s to complete.

    Tried to put unique index on title and description but got 1170 error.

    How could I optimize it? Would be very grateful for suggestions.



  • If you expect there to not be a lot of rows, or you only want to return a handful anyway, then a non-unique index on title is the way to go. As this column is a TEXT datatype, you will need to constrain the length in some way, I've chosen 100.

    create index sample_idx01 on sample (title (100))
    

    An index doesn't require uniqueness by default.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2