Large Like Queries
Hi, I was just wondering if anyone has any tricks for speeding up huge queries that require a LIKE comparison, and also a wild card at the front and back of the search term. For example: SELECT my_id FROM my_table WHERE my_value LIKE '%ABCDEFG%' LIMIT 50; I know that in a query such as this, mysql does not utilize indexing, so I was just curious how some others have attempted to speed up this kind of thing up Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Collective Row Concatenation
Hi, I am having a bit of a problem with my MySQL database, and I am hoping someone can help me out. I have a table, that currently contains 350 million rows, all of which basically contain a String (VARCHAR) of size 60 characters or less, and a bunch of other identifiers. These 350 million chunks, if combined together properly, form about 10 million unique strings of varying length. What I am curious about is whether there is a way for me to put these chunks together with a query. So if my table looks like this: mytable -- | id | order |characters | -- 1 1 abcdefghijklmno 1 2 gadrcdassdasds 1 3 adfasdfasdffdwef 2 1 asdfeereqdfqweq 2 2 dsfeqerqerqwerqw... 3 1 qweqeotrmfnrofod... What I would like to do is something like: SELECT COMBINE(characters) as charcter_set FROM mytable WHERE id = 1 ORDER BY order asc; I know that I could simply pull the results into a temporary table, and then use CONCAT or CONCAT_WS to combine them manually, but I would really like to be able to combine the results on the fly, as it takes a really long time to perform all the SQL statements I need on the 350 million row table. Maybe I am thinking about this problem the wrong way, but if anyone has any suggestions, please let me know. Thanks in advance, Chris Stark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Sorting UK Postcodes (WAS Sorting Results)
Hi, Could you seperate the letters into one column and the numbers in another... Then you could group by the Alphabetical column, and order by the numerical column... Regards, Chris Stark -Original Message- From: Alliax [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 12, 2002 4:53 PM To: MySQL List Subject: RE: Sorting UK Postcodes (WAS Sorting Results) Sorry, I forgot to say that postcode can be one or 2 letters in front of the numbers. -Message d'origine- They can be E1,E2,..,E12,E13 Order by name would do: E1,E10,E11,E12,E13,E2,E3,E4,... how can I get with a simple ORDER BY query E1,E2,E3,E4,E5,... ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Grant User Persmissions
Hello, Is there any way to grant a user only SELECT permissions on a set of tables in MySQL, but to also allow them to have CREATE, DROP, and INSERT on the Database itself?? What I want is a database where the users cannot modify the core tables, but I want them to be able to CREATE and DROP temporary tables that they have created?!? Any help would be great.. Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Extremely Large Numbers for MySQL column
Hello, I have a question, and hopefully someone out there can help me out. I have a lot of really large numbers (or should I say small, large in size ) that I need to store in my MySQL database. Numbers like: 2.8E-50 or 0.049zeros028 Anyway, I need to store these numbers in such a way that I can do Greater Than or Less Than calculations in the Where clause and pull out really small ones...Such as: SELECT * FROM results WHERE result_value 2.8E-50 Anyway, I cannot seem to find a column type that is large enough to store this kind of number. Is there any way to force MySQL to store numbers in Exponential Form or is there maybe a variable I can tweak to allow it to store larger numbers?? Any advice would be a huge help! Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Disappearing of New Inserts to MyISAM table
Hi Everyone, I am in kind of a jam, and I could use some help. Hopefully someone out there has encountered something similar to this. I have a MySQL database, and I am using Java to make the connection to the database. At one point in the execution of my app, I need to issue an ALTER TABLE command to add a column to one of my tables. The command issues successfully, however, if I login to the Server itself, and issue a DESCRIBE TABLE command, the newly added column is not among the list of columns. When I use WINMYSQLADMIN to monitor the database, it to shows the addition of the new column. Only when I login to the server itself does the column not show. It is strange because the command issues flawlessly, and I get no SQL or Java Errors...I have checked my permissions on the tables, as well as on the server itself, and I have full rights to do anything I please on the database. Please help me out with any advice you may have on this issue, anything would be a huge help. Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Disappearing of New Inserts to MyISAM table
Yes, it is connecting to the same server each time, it is very strange. I have tried moving the entire database to a different server, and I seem to encounter the same problem as well. It is almost as if the tables themselves have an issue which is causing the problem. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 11:49 PM To: Chris Stark; [EMAIL PROTECTED] Subject: Re: Disappearing of New Inserts to MyISAM table At 23:05 -0500 3/14/02, Chris Stark wrote: Hi Everyone, I am in kind of a jam, and I could use some help. Hopefully someone out there has encountered something similar to this. I have a MySQL database, and I am using Java to make the connection to the database. At one point in the execution of my app, I need to issue an ALTER TABLE command to add a column to one of my tables. The command issues successfully, however, if I login to the Server itself, and issue a DESCRIBE TABLE command, the newly added column is not among the list of columns. When I use WINMYSQLADMIN to monitor the database, it to shows the addition of the new column. Only when I login to the server itself does the column not show. It is strange because the command issues flawlessly, and I get no SQL or Java Errors...I have checked my permissions on the tables, as well as on the server itself, and I have full rights to do anything I please on the database. Please help me out with any advice you may have on this issue, anything would be a huge help. Bizarre. Perhaps this is a silly question, but are you sure you're actually connecting to the same server each time? Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Begin work
This statement signals the successful end-of-transaction. A transaction is a group of SQL statements whose changes are logically connected and can be made permanent or undone as a unit. All updates made during this transaction are made permanent, all row locks are released, and active SQL SELECT statements are closed. I got that quote from Oracle... I think it is the same as the COMMIT command in MySQL!! Chris -Original Message- From: Manish Mehta [mailto:[EMAIL PROTECTED]] Sent: Friday, March 15, 2002 12:07 AM To: mysql Subject: Begin work Hi Any body tells me what the use of Begin work in SQL . Manish Mehta - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Begin work
You usually use begin work before a bunch of inserts or deletes from a database i.e. BEGIN WORK INSERT INSERT DELETE INSERT . . . COMMIT WORK OR ROLLBACK WORK It is the start of a transaction and it allows you to guarantee some of the ACID properties of your database... Chris -Original Message- From: Manish Mehta [mailto:[EMAIL PROTECTED]] Sent: Friday, March 15, 2002 12:33 AM To: Chris Stark Subject: Re: Begin work Where Begin work Works process used mainly. Manish - Original Message - From: Chris Stark [EMAIL PROTECTED] To: Manish Mehta [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Friday, March 15, 2002 10:42 AM Subject: RE: Begin work This statement signals the successful end-of-transaction. A transaction is a group of SQL statements whose changes are logically connected and can be made permanent or undone as a unit. All updates made during this transaction are made permanent, all row locks are released, and active SQL SELECT statements are closed. I got that quote from Oracle... I think it is the same as the COMMIT command in MySQL!! Chris -Original Message- From: Manish Mehta [mailto:[EMAIL PROTECTED]] Sent: Friday, March 15, 2002 12:07 AM To: mysql Subject: Begin work Hi Any body tells me what the use of Begin work in SQL . Manish Mehta - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: speed up MySQL Selections
Hello, Depending on what types of values are in your table, and the types of selects that you are going to perform, you will most likely notice a speed change if you were to INDEX some of your attributes...Just look up INDEX in the mysql manual... I notice the largest improvement when I INDEX on an attribute that I am constantly using in a WHERE clause in my SELECT statement...It really helps MySQL when it goes to test the comparisons... Hope this helps... Chris -Original Message- From: Cael Mahold [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 09, 2001 4:20 PM To: [EMAIL PROTECTED] Subject: speed up MySQL Selections Hello, i have a question. I will have to set up a MySQL Database which will have a hughe amount of entries in a table. I did some tests and my select statements would need lot of time. I just wanna ask if there are some generally ways to speed up performance of mysql statements? thanx in advance bye Rüdi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Packet is Larger than max_allowed_packet from server configuration of 65536 bytes
Hi, I am having a problem with the configuration of the mysql server or client. I have a very large query that I need to send to MySQL from Java. The query has approximately 5000 individual items in the where clause. (i.e. WHERE i!='AP' AND i != 'J3' AND...to 5000). When I try to execute the Query I get the error: Packet is Larger than max_allowed_packet from server configuration of 65536 bytes I read the MySQL documentation and increased my max_allowed_packet to 24M on both the client and the Server in the my.cnf, but I still get the same errorIs there another variable I need to increase, because no matter what I change the value of max_allowed_packet to, I still get the 65535 bytes error?? Please help!! Thanks, Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
