Monthly Archives: February 2014

MySQL(1)- Index Optimization

  • Table Schema

This is the table condition. There are five fields.


My command is here to add new data.

MonthlyMovie.objects.filter(MovieID=movieid, Time=itime).update(Count=clicktime)

Now my requirement is to add big data to this table, almost 700,000 rows.

At first, I want to optimize each field. For example, I try to use the small field type. Int is better than char. (B-Tree will easily compare key value by int value, not char value)
Meanwhile, I use constantly to define each field’s length. But the kind of optimization doesn’t have great influence on efficiency. And then, I try to create the index on the table. Different index on the different field would give the variable result.

  • Experiments

I do some experiments on it.

solution1: idx1:MovieID,Time,Count;idx2:MovieID,Time,Rating
time: 846.411153
solution2: idx1:Time,MovieID,Count;idx2:Time,MovieID,Rating
time: 855.032292
solution3:idx1:Count;idx2:Rating;idx3:MovieID, Time
time: 840.703046
solution4:idx1:Count;idx2:MovieID, Time
time: 826.489348
  • Conclusions:

    • focus on query
    • focus on sort
    • don’t create an unused index
      because more indexes will take up more storage space. meanwhile, insert/remove data speed will be influenced. 
    • understand composite index
  • Tips:

    • indexes are not just for the primary keys or the unique keys. If there are any columns in your table that you will search it, you should almost always index them.
      You can assume index as a book of the index which helps you to find accurate content more easily.
    • The rule applies on a partial search like “last_name LIKE ‘A%'”. When searching from the beginning of the string, MySQL is able to utilize the index on that column.
      MySQL uses B-Tree to do index; it can do the search based on the prefix. From this point, you will understand mid-search or end-search are not applicable in MySQL, the prefix is an understandable way to set as an index.
    • Some of the searches can’t use the regular indexes. For instance, when searching for a word, like “WHERE post_content LIKE ‘%apple%'”, you will not see a benefit from a normal index.
    • If your application contains many JOIN queries, you need to make sure that the columns you join by are indexed on both tables.