innodb performance issues

Hi, A few days ago i posted a quaestion about performace, I now have a little more info, hopefully someone can help. I have a table, tblShoppingCart with 3 fields, cartUid (int 11 auto increment) userUid (int 11, indexed) strCartHash (varchar 32) The table is innodb Nomally my server load

Re: SQL 'clustering' query?

On Thu, 14 Jul 2005, Peter Brawley wrote: Dan, Hi, I remember reading about an SQL query type which did something like select all 'aircraft hangers' which contained exactly (or at least) some given set of aircraft. Or did it select the list of pilots qualified to fly all the airplanes in the

Re: mysql 4.0 to 4.1 migration and charset problems

Hello. It is not clear from you message what's the problem. From your calculations I can see that the length of int field in a flat file row (BTW please send a piece of your file) is different in mysqldump programs with different versions? How is the length of integer fields related to

Re: mysql forgets user passwords

Hello. PASSWORD '3446cb892d3dffdd' WITH GRANT OPTION | You're using passwords in old format. Is it possible that problem somehow related to this. Are you connecting using mysql command line client? What version it is? Chris Fonnesbeck [EMAIL PROTECTED] wrote: On 7/14/05, Gleb

Re: select based letter

Hello. What about: select topics from topics where topics like 'M%'; Sebastian [EMAIL PROTECTED] wrote: i have a text field column and i want to select the rows based on the first letter in this column, eg: - | topics | - Motherboard Hard Drives

BOOLEAN Vs NON-Boolean

Hello, I've just upgraded to 4.1 for boolean search facilities, but I think I may lose more than I 've gained. I just really need the +keyword1 keyword2 functionality so all results must contain keyword1, but I then need to order by relevance score which I've lost. Is it worth performing two

Setting default character_set_results

Hi, I have a problem with character_set_results on MySQL 4.1. I have this in my my.cnf: [mysqld] default-character-set=ujis default-collation=ujis_japanese_ci [client] default-character-set=ujis [mysql] default-character-set=ujis If I connect from mysql client, and do 'show variables' I get

VARCHAR changes to INT - UNION

Hi list Im new to the list so i hope it's the right place for my post. For a projekt i am combining different tables. Everything seems to work, but in some rows the Kode field VARCHAR(10) ends up as INT. I will try to explain with an example. Table1 46 47 48 67 Table2 BBEGYNDER BVILDIVAND

Charset and collation question

Hi all, Which charset and collatio would be the most suitable for a french / english system? Thanks Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: VARCHAR changes to INT - UNION

Dennis Duggen wrote: Hi list Im new to the list so i hope it's the right place for my post. For a projekt i am combining different tables. Everything seems to work, but in some rows the Kode field VARCHAR(10) ends up as INT. I will try to explain with an example. Table1 46 47 48 67 Table2

Re: VARCHAR changes to INT - UNION

Eugene Kosov wrote: Dennis Duggen wrote: Hi list Im new to the list so i hope it's the right place for my post. For a projekt i am combining different tables. Everything seems to work, but in some rows the Kode field VARCHAR(10) ends up as INT. I will try to explain with an example. Table1

Re: DROP FUNCTION doesn't work

Hello. Please check, if weird behavior remains on the latest release (4.1.12). Try to run FLUSH PRIVILEGES after 'drop function' statement and cast a look if mysql.func table is clear. Do you use xxx_deinit in your udf? I mean, is it possible that your function has some problem during

Re: Setting default character_set_results

Hello. Please check, if putting of these lines into my.cnf solves the problem: [dbdmysql] default-character-set = ujis I've found this suggestion at: http://lists.mysql.com/perl/2286 Batara Kesuma [EMAIL PROTECTED] wrote: Hi, I have a problem with character_set_results

Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?

Hello. If all your data has the same encoding it shouldn't be a problem. Just check that character_set_server and character_set_database variables have 'utf8' value for each database in which you're going to import data. Than invokes mysql with --default-character-set =

