Please login or register.

Login with username, password and session length
Advanced search  

News:

Get quality web hosting, virtual private servers, reseller web hosting, and dedicated servers from www.webhostfreaks.com or www.serverpowered.com!

Maintenance Notice

PHPFreaks has successfully moved to a new Dedicated Server, hosted by Server Powered. Please help support future upgrades by Donating.

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

0 Members and 1 Guest are viewing this topic.

jaymc

  • Member
  • Offline Offline
  • Gender: Male
  • Posts: 1,333
    • View Profile
Re: Large table and query time
« Reply #15 on: August 14, 2008, 05:38:12 AM »
Quote from: awpti on August 14, 2008, 05:31:25 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
  • **********
  • Offline Offline
  • Gender: Male
  • Posts: 9,910
    • 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

  • Member
  • Offline Offline
  • Posts: 257
    • 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

  • PHP Help Guru
  • **********
  • Offline Offline
  • Posts: 3,376
    • View Profile
Re: Large table and query time
« Reply #18 on: August 14, 2008, 04:26:46 PM »
Quote from: Xurion on August 14, 2008, 03:16:57 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?
Now I'll be uncreative, and put stats no one cares about.
Win Vista (It doesn't suck!) | Apache 2.2 | PHP 5.2 | MySQL 5 | MSSQL 2008
Fedora Core 9 | Apache 2.2 | PHP 5.2 | MySQL 5
Quote from: Daniel0 on Yesterday at 03:32:50 PM
After all, why would you insert your penis into a hole for no reason whatsoever?

awpti

  • Member
  • Offline Offline
  • Gender: Male
  • Posts: 356
    • 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

Blog:
-- GeekLAN
Projects:
Ignited Jobs - LAMP Tips

jaymc

  • Member
  • Offline Offline
  • Gender: Male
  • Posts: 1,333
    • 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

  • Member
  • Online Online
  • Gender: Male
  • Posts: 1,369
  • 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
PHP 5.2.6 | MySQL 5.1.28
Input queue: sqlite, Zend Framework, PostgreSQL
ExtJS masochist
 

Page created in 0.041 seconds with 17 queries.