MYSQL :: SET Datatype Performance

Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results.

Environment
Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram

mysql 4.1 with odbc 3.51 MYISAM
windows 2003 server std edition
date, account and invoice number are indexed
Database size 18 million rows

I am querying (selecting) columns of a date and an account

our tester program that opens a socket to the Mysql database and does a select for the above n times
each time the date and the account is randomized to minimize hits on records closeby.
This program will perform over 1000 queries per second.
At the end, the Mysql socket will be closed
Even when the socket is closed each time, I still get 400 queries per second.

When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results.

Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.

Int Datatype
In mySQL, I found the date type has smallint, int and bigint. What are their
differences? I set a field with auto_increment as int type, what will happen
when the number reaches to the limit?

Set Datatype And Php
I'm using the set datatype but when I run a query from php I get a string value like "isHotel,isRestaurant,isOffice,isAsia" instead of the actual number (byte value). The string value is great ofcourse but I actually need the value, not the string. What's the best way of getting it without writing some elaborated function?

SET Datatype
As I was perusing the MySQL documentation, I noticed that there is a
lack of documentation regarding the MySQL SET datatype and the queries
used to manipulate it. In fact, the comments are longer than the
documentation. A google search shows a similar lack up information
regarding SET. I have therefore added a new article to my site for those
interested in the MySQL SET datatype, which can be found at: Code:

Which Datatype?
I'm inserting informations to the table.

Information is this kind: (HH:MM:SS) example: 16:12:01
Which kind of datatype I should use ?

If I use datetime or timestamp the record looks like this: 0000-00-00 00:00:00

I dont want to use varchar, because I maybe want to count times.

Example: StartTime - EndTime etc..

SET Datatype
I understood that SET datatype is kind of like ENUM but offers the option of choosing more than value.
The question is, what if I have more than two values: let's say i have a column of colors and the options are: "blue" "red" and "green".
What will the user be avle to choose:
1. blue
2. red
3. green
4. Now what ? all the combinations of 2 of them, or maybe all three of them.

Datatype
I have a field in my databse that stores comments submitted from a web
form. For some reason the data is being truncated when re-displayed. I
have the settings as follows:

varchar(255) is this the longest field length for varchar?

Datatype Problems
I am attempting to convert a existing database to mysql. My current primary keys are BIT(128) with a mixture of # and chars. As far as I can tell there is no equivalent to this in mysql. Any ideas as to how to get around this.

Real Datatype
I am setting up a table and want to have a real data type for one of my column.

I have read the manual and it seems I need the decimal type, however this does not work, if I insert 37.5 or 37,5 it inserts as just 37.

There also seems to be a numeric datatype in the manual but myphpadmin does not offer this as an option when I modify the table.

Text Datatype
I am managing a small database and some of the tables
need to be strings of an average length of about 2,000
characters.

I decided to use the text datatype to store this
information knowing that the upper limit is about
65,000 characters.

However, the system crashes when the user tries to
enter about 2,000 characters. I checked the
max_allowed_package and net_buffer_size variables and
they are both set to 1MB.

Could you please advice me on what to look for or what
to change in order for the system to work as it is
supposed to be working?

Datatype For Currency
What data type should I use for currency. I will need them to list items based on prices.

String Datatype
I have a column datatype is varchar(255), I want to put in <img src="..." width=xx height=yy> to stored in this column, it won't let me, if I put in img src="..." width=xx height=yy (without the greater, less than symbol) it is OK, wondering I have to change this column datatype to something else, maybe text?

Datatype Encrypted
pls i need some help here, i have a microsoft access database file that was created with the 97 version, one of the two tables in the database has 2 columns with MEMO DATATYPE. But i cant view the content cos it looks somehow encrypted.
i have tried converting to MySQL but it still shows the same.
is there an SQL statement to select from the table, there by converting the data to real text, kinda decrypting it or is there a program to view its real content

Custom Datatype
Is there any wat to create the Custom Datatype in MySQL?

To create the Customdata Type in Oracle, I am using the Following Syntax.
CREATE OR REPLACE TYPE <type_name> AS OBJECT (
<column_name> <data_type>,
<column_name> <data_type>);

I wish to know what is the Equivalent in MySQL.

TEXT Datatype
Is there any way to count the number of words in a TEXT datatype? I thought of counting the number of spaces between words then adding 1 but I want to see if anyone knows of a better method.

Altering Datatype
I'm having a little trouble and can't seem to find the correct command to change an existing field's datatype to a different one. Currently the field has datatype "int(10)" and I'd like to change it to "decimal(9, 1)".
If anyone can point me in the right direction to solving this problem.

