INDEX with Table with 5 MILLION of rows

Discussion in 'Databases' started by xantoniox, Sep 26, 2009.

0
  1. #1
    Hi everyone,

    I have a table with 5 Million of rows !!

    This is the table:

    CREATE TABLE IF NOT EXISTS `users` (
    `user_id` int(9) NOT NULL auto_increment,
    `user_firstname` varchar(64) collate utf8_unicode_ci NOT NULL default '',
    `user_lastname` varchar(64) collate utf8_unicode_ci NOT NULL default '',
    PRIMARY KEY (`user_id`),
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;


    What kind of INDEX I have to use for best performance for this 2 queries:

    1) SELECT user_firstname, user_lastname FROM users LIKE X%

    ( where X is a word of one or plus char )

    2) SELECT user_firstname, user_lastname FROM users WHERE user_firstname=Y

    ( where Y is a generic user_firstname )


    Thank you :)
     
    xantoniox, Sep 26, 2009 IP
  2. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I you have problems holding the index in memory you can also use a partial index on for example the first 16 characters
    CREATE INDEX user_firstname_idx ON users (user_firstname(16));
     
    chisara, Sep 27, 2009 IP
  3. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #3
    Lets calculate it from basic.

    I will presume that table is kept MyISAM considering that it has very less concurrent write else table lock will become bottleneck.

    Say one row utilizes complete 132 byte [4 for id, 64 for first_name, 64 for last_name].
    That way, with 5 Million records, your table size would be around 630 MB.

    Your 2 queries are not using user_lastname field for search so keeping index only for user_firstname will suffice. The point is what type of index? Partial? Normal Full? Full Text?

    Partial wont help since you want to do exact name search.
    Full Text will be overhead since you don't wanna do match against.
    Normal Full index will be good to go, but wait, can it be more fast then normal?

    Yes, it can be, if you keep user_firstname as char instead of varchar. char type field takes complete space of the specified length, but indexing and search using that index becomes much faster as the hop or pointer calculation in index file becomes very simple and quick.

    In my opinion, change user_firstname to char(64) and add a normal index onto it. However, I would strongly recommend to do it on development database first and prepare a benchmark between varchar & char type field.
     
    mastermunj, Oct 20, 2009 IP