 |  |  |  |  |  |  |  |
How to store a big database
Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
•
•
Join Date: May 2007
Posts: 433
Reputation: 
Solved Threads: 56
Posting Pro in Training
0
#11 22 Days Ago
Hey.
If you have a table with a VARCHAR field, you can often improve search performance on it by changing the field to the CHAR type. It has a static length, which allows MySQL to calculate a static row size for all the rows, rather then having to calculate a row size for each row. This allows it to scan the rows faster.
You won't notice any difference tho. If you provide to sort a string, MySQL will automatically pad it with spaces, and then trim them when you fetch it. The only difference is that it will increase the storage space required.
That's not true anymore. VARCHAR used to be maximum 255 chars before 5.0, but now it can be a lot bigger.
If you have a table with a VARCHAR field, you can often improve search performance on it by changing the field to the CHAR type. It has a static length, which allows MySQL to calculate a static row size for all the rows, rather then having to calculate a row size for each row. This allows it to scan the rows faster.
You won't notice any difference tho. If you provide to sort a string, MySQL will automatically pad it with spaces, and then trim them when you fetch it. The only difference is that it will increase the storage space required.
•
•
•
•
Originally Posted by mwasif 
Then you must use TEXT type if the length is more than 255.
•
•
•
•
Originally Posted by http://dev.mysql.com/doc/refman/5.0/en/char.html
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.
Please do not ask for help in a PM. Use the forums.
And use [code] tags!
And use [code] tags!
•
•
Join Date: Sep 2007
Posts: 1,472
Reputation:
Solved Threads: 136
0
#12 22 Days Ago
•
•
•
•
If you have a table with a VARCHAR field, you can often improve search performance on it by changing the field to the CHAR type.
Try not to bump 10 year old threads as it can be really annoying.
Like php then read my website at http://syntax.cwarn23.net/
Star-Trek-Atlantis - now that's what I call a movie ^_^
My favourite PC. - MacGyver Fan
Bad english note: dis-iz-2b4u
Like php then read my website at http://syntax.cwarn23.net/
Star-Trek-Atlantis - now that's what I call a movie ^_^
My favourite PC. - MacGyver Fan
Bad english note: dis-iz-2b4u
•
•
Join Date: May 2007
Posts: 433
Reputation:
Solved Threads: 56
Posting Pro in Training
0
#13 22 Days Ago
•
•
•
•
Originally Posted by cwarn23
So is the char field type faster than text field type? Because that's what I really need to know although it's a bugga it will consume more space for faster performance.
What makes it faster is that it will always have the same size for every row. If you create a
CHAR(50) column and only put 20 characters into one of the fields, MySQL will still use 50 characters to store that field. It would append 30 spaces to the original 20 and store that. (And then remove the 30 spaces when you fetch the data, so no worries there.)TEXT and VARCHAR (and VARBINARY) are variable-length fields, which shrink to fit the data each field stores. This means that when MySQL searches the table, it has to calculate the position of the data within the data-file on a per-row basis, whereas when you only use fixed-length fields, it can assume all the rows are the same size and calculate where the data is for the entire table before it starts searching.
So in short, yes, CHAR is faster than VARCHAR, but only if ALL the fields in the table are fixed-size. If you have 3 VARCHAR, TEXT or VARBINARY fields in a table, swapping one out won't change anything. It's all or nothing.
Please do not ask for help in a PM. Use the forums.
And use [code] tags!
And use [code] tags!
•
•
Join Date: May 2007
Posts: 433
Reputation:
Solved Threads: 56
Posting Pro in Training
0
#14 22 Days Ago
Please note tho, that the performance gain from using this method is usually very minor when compared to other optimization methods, such as properly indexing the fields.
A well placed index can be the difference between counting query speeds in minutes and milliseconds. (True story!)
A well placed index can be the difference between counting query speeds in minutes and milliseconds. (True story!)
Please do not ask for help in a PM. Use the forums.
And use [code] tags!
And use [code] tags!
•
•
Join Date: Sep 2007
Posts: 1,472
Reputation:
Solved Threads: 136
0
#15 21 Days Ago
Did you know that with todays cpu speeds the average computer can calculate the reverse lookup of any hash that contains up to two digits. So if there are >=2 digits being stored in a hash then the computer can calculate the original two digit string within less than a second. Just a thing I learnt when creating a dehasher in Java.
Try not to bump 10 year old threads as it can be really annoying.
Like php then read my website at http://syntax.cwarn23.net/
Star-Trek-Atlantis - now that's what I call a movie ^_^
My favourite PC. - MacGyver Fan
Bad english note: dis-iz-2b4u
Like php then read my website at http://syntax.cwarn23.net/
Star-Trek-Atlantis - now that's what I call a movie ^_^
My favourite PC. - MacGyver Fan
Bad english note: dis-iz-2b4u
•
•
Join Date: Sep 2007
Posts: 1,472
Reputation:
Solved Threads: 136
0
#16 20 Days Ago
•
•
•
•
Originally Posted by cwarn23
Did you know that with todays cpu speeds the average computer can calculate the reverse lookup of any hash that contains up to two digits. So if there are >=2 digits being stored in a hash then the computer can calculate the original two digit string within less than a second. Just a thing I learnt when creating a dehasher in Java.
Try not to bump 10 year old threads as it can be really annoying.
Like php then read my website at http://syntax.cwarn23.net/
Star-Trek-Atlantis - now that's what I call a movie ^_^
My favourite PC. - MacGyver Fan
Bad english note: dis-iz-2b4u
Like php then read my website at http://syntax.cwarn23.net/
Star-Trek-Atlantis - now that's what I call a movie ^_^
My favourite PC. - MacGyver Fan
Bad english note: dis-iz-2b4u
0
#17 20 Days Ago
•
•
•
•
Originally Posted by cwarn23
Did you know that with todays cpu speeds the average computer can calculate the reverse lookup of any hash that contains up to two digits. So if there are >=2 digits being stored in a hash then the computer can calculate the original two digit string within less than a second.
|
Similar Threads
- Store checkbox value in database (PHP)
- Store pics in database (PHP)
- Multi Location Store Software database design (VB.NET)
- how to convert images to binary to store in database (Java)
- upload images and store the location in a database (PHP)
- How to Hash and Store in Database (ASP.NET)
Other Threads in the MySQL Forum
- Previous Thread: Get ID from table
- Next Thread: About a SELECT command to execute in my MysQL Database
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Related Forum Features
Latest MySQL Posts
- Today's Posts
- Unanswered Threads
agplv3 alfresco amazon api artisticlicense bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm database design developer development distinct drupal dui ec2 email enterprise eudora facebook form foss gartner gnu government gpl greenit groklaw groupware hiring images innerjoins insert ip joebrockmeier join journalism keyword keywords laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource oracle penelope php priceupdating query referencedesign reorderingcolumns resultset saas select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency virtualization
