Re: mysql naming convention

that I agree with Douglas Sims that what is most readable is best. select user_email, user_address, user_postcode from users where user_id = %d; # Hmm select email, address, postcode from users where user_id = %d; # Less typing and more readable. James Harvard -- MySQL General Mailing List

Re: mysql naming convention

suppose it just proves that, when it comes to coding practices, Your Mileage *Will* Vary. :-) James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL lock tables - bug or not?

Thanks for your reply. I repeated your test with the same results on 4.1.21 (database in question is on 4.1). I'll give the ISP another kick and see what they have to say. So there's no other reason why an ISP might not want to grant LOCK TABLES in a shared hosting environment? Thanks, James

MySQL lock tables - bug or not?

in the manual as a side effect of granting 'lock tables' permissions. Does anyone know if it is a bug or not? Does anyone know whether LOCK TABLES really is a security risk in a shared server / multi-user environment? TIA, James Harvard -- MySQL General Mailing List For list archives: http

Re: MySQL 5.0.22 and show columns bug?

OK, fair enough. In that case I would think that filing a report on bugs.mysql.com would be your best way forward. At 8:32 am + 7/7/06, SciBit MySQL Team wrote: While you are not wrong, James, is the length member suppose to denote the maximum length of data contained in result's specified

Re: MySQL 5.0.22 and show columns bug?

Although I know nothing about C I imagine this is because the 'type' column can contain all the possible values from an ENUM or SET field. James Harvard At 10:30 am + 6/7/06, SciBit MySQL Team wrote: Since a couple of recent stable versions back (and more recently, MySQL 5.0.22), MySQL has

RE: New to the group

. James Harvard Ok, so you don't want info on databases, but on which language to use to build a web site? You must sit down and determine what the site will be used for and what features you *must have* in your website. Find a website out there that has the features and style you're

Re: MySQL Clustering

the article http://dev.mysql.com/tech-resources/articles/advanced-mysql-replication.html HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How do I get off this list that I do not remember joining in the first place!!!!

At 11:55 am +1200 26/5/06, Phil Robbins wrote: I've read the notice AND tried to unsubscribe TWICE. I still get the mail. http://lists.mysql.com/troubleshoot.php HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http

Re: Fulltext search for term 'c++'

I think the '+' will not get into the index in the first place. So, there's probably no way to get a search to use the index. However if you want a consistant query format for your search you could probably get the correct result by wrapping the search term in double quotes: select f1 from t1

Re: Determine version of *.frm, *.MYD and *.MYI

I know it a 4.1... But as I compiled it my self it is not so easy to figure it out And it might take a while to trial-n-error all 4.1.x You should be able to see from the change log / version history pages in the manual which minor version releases introduced incompatible changes to the

Re: Fulltext 3 letter words

on any tables with a full-text index. Also, note the warning about using myisamchk further down the manual page. HTH James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Problems with Mysql 5 and Visual Basic 5

I think you will need to give the list some more information! What error message do you get? Is it an error when running SQL queries, or a problem connecting to the database? James Harvard At 9:22 am -0300 24/4/06, Gabriel Mahiques wrote: I migrated to mysql 5 but the applications with Visual

Re: How to Verify Replication Status?

Never used it, but this might help: http://dev.mysql.com/doc/refman/5.0/en/checksum-table.html James Harvard At 5:00 pm -0700 19/4/06, Robinson, Eric wrote: I have master-slave replication working fine. However, I worry about the possibility of the master and slave accidentally getting out

Re: Table Size

that was introduced in v5. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: drop table that doesn't exist

Not a db setting - the DROP statement itself. http://dev.mysql.com/doc/refman/5.0/en/drop-table.html HTH, James Harvard At 1:56 pm -0700 21/3/06, ChadDavis wrote: I'm runnning a script that creates a few tables. I have line that drops the tables before the creation of the tables just in case

Re: Accountability with MySQL

But (in at least some situations) is not appropriate to record that you know that you don't have a value? I think the words of Donald Rumsfeld are appropriate here: There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things that we

Re: Accountability with MySQL

with no matching rows. James Harvard At 11:27 am + 16/3/06, [EMAIL PROTECTED] wrote: As someone totally unread in the theory of databases, that seems unduly puritanical. I assume that what Date would propose is that you have another table (related by master key) in which, if you do not know something

Re: Accountability with MySQL

. It's not often you can say that two people are quite literally arguing about nothing! James Harvard (... being flippant because the actual arguments started going over my head about half-a-dozen posts ago...) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql

Re: Very large from

) James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Accountability with MySQL

) *is* the same. So you're both right. Sort of. :-) James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Inner join with left join

