Can this mysql/myisam table be optimized?

Question

I run a website where I need to store the information like this:

table: logs
 * date  (date)
 * server_1 (unsigned int)
 * server_2 (unsigned  int)
 * user_id (unsigned  int)
 * ip (unsigned int)
 * service (enum)
 * traffic (unsigned bigint)

My queries look primarliy like this:

SELECT SUM(traffic) FROM logs WHERE user_id = 8381 AND date > DATE_ADD(CURDATE(), INTERVAL -7 DAY) AND service != 'unknown'

I have a composite primary key over all the fields except traffic and an index on user_id and service

is it maby more efficient to store the traffic as a float?

Also is MyIsam a good engine or should I use innodb?

Or even another database system?

The table gets really quite big (tens of millions of rows) and is relly heavily queried.

The query has a `hoster` column. Your table's definition doesn't. — ypercubeᵀᴹ, May 20 '12 at 11:54
My first choices if I wanted to speed up this query, would be an index on `(user_id, service, date, traffic)`. If it's not used by mysql optimizer, then an index on `(user_id, date)` would probably be (but the plan would use both the index and part of the table's data). — ypercubeᵀᴹ, May 20 '12 at 11:58

score 1 · Answer 1 · answered May 20 '12 at 11:51

1

MySQL won't use a composite index unless it uses the first "n" fields in the index:

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

At the very least create a separate index for each important field, especially the date column.

answered May 20 '12 at 11:51

Alnitak

334,560
70
407
495

And for this particular query, an index on `(user_id, date)` – ypercubeᵀᴹ May 20 '12 at 11:55
@ypercube or the other way around, depending on which field has higher cardinality... – Alnitak May 20 '12 at 12:07
NO, there is a range condition on date, so it won't work the other way around. – ypercubeᵀᴹ May 20 '12 at 12:46
@ypercube ah yes, better to match as few records as possible with the first field. – Alnitak May 20 '12 at 12:51

score 0 · Answer 2 · answered May 20 '12 at 12:13

As Alnitak points out, the question is how your indexes are defined. One index is probably not enough, if you want fast access on these logs.

Optimization is usually done only by indexing (at least in your case). To answer your other questions:

casting the traffic attribute to a float won't bring performance, it shouldn't make a difference.
MyISAM is fast! InnoDB will be slower, because it has all its DB transaction overhead. But: It depends on what you want. MyISAM can be really dangerous, because it can break always (Poweroffs, etc). Speed would be the main reason to use it, but I don't think in your case that would make such a big difference (however indexing does). Always use InnoDB, unless your data is not important (for logs you can maybe ignore this).
Other DBMS behave basically like InnoDB. I'm currently using PostgreSQL, which is very mature, but definitely not as fast as MyISAM.

So, try to define good indexes. The query here needs an index on user_id, date. Note: The order is important!

The index order depends on whether there are more dates, or more user IDs. Also, records for the same date are more likely to be adjacent in the database. — Alnitak, May 20 '12 at 12:35
@Alnitak: Yeah, but in this case it will be most probably the user_id, because the date field uses a `greater than` operator. — Dave Halter, May 20 '12 at 12:50

Can this mysql/myisam table be optimized?

2 Answers2