Changing Datatype
I have a table - lets call it "TABLE". I have a column in TABLE called "COUNT", which has a datatype of "TEXT".
currently, TABLE is heavily populated with records. Column COUNT is populated with Integers. I want to change the datatype of COUNT to "BIGINT" w/o losing any of the data.

Avoid Zero In Interger Datatype
Is there any way to avoid zeros which automatically entered in database in those column those have integer data type.

or i want to enter - instead of those zero

Correct Datatype Question
I'm trying to construct a database containing information that will eventually be brought into a flash movie. It's basically information to be used in a digital clothing catalogue slideshow. I need to include the following information for each clothing item:

Style Number
Description
Colours
Sizes

I was just going to create 4 tables (haven't decided yet how these will be structured) but just wanted to ask a question about the description column. I was going to make this a text datatype even though information included in it would be be repeated. That is possible information could be a number of sleeve lengths, cuff types, fabric compositions, collar types etc. All of these could very well be placed in separate tables to avoide redundancy but I just wondered if, in this particular instance, it is really necessary to do so.

I was going to create a simple CMS so that the client could enter new items, ie. style number, description, colours, sizes and then maybe have the ability to delete these items. So I guess my question is should I be using a text datatype for the description or be breaking the information up further?

What Is The Datatype For Money In Mysql
what is the datatype for money in mysql.

How To Decrypt Password Datatype
how to decrypt the password datatype in mysql. i made a page where in i list all username and password, but the password is encrypted because i used password data type. is there any way how to decrypt it?

Datatype For A List Of Items
I have a table called students and a table called classes.

In the web page you create a student by typing in the classes he has taken. If the class doesn't yet exist in the classes database, it will be created.

Somehow, I want to store what classes each student has taken. I to be able to find, for example, all the students who have taken courses 232 and 199 but haven't taken course 104.

Is there a datatype similiar to an array/list, or is there a better way to store this information? (I want to be able to search quickly.)

Mysql To Fget Datatype
lets say i have a table..

table.myfruit
-------
fruitname | varchar(25)

the variable fruitname is a varchar with a limit of 25 characters. i want to query to database to find out that fruitname is limited to 25 characters (in a php script)

SET Datatype In Stored Functions
i'm trying to create a stored function that can tokenize a file path and give me access to each directory in the path. i hoped to do this by replacing the slashes with commas and then treating the result as a set. below is the function. the 'FIND_IN_SET' command works properly, so that proves that the file path variable can be treated like a SET, at least in some respects. i need to to be able to supply an integer index and retreive the corresponding string in the set from within my function. is there any way to do this?

delimiter $
CREATE FUNCTION ADD_FILE (filepath VARCHAR(256) )
RETURNS VARCHAR(25)
BEGIN
DECLARE i INT;
SET filepath = REPLACE(filepath, '/', ',');
SET i = FIND_IN_SET('luma', filepath );
RETURN filepath;
END$
delimiter ;

Datatype For Textarea Form Field
What is the best datatypes relavent to mysql for form fileds such as textarea? I set my textarea form field that accepts comments to map to column in mysql with datatype as Text, Is this OK?

Working With The Text Datatype In Mysql
I am managing a small database where a few fields are defined as being of
type text. In my understanding, this would allow the user to enter up to
about 65,000 characters in the text field.

However, if the user types about 2,000 characters, then nothing is entered
in the database. I have checked the max_packet_allowed and the
net_buffer_length and the limits are quite high.

Could somebody advice me on how to go about solving this problem?

"I think there is a world market for maybe five computers."
Thomas Watson, Chairman of IBM, 1943.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=...sie.nctu.edu.tw

Working With The Text Datatype In Mysql
I am managing a small database where a few fields are defined as being of
type text. In my understanding, this would allow the user to enter up to
about 65,000 characters in the text field.

However, if the user types about 2,000 characters, then nothing is entered
in the database. I have checked the max_packet_allowed and the
net_buffer_length and the limits are quite high.

Could somebody advice me on how to go about solving this problem?

I look forward to hearing from you soon.

"I think there is a world market for maybe five computers."
Thomas Watson, Chairman of IBM, 1943.

Newbie Help - Change Datatype Of A Table
--Apple-Mail-5--1016425622
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
format=flowed

how can i change the data type of a table? I'm not sure if that is my
problem. If anyone is interested here is a link what I've been trying to
do:

Retrieve The 'month' From Datatype Date
is there any function that allows me to retrieve a particular information, say, the month, from the datatype 'date'?

Displaying TEXT Datatype With C Program
I'm currently trying to display the contents of a field which datatype
is set to TEXT, I'm trying to output this through a C program.
the line is as follows:

while((row = mysql_fetch_row(result)) != NULL)
{
printf("SID: %d Sensor: %s",(row[1] ? row[1] : "NULL"));
}

I have trued %c and %s in to print the Sensor name, however %s
crashes the program and %c produces a smiley face.

Displaying TEXT Datatype With C Program
I'm currently trying to display the contents of a field which datatype
is set to TEXT, I'm trying to output this through a C program.

the line is as follows:

while((row = mysql_fetch_row(result)) != NULL)
{
printf("SID: %d Sensor: %s",(row[1] ? row[1] : "NULL"));
}

I have trued %c and %s in to print the Sensor name, however %s
crashes the program and %c produces a smiley face.

Mysql Equivalent Of Guid Datatype
helo, I'm using on PDA guid as primary key datatype. I wonder why it isn''t available in MySQL.

Now I have to define my tables on the server. As guid is not available, what should I take?

Working With The Text Datatype In MySQL
I am managing a small database and some of the tables
need to be strings of an average length of about 2,000
characters.

I decided to use the text datatype to store this
information knowing that the upper limit is about
65,000 characters.

However, the system crashes when the user tries to
enter about 2,000 characters. I checked the
max_allowed_package and net_buffer_size variables and
they are both set to 1MB.

Could you please advice me on what to look for or what
to change in order for the system to work as it is
supposed to be working?

Displaying MySQL TEXT Datatype In C Program
I'm currently trying to display the contents of a field which datatype
is set to TEXT, I'm trying to output this through a C program.

the line is as follows:

while((row = mysql_fetch_row(result)) != NULL)
{
printf("SID: %d Sensor: %s",(row[1] ? row[1] : "NULL"));
}

I have trued %c and %s in to print the Sensor name, however %s
crashes the program and %c produces a smiley face.

Problem: Displaying MySQL TEXT Datatype In C Program
I'm currently trying to display the contents of a field which datatype is set to TEXT, I'm trying to output this through a C program.

the line is as follows:

while((row = mysql_fetch_row(result)) != NULL)
{
printf("SID: %d Sensor: %s",(row[1] ? row[1] : "NULL"));
}

I have trued %c and %s in to print the Sensor name, however %s crashes the program and %c produces a smiley face.

Performance Of LIKE
I'm weighing the performance of materialised path vs. nested set algorithms, for storing a tree structure. The tree will have a lot of searches, a fair amount of inserts, but probably fairly few move-operations. The decisive factor seems to be the speed of a LIKE-query. I know, that regular expressions perform pretty well, as long as they are anchored. From this, I deduct, that LIKE must be able to perform equally good, since a path is indeed anchored. The questions is, if a LIKE will always result in a full scan, or if it can utilise indices? If it doesn't allow use of indices, would it be worth it, to use an equality comparison, on a substring (Which is essentially, what I'll be querying for) or am I just entirely missing the point?

Performance
I'm developing a PHP script and using MySQL.

What is better, have one database with many tables por all accounts (example: account1_table1, account1_table2... account2_table1....) or one database for each account?

Is there many differences in performance?

Which Is Better In Performance
country

(id) country
(1) America
(2) Germany
(3) Canada
(4) China
(5) Egypt
(6) Russia
(7) Korea
(8) France
(9) Brazil

city

(id) city
(1) Berlin
(2) New York
(3) Cairo
(4) Paris
(5) Seoul
(6) Peking
(7) Chicago
(8) Moscow
(9) Denver
I have two tables like the above.

And I have one of these tables below.

message1

(id) (city) message
(1) (2) I love New York.
(2) (5) Kumkang mountains are beautiful.
(3) (4) I was born in France.
(4) (6) Great Wall is great.
(5) (7) Chicago is a big city.

message2

(id) (city) (country) message
(1) (2) (1) I love New York.
(2) (5) (7) Kumkang mountains are beautiful.
(3) (4) (8) I was born in France.
(4) (6) (4) Great Wall is great.
(5) (7) (1) Chicago is a big city.

code1

select city, country
from message1
left join city on city.id=message1.city
left join country on country.id=city.country

code2

select city, country
from message2
left join city on city.id=message2.city
left join ccountry on country.id=message2.country
I think The codes above have a same result.(sorry, I didn't test it.)

Performance
I have a "performance" related question.

I am developing an events calendar with php and mysql. In the calendar I want any day that has events to be a link. Therefore just before a day is added, I want to query the database to see if any events occur on that date.

Is it bad practice to query the database that much? In this case it'll be hit 30 or 31 times. also, what takes up less space in the db... the mysql date data type or the php timestamp?

Performance
I have 3 tables: books, images and books_xref_images (with the xref
table merely matching up bookIDs and imageIDs.

Assuming I only want information on one book (and it's image) would
it be quicker to do 3 LEFT JOINs in one query....

.... or 2 distinct queries, with the first retreving the book info and the
second getting the image info?

Performance
One of my customers runs MySQL. They encounter performance problems. It looks to be the storage, although they run on performant EMC storage (15000 rpm disks, RAID-1). I asked them how their MySQL is setup and they run MyISAM for nearly all tables, altough their application is typical OLTP. It is in my knowledge that you should use InnoDB for OLTP databases, or am I wrong.

My customer told me that they did tests with MyISAM vs InnoDB and he told me MyISAM was more performant. Is that possible ? I can imagine that they never will have enough server memory to keep all tables of their 50GB database in memory.

If there is not enough server memory to keep all tables in memory via MyISAM, what will happen ? Swapping, resulting in loads of extra I/O ?

Best Performance For WHERE LIKE AND ..
I'm doing heavy searches on a table with statements like

SELECT * FROM thetable WHERE col1 LIKE '%ab%' AND col1 LIKE '%cd%'

.. and so on. I need to search for keywords shorter than 4 characters, so I can't use fulltext MATCH-AGAINST. When searching using WHERE-LIKE -method above, should I use FULLTEXT or INDEX in the column in question?

Performance
i am having a problem at the moment with my pc. i keep getting performance issues when running mysql queries and when i go in to task manager
mysqld-nt.exe is always quoted as a very high number on cpu.

is there something i may have done incorrectly to cause this.

i have been using php/mysql for a good while now and have never came across this problem before?

it is the same regardless of which queries i am running (including those which always worked fine before).

Best For Performance
It's a web application, accessed via internet, with around 100.000 clients and
each client have around 10 users. This web app have a database of around
100 tables.

Ok, the question is: Is it better to have each client with it's own catalog on
the database (each catalog with around 100 tables) or is it better to use one
catalog for all the clients (the tables would have a lot more records this way,
but only one catalog)?

I'm considering performance.

Performance
I have problems with mysql-performance. When I check "Status", there is written: 23% of all questions are "change db". What does it mean? Select Task are only 13%. Could this be the reason of performance problems?

Performance - Select *
I'm trying to optimize an application, which is putting way too much load on the rdbms. Currently most queries select *, but I'm wondering how much it matters in terms of performance to specify the exact number of columns needed ? Obviously, I'd transfer less data from rdbms to application layer, but apart from that is there anything to archieve ?

Performance Troubles
I'm having some trouble with optimising my database queries. I've recently been making use of a feature that my host provides that lets me create reports on any queries that are taking more than 1 second to generate. Quite honestly I'm not sure if it could be the table structure, indexing issues or just the overall query that's horribly coded.

I've attached the query report from the server in a post attachment. As you can see I like using my inner joins, hehe.

Here are the table rows, in case you're wondering how big tables are relative to each other:

archive - 428,720 rows
blo - 3,741 rows
users - 3,945 rows

It might be useful information.

So yeah, basically what can I do, if anything, to get these queries to move along. Points would be awesome.
:-)

Performance Question
CREATE TABLE __testpages (
rowid int(11) NOT NULL auto_increment,
user varchar(255) NOT NULL default Ɔ',
pagebyurl varchar(255) default NULL,
timebegin int(10) NOT NULL default Ɔ',
pagenum int(5) NOT NULL default Ɔ',
lastpage tinyint(1) NOT NULL default Ɔ',
day char(2) default NULL,
month char(2) default NULL,
week char(2) default NULL,
quarter char(2) default NULL,
year varchar(4) default NULL,
PRIMARY KEY (rowid),
KEY user_2 (user,`year`,`month`,den),
KEY user (user)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

and I genarate 15,000,000 rows. Then I execute the query:
SELECT COUNT(*) FROM __testpages where user='xxx'

the user index cardinality is 11

this query returns between 17,000 and 11,000,000 rows depends on the 'xxx' and takes between 5 and 20sec on my mashine. Is it normal or it is too slow?
I am running the mysql server on dedicated server I don't know exactly its parameters