At 5:08 pm -0800 22/2/06, Scott Haneda wrote: I think we are close, thanks ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON conditions SELECT p.id, p.prod_name, sum(oi.quantity) as qty FROM Products p LEFT JOIN orders as o ON (p.id = oi.product_id) Maybe this is

Re: Query Complexity Limit Question [Newbie Question]

the system if you are not careful. Yes indeed. You need to think carefully about what indices you need on your tables. As has already been said, EXPLAIN SELECT is your friend! James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http

Re: Unclear about key_len in EXPLAIN output

between a multi-part key and key_len is. From what the manual says I initially assumed that key_len simply represented the number off key parts used. I don't think a manual suffers from things being spelt out more clearly! James Harvard -- MySQL General Mailing List For list archives: http

Re: Unclear about key_len in EXPLAIN output

'That other statement' that I quoted is actually just part of the paragraph from the manual that you quoted! My point was that it doesn't say what units the key length is given in or explain _how_ to determine the number of parts used. However the more I think about it the more I see that it

Unclear about key_len in EXPLAIN output

, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Differences between numbers of rows in tables

number of rows someone could still add another 20 rows in the second after you run SHOW TABLE STATUS. James Harvard At 5:54 pm -0700 1/2/06, Dan Trainor wrote: So, now this has made me think here. If 'SHOW TABLE STATUS' only shows an estimate of the number of rows contained in a table, how

Re: Sudden Mysql Crashes - table damage?

First of all I would check for file system corruption. IIRC there is a command line tool for repairing MyISAM tables, so you can use that without needing the MySQL database server. I'm sure there are full details in the manual: http://dev.mysql.com/doc/refman/5.0/en/ Good luck! James Harvard

Re: Support between MySQL and PHP

MySQL versions = 4.1 use a new, more secure authentication protocol. Probably the version of PHP you are using does not support it. Ah, here it is: http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html HTH, James Harvard At 1:18 pm -0600 29/1/06, Philip R. Thompson wrote: I just

Re: DB Tables on separate hardisks

HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Troubles installing MySQL5 via Darwin Ports

cannot be read/written to), then download the appropriate installer from mysql.com and let the list know what problems you encounter with that. Good luck, James Harvard On Jan 24, 2006, at 5:13 AM, James Harvard wrote: Is there a reason why you're using Darwin Ports and not the installer

Re: UK Postcodes

which can only be hindering UK businesses from developing localised on-line services. /rant James Harvard At 4:00 pm -0500 24/1/06, Rhino wrote: You'd think that the people who run the post office in the UK - British Telecom?? - would have had a number of enquiries from people who wanted to match

Re: simple load query

Your FIELDS clause is not in the right place: http://dev.mysql.com/doc/refman/5.0/en/load-data.html HTH, James Harvard At 7:06 pm -0500 17/1/06, kalin mintchev wrote: whats the problem with this: load data infile 'stores.txt' into table useyourcash_sports_us (chain, store, address1, address2

Re: Query optimization

It might be a good idea if you could post the results of an EXPLAIN SELECT ... for a fast query and a slow query along with their actual SQL statements. James Harvard At 11:37 am -0800 12/1/06, Tripp Bishop wrote: I've got a question regarding optimizing a query. -- MySQL General Mailing List

Re: Geographical advice

. Therefore you search WHERE x BETWEEN 3 AND 9 AND y BETWEEN 5 AND 11. I'm not certain but I think MySQL should be able to used a combined index of (x,y) for that. As you probably know you can use EXPLAIN SELECT to check whether MySQL is using an index. HTH, James Harvard At 12:01 pm + 10/1/06

Re: query with vars

You should be able to join the tables like this: select p1.id /* and other cols */ from people1 p1 inner join people2 p2 on p1.Lname = p2.Lname and left(p1.Fname, 3) = left(p2.Fname, 3) group by p1.id; HTH, James Harvard I have two independently built tables of people. I am trying to match

Re: SELECT DISTINCT uses index but is still slow

; TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SELECT help.

this though, if your tables are very large. This might work too: select c_name, count(t.id) as t_count, max(t.date) as t_latest from customers c inner join transactions t on c.c_no = t.c_no group by c.c_no having t_count 4 and t_latest '2005-06-05'; HTH, James Harvard At 7:29 am -0800 5/1/06

Re: Strange insert

I assume you did not intend to post to this list, but if you did then this is a problem with your application code, not with MySQL, so I'm afraid this list is not the best place to ask. James Harvard At 11:19 am -0600 5/1/06, Ngim wrote: Hi all, I have an one insert statement in my hub page

Re: join question

http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html I'd translate it to your example, but it's bed-time here in England! HTH, James Harvard At 11:42 pm + 5/1/06, Terry Spencer wrote: I have a question for clearer brains than mine. I would like to join two tables

