Please login or register.

Login with username, password and session length
Advanced search  

News:

(2009-05-04) The Rules and Terms of Service have been updated. Please make sure you read, understand, and follow them.

Pages: 1 [2]  All

Author Topic: Large table and query time  (Read 889 times)

0 Members and 1 Guest are viewing this topic.

jaymc

  • Devotee
  • Offline Offline
  • Gender: Male
  • Posts: 1,471
    • View Profile
Re: Large table and query time
« Reply #15 on: August 14, 2008, 05:38:12 AM »
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?
Logged
I would love to change the world, but they won't give me the source code

fenway

  • MySQL Si-Fu / PHP Resident Alien
  • Global Moderator
  • 'Insane!'
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 11,605
    • View Profile
Re: Large table and query time
« Reply #16 on: August 14, 2008, 09:09:55 AM »
I will echo the previous comment about COUNT(*) and InnoDB -- have a summary table.
Logged
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

Xurion

  • Enthusiast
  • Offline Offline
  • Posts: 258
    • View Profile
Re: Large table and query time
« Reply #17 on: August 14, 2008, 03:16:57 PM »
Change your tables to myisam. Count works faster on these.
Logged

corbin

  • Guru
  • Freak!
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 6,735
    • View Profile
Re: Large table and query time
« Reply #18 on: August 14, 2008, 04:26:46 PM »
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.
Logged
Why doesn't anyone ever say hi, hey, or whad up world?

awpti

  • Enthusiast
  • Offline Offline
  • Gender: Male
  • Posts: 420
    • View Profile
    • WWW
Re: Large table and query time
« Reply #19 on: August 14, 2008, 09:14:53 PM »
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.
Logged
Server: Apache 2.2.11 - PHP 5.2.9, 6.0.0-dev - MySQL 5.0, 5.1 and 6.0 - Core2Duo 2.66Ghz, 4GB Mem
GeekLAN - Ignited Jobs - LAMP Tips

10+ Years of hobby PHP Development and Database Design.

jaymc

  • Devotee
  • Offline Offline
  • Gender: Male
  • Posts: 1,471
    • View Profile
Re: Large table and query time
« Reply #20 on: August 17, 2008, 01:44:57 PM »
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
Logged
I would love to change the world, but they won't give me the source code

Mchl

  • Guru
  • Fanatic
  • *
  • Offline Offline
  • Gender: Male
  • Posts: 4,854
  • That's Largo in my avatar, not me.
    • View Profile
    • WWW
Re: Large table and query time
« Reply #21 on: August 17, 2008, 01:51:06 PM »
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?
Logged
NetBeans fanatic | ExtJS masochist | C++ denier
PHP4 & MySQL4 are no longer supported.
PHPFreaks Tutorials | PHP Debugging: A Beginner's guide | PHP Security Tutorial || How To Ask Questions The Smart Way
I'm frustrated with the size of the universe - do not approach

PHP Freaks Forums

 
 
Pages: 1 [2]  All
 

Page created in 0.053 seconds with 19 queries.