CODEWITHSUNDEEP
phpmysqlindexing
kb0000
kb0000

Reputation: 444

PHP MySQL Indexes

I want to know on Indexes.

I want to create an index on one of MySQL Tables (number of rows is 300,000). Here, are some columns

I want to create index on categoryid column. I have read in other posts that update, insert, delete makes processing slow because MySQL recreates indexes on every update. So below are my queries:

  1. What does update here means?

  2. Does it mean an update to any column of any row or update to that particular indexed column (categoryid here).

    Because in this case when ever an item is shown in search results an impression will be incremented and if user visits item's page then visits will be incremented.

  3. So, does this updation in impressions and visits will recreate the index (categoryid) every time (categoryid does not changes on updates) or it will only recreate index when categoryid is updated or new row is added?

Upvotes: 0

Views: 452

Answers (3)

liveoak
liveoak

Reputation: 11

how ever it depends on...indexing is a way of optimization. First of all you can identify slow queries with this entry: log-slow-queries long_query_time = value in my.cnf This gives you an idea if there is a need for optimization.

Next let MySQL explain the query. the most valueable is possible_keys: item_name, categoryid and the used keys key: item_name and look if there is: using_filesort. The last one (using_filesort) says you should use an index to save response time

But!, because there is one key per table used is also worth to think about aggregation in some ways:

Combined index:

  • (categoryid, item_name) when your WHERE part is categoryid="iao" AND item_name="xyz"
  • (item_name, categoryid) when your WHERE part is item_name="xyz" AND categoryid="iao"

---> the order is important!

if your WHERE part is item_name="xyz" AND categoryid="iao" the use of two indexes: 1. index: item_name helps saving time 2. index: categoryid is lost time

the most benefit of using combined index you will get when your WHERE part make use of ORDER BY e.g.: WHERE part is item_name="xyz" AND categoryid="iao" ORDER BY date_added. In this case combined index: (item_name, categoryid, date_added) save time.

And yes do it the right way:
indexing consume time by indexing (DELETE, UPDATE, REPLACE, INSERT) and save time on each SELECT

Upvotes: 1

Matt Healy
Matt Healy

Reputation: 18531

When you index a column, any changes to values in that column will take "longer" to process because the index has to rebuilt/resorted, although this change will hardly be noticeable unless you are dealing with millions of records. On the flipside, having the index means searching on those columns will be many times faster. Its a tradeoff you need to make but usually the index is worthwhile if you are searching on those fields.

Upvotes: 0

Saiyam Patel
Saiyam Patel

Reputation: 1161

it will be update the only when you change categoryid is updated or new row is added if you have created the index on the categoryid... It will update the mapping table where this indexing manage...

Upvotes: 1

Related Questions