SELECT DISTINCT uses index but is still slow

bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com

Inconsistent rows returned examined in slow query log

queries were not dealt with by the query cache. Any ideas? Thanks, James Harvard # Time: 060103 9:45:12 # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 86 Lock_time: 0 Rows_sent: 12 Rows_examined: 6733255 select sql_cache dates.date_month from data_gb_e data inner join

RE: Changing types on the fly in select queries?

of my head if that would be able to use an index on (year,period,week) though. Anyone? Good luck, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Do I need to tweak my server variables for this SELECT statement?

, of course, but my-huge.cnf is a better starting point than my.cnf. James Harvard At 6:45 am -0800 23/12/05, Grant Giddens wrote: I think that the reason the original query is so slow is that I don't have enough RAM allocated to mysql. When the original query takes place, I see a process

Re: Strange behavior with integer unsigned type...

), and IIRC MySQL uses 64 bit maths. FWIW my preferred web app middleware - Lasso - does the same thing (only with signed 64 bit numbers). HTH, James Harvard At 11:17 pm +0200 23/12/05, Gleb Paharenko wrote: On both 4.1.16 and 5.0.17 I've got the same results, however not 2^32

Re: Reporting tools for summary data

, they will in ten days time) various trade-flow reports from their web site, so it's all web-based. I use Lasso http://www.omnipilot.com/ as my middleware. Regards, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com

Re: Do I need to tweak my server variables for this SELECT statement?

- might be useful if you continue to have trouble. HTH good luck, James Harvard For the first time, I'm working with a really large database. I have 1 SQL statement that brings my server to it's knees. This setup is currently on my home development PC, and not in production. The server

Re: Do I need to tweak my server variables for this SELECT statement?

That's why I suggested simply using the supplied my-huge.cnf config file, because I assume it was contructed by some MySQL uber-guru who does know how much RAM to allocate to each variable! James Harvard Like I mentioned before, I am tweaking the .cnf files by blind trial and error. I

Re: Reason for Auto-increment primary keys?

worried about shaving 40 MB off a 2 GB file, because 40 MB _was_ my hard disc! So presumably as the years go by increasing processor power and storage speed size will mean we will no longer have to compromise on purity of db design to get acceptable performance. OK, now I'm just rambling. James

Are primary keys essential?

, that is - sorry! TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Are primary keys essential?

destination country, trade commodity category etc., but they are _not_ themselves referenced by any other table. Therefore I have not yet found, nor do I envisage finding, any use for an arbitrary auto_increment primary key. So why would/might I need a PK at all? TIA, James Harvard -- MySQL

Re: Are primary keys essential?

In hindsight my thread title was misleading - sorry. Should have been are primary keys _always_ essential?. JH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to avoid sorting sorted tables ?

it with the last time the table was ordered. HTH, James Harvard At 9:29 am + 19/12/05, C.R.Vegelin wrote: Now I use: ALTER TABLE t ORDER BY a, b, c, d, e, f; This works fine, but takes about 13 minutes for 6 million rows. Without inserts, deletes or other sorts, this PK order remains intact

Re: fulltext search

hypenated words. ... MATCH (col_name) AGAINST ('s-au' IN BOOLEAN MODE) ... HTH, James Harvard At 5:26 pm +0200 18/12/05, Octavian Rasnita wrote: Please tell me how can I configure MySQL 5 in order to be able to search (using fulltext indexes) for combined words like s-au. -- MySQL General Mailing

Re: How to Square a number?

Maybe I'm missing something, but can't you just multiply the number by itself? select (3 * 3); select (int_col * int_col) as squared from table_name; James H At 5:36 pm -0600 18/12/05, mos wrote: How do I square a number in MySQL 4.1? I thought it would be something simple like: select 3**2

Re: is there any BEEP command ?

Well it works on Mac OS X! While there is no beep command, you can possibly get it to beep by selecting the character code for a terminal beep (this is what the mysql client uses internally) Try out: SELECT char(7); -- MySQL General Mailing List For list archives:

Re: Transactions (not rolling back on error)

Hi Cory - nice to see a fellow Lasso user here! I've not use transactions myself but I think you might be having a problem with autocommit. http://dev.mysql.com/doc/refman/5.0/en/commit.html HTH, James Harvard At 12:44 am -0700 15/12/05, Cory @ SkyVantage wrote: I have a transaction

Re: [OT-ish] Hardware for MySQL server

Thanks for all the feedback on this. Is there any received wisdom on whether 1 dual core processor is better than 2 'normal' processors? Also, is there any advantage to SCSI over SATA? TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql

[OT-ish] Hardware for MySQL server

brands available in the UK, or UK based companies that will build servers, supporting 3 discs (2 RAID 1 for the OS)? Many thanks, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]