Re: Charset and collation question

Hello. Similar question has been asked already. See: http://lists.mysql.com/mysql/179154 Stephane Savage [EMAIL PROTECTED] wrote: Hi all, Which charset and collatio would be the most suitable for a french / english system? Thanks Stephane -- For technical

Stored Procedure in MySQL 5.x

How effective is Stored Procedure in MySQL 5.x? -- Power to people, Linux is here.

Group By query optimization

Hi All, I have a mysql query which takes 8 seconds to run ona dual xeon 2.4, 3Gig ram box, SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM gamesessions AS gamesessions WHERE valid=1 AND sessiontype IN (1,2) GROUP BY gamename; Explain

Re: innodb performance issues

Tony, You said that you copied the my.cnf file from huge.cnf - not sure what version you are using (I missed your original post), but the my-huge.cnf in mysql 4.0.24 is for MyISAM. You should have used my-innodb-heavy-4G.cnf as the starting point for an InnoDB system. The my-huge.cnf

Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?

I've got some years-old MySQL databases mostly in 4.0, but one server running 3.23 that are all using the default encoding. I want to update all their data to 4.1 with UTF-8 encoding. Anyone done this kind of dump-and-update?Any advice to share or good URLs you've seen with others' advice

Re: BOOLEAN Vs NON-Boolean

you are doing it correctly.. you should be able to sort by relevance. it know you can sort like that because my search engine does using boolean. Lee Denny wrote: Hello, I've just upgraded to 4.1 for boolean search facilities, but I think I may lose more than I 've gained. I just really

Re: Stored Procedure in MySQL 5.x

How effective? Very effective. Actually, I am testing it, and once I overcame a few syntax issues, it's working great. Procedures and functions both. I havent tested triggers yet. But so far I am using a mini-blog thing on my web site with them and I am loving the ease of Mysql combined

Re: innodb performance issues

Hi David, On Fri, 2005-07-15 at 10:25 -0700, David Griffiths wrote: Tony, - not sure what version you are using 4.1.11. Server is a duel xeon machine with 4gb or ram running mysql and apache webserver and not much else. You should have used my-innodb-heavy-4G.cnf as the starting point

Re: mysql forgets user passwords

On 7/14/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. PASSWORD '3446cb892d3dffdd' WITH GRANT OPTION | You're using passwords in old format. Is it possible that problem somehow related to this. Are you connecting using mysql command line client? What version it is? I

Create an Innodb database ?

I am running MySql 3.23.53. I am developing a multi-user application in delphi using the Zeoslib controls. Since it is going to be a multi-user application, should I use Innodb ? If so, are there any good tutorial/references for the Innodb ? thanks, Darryl -- MySQL General Mailing List For

Re: Create an Innodb database ?

Darryl Hoar wrote: I am running MySql 3.23.53. I am developing a multi-user application in delphi using the Zeoslib controls. Since it is going to be a multi-user application, should I use Innodb ? If so, are there any good tutorial/references for the Innodb ? thanks, Darryl Hi Darryl,

Re: innodb performance issues

Tony, Your my.cnf file is set up for MyISAM, not InnoDB. MySQL allows you to allocate memory and resources to any and all storage engines. Yours is set up to give lots of resources to MyISAM, and none to InnoDB. Reducing MyISAM key_buffer = 384M - this is way too much - I'd set to to 2-16

Re: Create an Innodb database ?

Danny Stolle [EMAIL PROTECTED] writes: So now also my question: When to use innodb instead of myisam? What performace advantages does this engine have? Well, if you want to have transactions, you must use innodb... -- MySQL General Mailing List For list archives:

Re: Create an Innodb database ?

At 03:09 PM 7/15/2005, you wrote: Darryl Hoar wrote: I am running MySql 3.23.53. I am developing a multi-user application in delphi using the Zeoslib controls. Since it is going to be a multi-user application, should I use Innodb ? If so, are there any good tutorial/references for the Innodb

slow count(1) behavior with large tables

