Large Table Width Slows 5.x Performance?
Posted by: Hauser Klink
Date: June 05, 2007 06:06PM

Hi folks, I am running a MySQL v.5.0.22-Debian_0ubuntu6.06.3-log with PHP extension mysqli, protocol version 10, on a server with Apache 2.0.55 and PHP 5.1.2.

Posted this also in Information Schema: http://forums.mysql.com/read.php?101,156062,156062#msg-156062 No response, because it is less read I guess.

In older versions of MySQL (3.2x.x && 4.x), my tables were printed by http using phpMyAdmin just a fast as the net would send them. I am reluctant to believe my complaint is a phpMyAdmin issue because the exact same version of those scripts on the old server sent queries screaming fast. However in this recent upgrade, the access to the large tables literally takes about 10-12 seconds, even looking at it locally (so it isn't the net being slow). Smaller tables with <50 columns are accessed in a blink. It seems the fatness of the tables is part of the problem and I don't know what has changed that would cause this. I assume there is a configuration setting that must be addressed.

How fat you say? My main job is as PHP consultant to university researchers collecting data over the web from forms. The tables I am discussing are at least 350 columns wide. The data schema for the last 5 years and, I believe, requires that ~300 fields contain separate datas for each record. We deal in about 500,000 new records per year, so rotating the structure and making the data collect row-wise with some key marker would give us tables of 150 million records, and even MySQL with legendary access speed can't make that act fast. Interestingly, as a test I have written around this and used CONCAT() to agglomerate 1 long string in a text field instead of 300 separate fields and it goes appropriately fast. However, this is only a test and not the format researchers need.

The only other thing that I *suspect* may be different is the addition of InnoDB in this recent version (tables creates all remain exactly the same). Does this sound right? How can I do an experiement to temporarily disable this and see if things accelerate?

Any advice on table structure is also appreciated. Trying to avoid kludgey methods though (somebody said in another forum break it out into tables of 50 cols each and link them up....er no, thanks).

HK


PS: Final notes, UPDATE seems perfectly swift; SELECT is where the sluggishness is most apparent. Also, this is sluggishness occurs even with a SELECT query on an *truncated* table(!).

Options: ReplyQuote


Subject
Written By
Posted
Large Table Width Slows 5.x Performance?
June 05, 2007 06:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.