CODEWITHSUNDEEP
mysqlindexingdatabase-indexes
PeterTheLobster
PeterTheLobster

Reputation: 1395

How can I optimize this MySql table for search speed using indexes?

As a MySql amateur I would like to ask for some advice regarding table optimization and the use of indexes.

Consider a table containing advertisements posted by users. The table has the following structure (this is a Laravel implementation but I think the code is quite self-explanatory):

Schema::create('advertisements', function (Blueprint $table) {
        $table->increments('id');     //PRIMARY KEY AUTOINCREMENTS
        $table->text('images');       //TEXT
        $table->string('name', 150);  //VARCHAR(150)
        $table->string('slug');       //VARCHAR(255)
        $table->text('description');
        $table->string('offer_type',7)->nullable()->index();
        $table->float('price')->nullable();
        $table->string('deal_type')->nullable()->index();
        $table->char('price_period',1)->nullable()->index();
        $table->float('price_per_day')->nullable();
        $table->float('deposit')->nullable();

        $table->integer('category_id')->unsigned()->index();
        $table->foreign('category_id')->references('id')->on('categories');

        $table->integer('author_id')->unsigned()->nullable();
        $table->foreign('author_id')->references('id')->on('users');

        $table->timestamps();
    });

Users on a website can search for advertisements in the table above using several criteria, such as: price range, offer_type, price_period or deal_type.

As you can see I have indexed the offer_type, price_period and deal_type columns. From what I understand this causes the DB to create a BTREE index of the values within these columns.

However these values are always going to be from a pre-defined set: For example - price_period is always one of: NULL, h, d, w, m, y (hour, day, week, month, year.) The deal_type column would always be either offer or demand.

Question: If I have a set of columns that will only contain values from a pre-defined, small range of values, is it better (performance-wise) to create a separate table for them and use foreign keys rather than indexing the columns? EDIT: After further research I realize now, that foreign keys are just a referrence tool and not a performance one and they can (and should) be indexed as well. But does having an indexed foreign key, which is a number, perform better than having a short string indexed?

Upvotes: 0

Views: 45

Answers (1)

Rick James
Rick James

Reputation: 142298

Indexing flags and other low-cardinality columns is usually useless. For example, if half the table has a certain value for a flag, it is faster to ignore the index on that flag and simply scan the entire table.

We really need to see the queries in order to judge what indexes are needed. Based on your hints, I will make a stab anyway...

"such as: price range, offer_type, price_period or deal_type" -- I assume the user will give a min and max price? Then let's build a "composite" index ending with price_per_day. Will they always be specifying all of the other three columns? And a single value for each column? If yes to all of the above, then this composite index is optimal:

INDEX(over_types, price_period, deal_type, price_per_day)

(The first 3 columns can be in any order, but the thing applied to the range needs to be last.)

If the user may include only some of those flags, and/or may include multiple values for them, then it becomes messier. Watch what users ask for and tailor extra indexes based on common queries. Use this index cookbook to help build them.

Upvotes: 1

Related Questions