i'm trying to run this query: SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE b.BaseType = 0 AND m.BoardID = b.BoardID; MSGS has 9.5 million rows, and is indexed on BoardID MBOARD has 69K rows and is indexed on BaseType EXPLAIN shows: mysql explain SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE

Re: slow count(1) behavior with large tables

Hi, You're doing a join on 'BoardID' on the tables MSGS and MBOARD. Is the BoardID field indexed on the MSGS table too? If not then that may be your problem. Cheers, Andrew On 15/7/05 23:31, Jon Drukman [EMAIL PROTECTED] wrote: i'm trying to run this query: SELECT COUNT(1) FROM MSGS m,

Re: Group By query optimization

Hi, Put indexes on 'valid' and 'sessiontype' and all will be good. Cheers, Andrew On 15/7/05 18:26, Kishore Jalleda [EMAIL PROTECTED] wrote: Hi All, I have a mysql query which takes 8 seconds to run ona dual xeon 2.4, 3Gig ram box, SELECT gamename, MAX(score) AS score,

Re: slow count(1) behavior with large tables

Andrew Braithwaite wrote: Hi, You're doing a join on 'BoardID' on the tables MSGS and MBOARD. Is the BoardID field indexed on the MSGS table too? If not then that may be your problem. MSGS.BoardID is indexed, and the EXPLAIN output I included in the original message shows that it is

Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?

On 7/15/05, Bruce Dembecki [EMAIL PROTECTED] wrote: This process has worked for us taking our latin1 4.0 databases and turning them into utf8 4.1 databases. UTF8 data we had already put in our 4.0 database despite it's latin1 encoding was correctly exported out of 4.0 and correctly converted

Re: slow count(1) behavior with large tables

Andrew Braithwaite wrote: Hi, You're doing a join on 'BoardID' on the tables MSGS and MBOARD. Is the BoardID field indexed on the MSGS table too? If not then that may be your problem. Cheers, Andrew He said, MSGS ... is indexed on BoardID. Did you look at the EXPLAIN output? The query

Re: slow count(1) behavior with large tables

Sorry, I meant to say is the 'BoardID' field indexed on the MBOARD table too? Cheers, A On 16/7/05 00:01, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, You're doing a join on 'BoardID' on the tables MSGS and MBOARD. Is the BoardID field indexed on the MSGS table too? If not then that

Re: slow count(1) behavior with large tables

Andrew Braithwaite wrote: Sorry, I meant to say is the 'BoardID' field indexed on the MBOARD table too? yes, BoardID is the primary key. BaseType is also indexed. from the EXPLAIN output i can see that mysql is choosing to use BaseType as the index for MBOARD (as we know, mysql can only use

MySQL 5.0.9 build problem

Hello, This is my first post on this list, so forgive me, please, if this is the wrong forum for this question. While trying to build RPMs for 5.0.9-beta, I have run into this error: + make test cd mysql-test; \ ./mysql-test-run ./mysql-test-run --ps-protocol Installing Test Databases

Re: slow count(1) behavior with large tables

Jon Drukman wrote: Andrew Braithwaite wrote: Sorry, I meant to say is the 'BoardID' field indexed on the MBOARD table too? yes, BoardID is the primary key. BaseType is also indexed. from the EXPLAIN output i can see that mysql is choosing to use BaseType as the index for MBOARD (as we

Re: mysql forgets user passwords

Chris Fonnesbeck wrote: Here is the startup script: standard script snipped The grant command was: grant all on *.* to [EMAIL PROTECTED] identified by 'my_password'; Thanks for the help, C. Chris, You only answered one of my questions. I'm hoping to narrow the problem by determining

Query Question

I have the following query which display every Cuisine in the database sorted by the WebsiteName. How can I modify this to get a COUNT of the number of records in each Cuisine in each WebsiteName? SELECT DISTINCT Restaurant.Cuisine, RestaurantWebsites.WebsiteName FROM Restaurant INNER JOIN