MySQL Forums
Forum List  »  Partitioning

Re: Large Table Architecture
Posted by: Rick James
Date: May 25, 2011 07:45PM

> 500 mil rows
That's almost the largest table discussed on these forums.

> Col 2 varchar(75) 3000 +values
> Col 3 varchar(15) 3 possible values
In a large table? Normalize! Put col2 into another table; JOIN with a SMALLINT UNSIGNED. Ditto with col3 and a TINYINT UNSIGNED, or ENUM. Those changes will shrink the data and index size significantly, which will improve performance.

Possibly even better would be to put both of them into one table, with somewhere between 3000 and 3*3000 rows, with a SMALLINT UNSIGNED.

PARTITIONing does not necessarily provide any performance benefit. There are a few, hard to conjure up, cases where it does.

> performance while trying to generate aggregate counts
* Let's see the queries
* Consider summary tables
* Is the data unchanged (not UPDATEd, not DELETEd) once it is INSERTed?

To assist in analyzing slow SELECTs, please provide
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?

How is the data loaded? (The answer may impact the SELECTs.)

Munch on these, re Summary Tables:
http://forums.mysql.com/read.php?125,393184 (Storing a large, dense tensor (35gb scientific data) -- is mysql appropriate?)
http://forums.mysql.com/read.php?24,372008 (Index performance)
http://forums.mysql.com/read.php?10,364300 (Thinking about sorting articles..)
http://forums.mysql.com/read.php?52,359476 (Slow 'sending data' phase on mySQL query, for simple query)
http://forums.mysql.com/read.php?24,355548 (views, unions and different dbs)
http://forums.mysql.com/read.php?106,288561 (Partitioning with range(to_days) issue)
http://forums.mysql.com/read.php?10,298557 (UNIX_TIMESTAMP Duplicate key)
http://forums.mysql.com/read.php?20,294180 (Multiple complex queries)
http://forums.mysql.com/read.php?125,287739 (Large table designissue)
http://forums.mysql.com/read.php?10,282768 (Which storage engine?)
http://forums.mysql.com/read.php?10,276359 (should i go with myisam or innodb?)
http://forums.mysql.com/read.php?125,266570 (Best way to store/access large amounts of data?)
http://forums.mysql.com/read.php?24,263259 (Slow JOIN to convert IP to Country Code)
http://forums.mysql.com/read.php?10,263071 (Bitmap Index)
http://forums.mysql.com/read.php?24,253221 (InnoDB CPU Spikes)
http://forums.mysql.com/read.php?10,254332 (Very long query doubt... (Monster query...))
http://forums.mysql.com/read.php?125,252723 (Database Design for huge set of data)
http://forums.mysql.com/read.php?10,252593 (Counters)
http://forums.mysql.com/read.php?21,247124 (Solving table locking issues)
http://forums.mysql.com/read.php?10,247779 (compressing tables)
http://forums.mysql.com/read.php?125,245133 (Design approach for summary table by 3 items)

Options: ReplyQuote


Subject
Views
Written By
Posted
5873
May 24, 2011 12:55PM
Re: Large Table Architecture
3160
May 25, 2011 07:45PM
2012
May 25, 2011 09:33PM
2228
May 25, 2011 11:31PM
1824
May 26, 2011 09:49AM
2257
May 26, 2011 10:52AM


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.