CODEWITHSUNDEEP
mysqlperformancejoinview
galchen
galchen

Reputation: 5290

mysql views performance vs extra column

my question is about selecting the best method to perform a job. i'll present the goal and my various solutions.

i have a list of items and a list of categories. each can belong to a number of categories.

items          (id, name, ...other fields...)
categories     (id, name, ...... )
category_items (category_id, item_id)

the items list is very large and is updated every 10 minutes (using cron). the categories list is fixed.

on my page, i'm showing a large list of items and i have category filters. the whole filtering is done on client side javascript. The reason is that the items that are currently available are limited to +- 1000, so all the data (items+categories) will be loaded together.

this page is about to be viewed many times, so performance is an issue here. I have several ideas, both will result in a good performance. in all of them, the complete list of categories will be sent. the items however...

  1. running a single select using join and group_concat. something like this:

    SELECT i.*, GROUP_CONCAT(ci.category_id SEPARATOR ",") AS category_list FROM items AS i LEFT JOIN category_items AS ci ON (ci.item_id = i.id) WHERE ... GROUP BY i.id ORDER BY ...

  2. creating a view with the above

  3. storing the GROUP_CONCAT result as an additional column. this will only be updated every several minutes under cron.

indexing is done correctly so all methods will work relatively fast. join is a heavy operation, so my question is about (2), (3):

is the view updated only on every CRUD or is it calculated on every select? if it is only updated on CRUD, it should be about the same as storing the column.

take in mind that the items table will grow, and only latest rows will be selected.

Upvotes: 0

Views: 178

Answers (2)

Umbrella
Umbrella

Reputation: 4788

Creating a view is just saving a query, (2) will still run the query and the join. (3) of course will save the time at the expense of space.

The answer, therefore is a question: Do you and your app value time or space?

Also, instead of using cron to update the cache field (your GROUP_CONCAT) you could use a trigger on the category_items table;

Upvotes: 1

Mchl
Mchl

Reputation: 62387

Solution 4. Have a MEMORY type table, which will be updated with results of your query from solution 1, by the same cron script, that updates items table.

Other than that: 1. and 2. are equivalent. MySQL's views are not materialised, so querying the view will actually run the SELECT from point 1.

Upvotes: 1

Related Questions