Topic: Large table and query time

[jaymc]

Your DB design here is full of fail.

Do you handle tons of inserts/updates to this table? If not, switch the engine to isam.

What would you consider as tons of updates/inserts. How many a second to justify it being innodb?

[fenway]

I will echo the previous comment about COUNT(*) and InnoDB -- have a summary table.

[Xurion]

Change your tables to myisam. Count works faster on these.

[corbin]

Change your tables to myisam. Count works faster on these.

But MyISAM does table locks, instead of row locks.  If he has lots of updates/deletes, MyISAM could be a bad idea.

[awpti]

His next best option is to switch to MySQL 5.1 and start making use of partitions.

That's HUGE.

I tried his table layout on my own server with partitions and ~20M rows.

Queries that took 2-3 minutes without partitions took ~5-10 seconds tops with.

Lots of solutions to this issue. count() on an innodb table is just bad news.

A summary table wouldn't be a bad idea at all.. just make a single field table with a value you add/subtract from based upon the addition or subtraction of rows.

[jaymc]

This martition stuff in 5.1

Does MYSQL fully take care of it as in manage the data, or is it something I need to take care of on query level

Sounds nice

[Mchl]

I'd really like to try it out as well.
It seems that apart from defining partitions, the rest is taken care of by mysql.

awpti: Into how many parts did you partition your 20Mrows table?