CODEWITHSUNDEEP
sqlmysqlquery-performance
Or Weinberger
Or Weinberger

Reputation: 7482

MySQL query performance

I have the following table structure:

EVENT_ID(INT)    EVENT_NAME(VARCHAR)    EVENT_DATE(DATETIME)    EVENT_OWNER(INT)

I need to add the field EVENT_COMMENTS which should be a text field or a very big VARCHAR.

I have 2 places where I query this table, one is on a page that lists all the events (in that page I do not need to display the event_comments field).

And another page that loads all the details for a specific events, which I will need to display the event_comments field on.

Should I create an extra table with the event_id and the event_comments for that event? Or should I just add that field on the current table?

In other words, what I'm asking is, if I have a text field in my table, but I don't SELECT it, will it affect the performance of the queries to my table?

Upvotes: 2

Views: 318

Answers (6)

stivlo
stivlo

Reputation: 85546

How many events are you expecting to have?

Chances are that if you don't have a truckload of hundred of thousands events, your performance will be good in any case.

Upvotes: 0

bhamby
bhamby

Reputation: 15469

It shouldn't be too much of a hit, but if you're worried about performance, you should always run a few benchmarks and run EXPLAINs on your queries to see the true effect.

Upvotes: 0

MarkR
MarkR

Reputation: 63616

Yes, it probably will affect other queries on the same table, and you should probably do it anyway, as you probably don't care.

Depending on the engine, blobs are either stored inline (MyISAM), partially off-page (InnoDB) or entirely off-page (InnoDB Plugin, in some cases).

These have the potential to decrease the number of rows per page, and therefore increase the number of IO operations to satisfy some query.

However, it is extremely unlikely that you care, so you should just do it anyway. How many rows does this table have? 10^9 ? How many of them have non-null values for the blob?

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425813

Adding a field to your table makes it larger.

This means that:

  • Table scans will take more time
  • Less records will fit into a page and hence into the cache, thus increasing the risk of cache misses

Selecting this field with a join, however, would take more time.

So adding this field into this table will make the queries which don't select it run slower, and those which do select it run faster.

Upvotes: 2

Simone
Simone

Reputation: 11797

Yes, it affect the performance. At least, according to this article published yesterday.

According to it, if you don't want to suffer performance issues, it's better to put them in a separate table and JOIN them when needed.

This is the relative section:

Try to limit the number of columns in a table. Too many columns in a table can make the scan time for queries much longer than if there are just a few columns. In addition, if you have a table with many columns that aren't typically used, you are also wasting disk space with NULL value fields. This is also true with variable size fields, such as text or blob, where the table size can grow much larger than needed. In this case, you should consider splitting off the additional columns into a different table, joining them together on the primary key of the records

Upvotes: 1

Andrew Jackman
Andrew Jackman

Reputation: 13976

You should put in on the same table.

Upvotes: 0

Related Questions