SET Datatype Performance
I have a “little” performance problem using the SET datatype.
I have a database with over 800,000 rows which have 34 columns which are enum(‘0’,’1’) to describe various categories.
So when I search for a certain category or categories the search would be like:
Select count(id) from my_table where cat1=’1’ and cat10=’1’ and cat25=’1’;
If cat1 is indexed and cat10 is not.
Select count(id) from my_table where cat=’1’;
would return data in less than a sec while
select count(id) from my_table where cat10=’1’
might take over 5-6 secs to return the value.
Since I can’t index all 34 category columns, due limitations of how many indexes I can create (there are other fields that required index besides the categories) I looked into how to do this more efficiently. I came up with the idea if using the set datatype. I created a test table
CREATE TABLE set_test(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
cats SET('cat1','cat2','cat3',……'cat34')
);
I loaded it up with 800,000 values from my main table and searches like
Select count(id) from set_test where cats&1;
Would return the count in less than a second.
So far so good. So I added a new column to the main database with the ‘cats’ column, containing the same info I had in the set_test table. To my surprise:
Select count(id) from my_table where cats&1;
Would take over 6 secs to return the value.
Or course the table has 45 columns (including the old 34 category columns) instead of two, but does it really make that much of a difference? If so, what can I do to get a decent performance?
View Complete Forum Thread with Replies
Related Forum Messages:
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.
View Replies !
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: .....
View Replies !
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?
View Replies !
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?
View Replies !
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?
View Replies !
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..
View Replies !
Datatype Should I Use For Prices?
I am currently working on a field that contains 'Prices' I declared its datatype as double, Length=7 and Decimals=2and it works fine but a problem arises when the decimal contains a fixed .00
View Replies !
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.
View Replies !
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.
View Replies !
Datatype For Birthday
I'm just wondering what datatype should I choose for birthday? The date -would naturally be most logical, but there is also a possibility for users to give only year. So should I use: a) date - in which case those without day&month would be marked as 1.1. b) string, and then parse (php app) c) something else?
View Replies !
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
View Replies !
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?
View Replies !
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.
View Replies !
SET Datatype ENUM
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.
View Replies !
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.
View Replies !
Alternative To A SET Datatype
Currently I'm using a SET to contain a list of selected days, e,g: "1, 2, 3, 6" or "4,6" and then to check if today is in that set I use: Code: FIND_IN_SET(DAYOFWEEK(NOW()), days) I read in the MySQL documentation about SETs and it seems that perhaps I should use another datatype. I know a SET 'works', but is there a more appropriate/better datatype/design that I should use instead?
View Replies !
Working With The Text Datatype
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?
View Replies !
Currency Decimal DataType
I won't be asking about what data type to use for currency since I know DECIMAL is the recommended one. My question is what values should I use in DECIMAL(M,D). I will be storing big amounts of currency up to to a million. So what is the precision and scale recommended for these amounts
View Replies !
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)
View Replies !
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 ;
View Replies !
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.)
View Replies !
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?
View Replies !
MySQL Datetime DataType
I'm just trying to insert a datetime value into mysql datetime feild, but everytime run the script, and then look in the data base all i see is 0000-00-00 00:00:00 sql= "INSERT INTO ForumPosts (PostDate) VALUES ('" & NOW() & "')" set rs= MyConn.execute(sql) set sql= nothing
View Replies !
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?
View Replies !
The Point Of The BLOB Datatype?
A new project of mine requires keeping multiple versions of an article as it's changed. I know this capability is in MediaWiki, so I downloaded it in hope of uncovering how they do it. Viewing the database I came across this unviewable blob data. I see learnt that a blob field basically stores raw binary data. So the question is (like in the Title), what is the point of the datatype, BLOB? And why has it been used in the case of MediaWiki? I guess I also want to know how I can convert it to UTF-8 in PHP.
View Replies !
INT Datatype Display Length, What For?
We just noticed that a field we defined as INT(3) contained the integer 15525 which is 5 characters long. After digging some through the manual I found this which explains that the defined display length does not constrain the possible values that are put in the field. The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters. ~http://dev.mysql.com/doc/refman/5.0/...ric-types.html What is the use of the display length then if it isn't used to enforce the fields contents?
View Replies !
Which Datatype? Scientific Notation?
I'm creating a software that deals with big numbers. Bigint can handle them, but I'm wondering if its my best option. Each big number only contains three significant digits. By that I mean, they are all in this form: 537000000 599000000 1620000 1660000000 32300000000 2020000000 Now I don't know much about SQL, but I'm thinking there must be some datatype that uses scientific notation to store these more efficiently, IE: 5.37E8 5.99E8 1.62E6 1.66E9 3.23E10 2.02E9
View Replies !
Text Datatype Crash On More Than 2,000 Words
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.
View Replies !
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.
View Replies !
Replication :: Issues With LongBlob Datatype
Has anyone ever had any issues with the LongBlob datatype and replication. I'm using 4.1.14 I got it working perfectly, it did thousands of queries and then when I did a show slave status. the sql thread was running but the I/O thread was not. I got the log_position and it's a longblob, microsoft word doc. huge binary string. I tried to stop slave; the skip_position = 1 thing, then start slave, but the I/O still wouldn't start up? I'm not sure how to get it back going or fix the longblob problem. I got no errors on the slave, the I/O is just dead, seemingly irreversable.
View Replies !
Time Datatype And Error 1064
Time and date are still giving me grief as I try to insert some data into a table. The query I'm running is: INSERT INTO security VALUES (24623,2003-07-04,18:23,10:03); The datatypes are char, date, time, time #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':23, 10:03)' at line 1 I've been into the manual 10.3.2 TIme -
View Replies !
Count Of Text Datatype Column
What is the SQL syntax to count the number of characters for a column which is defined as 'Text' datatype ? Also, We are using a column called 'Note' of Text datatype which allows user to enter alphanumeric values from front end.
View Replies !
Timestamp :: No Datetime Format And Datatype
Since upgrading our server and now having mysql 4.1.14, we have a problem with our timestamp column. We need it to be in 14 digit mysql format, not datetime format. Our affiliate software will not run this way for certain functions. Is there a workaround for this, or do we need to go back to our previous version?
View Replies !
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?
View Replies !
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?
View Replies !
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.
View Replies !
Datatype TIME - Select Statement Format
The table I have has two columns of ID and INTERVALS. The INTERVALS is datatype TIME. Whenever I do a select I always get the table back showing the time in the format HH:MM:SS like for 11am I get back 11:00:00. I only want HH:MM like 11:00. How do I achieve this? I have tried to limit to 2 decimal places but that has not worked so far. ...
View Replies !
Char DataType Vs Varchar Data Type
I understand that char is faster but fixed lenght variable and var char is variable lenght but those that mean that char always has to be fixed say 10 charactors and can never be more or less
View Replies !
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.
View Replies !
IO Performance
I'm running a 5.0.51a mysql server on pentium M. My application is made of 30 myisam tables written every 2 seconds and a very few reads (1 evry hour) from the whole table. Each table is a kind of circular buffer (fixed number of line, current line). Each line of a table contains a medium blob whith fixed size (about 70k). I know the average output (Kb/sec) from the client to th server, says 200 Kb/sec. When trying to measure this flow with 2 invocations of "show global status like '%Bytes%'; followed by division by the elapsed time, I find between 1 and 2 times more (400 Kb/s). When I measure the output to the disk with the iostat command (iostat -d -k 10) I find 4 times more (800 Kb/s) and a lot of read Kb/s (100 kB/s). Is this normal or is there some tunning to do ? During this time, the iowait rate is very high (30%). Is this normal or is there some tunning to do ?
View Replies !
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.
View Replies !
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.)
View Replies !
|
TRACKER
