HELP! Select queries for tables that has fields with # characters
I am trying to query a table that has field names with # characters in them. For example a table emp_earn has a field called FILE# I need to do a query where FILE# = 1332, but anything I try errors out. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: HELP! Select queries for tables that has fields with # characters
select * from emp where `file#` = 1332; returns ERROR 1054: Unknown column 'file' in 'where clause' -Original Message- From: Bernard Clement [mailto:[EMAIL PROTECTED] Sent: Thursday, January 29, 2004 1:19 PM To: Howell, Scott; Mysql (E-mail) Subject: Re: HELP! Select queries for tables that has fields with # characters Hello Howell, See URL: http://www.mysql.com/doc/en/Legal_names.html for the solution. Basically enclose FILE# with `, e.g. where `FILE#` = 1332 Bernard On Thursday 29 January 2004 12:55, Howell, Scott wrote: I am trying to query a table that has field names with # characters in them. For example a table emp_earn has a field called FILE# I need to do a query where FILE# = 1332, but anything I try errors out. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query is running slow that was running fast last week.
This query below is running REALLY slow. The indexes and EXPLAIN result are below.
Last week this query was running in 20 sec, now it takes so long (20 mins). No I
reloaded all the data from the oe-invoice table over the weekend. as I do every
weekend. I have noticed that sometimes the query performance to into the weeds for no
aparent reason. I did an myisamchk --recovery on the v7_oe-invoice.MYI file, but did
not help.
I noticed it has a NULL on all of the cardinality fields in the show index command.
Does this mead anything?
SELECT
v7_oe_invoice1.`cust-num`, v7_oe_invoice1.`cust-site`, v7_oe_invoice1.`whs-num`,
v7_oe_invoice1.`tax-code`, v7_oe_invoice1.`post-date`, v7_oe_invoice1.`inv-merch`,
v7_cust1.`cust-job-num`, v7_cust1.`cust-name`, v7_cust1.`cust-transfer`,
v7_tax1.`tax-desc`
FROM
`v7_oe-invoice` v7_oe_invoice1,
`v7_cust` v7_cust1,
`v7_tax` v7_tax1
WHERE
v7_oe_invoice1.`cust-num` = v7_cust1.`cust-num` AND
v7_oe_invoice1.`tax-code` = v7_tax1.`tax-code` AND
v7_cust1.`cust-transfer` = 0 AND
v7_cust1.`cust-job-num` = 0 AND
v7_oe_invoice1.`post-date` = {ts '2003-12-01 00:00:00.00'} AND
v7_oe_invoice1.`post-date` {ts '2003-12-22 00:00:01.00'} AND
v7_oe_invoice1.`whs-num` = '8'
ORDER BY
v7_oe_invoice1.`tax-code` ASC,
v7_oe_invoice1.`cust-num` ASC
| Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
| v7_oe-invoice | 1 | idx-oei-oet |1 | oe-tran-num | A
|NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-csn |1 | cust-num | A
|NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-slc |1 | slm-code | A
|NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-cjn |1 | cust-job-num | A
|NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-csi |1 | cust-site| A
|NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-pod |1 | post-date| A
|NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-whn |1 | whs-num | A
|NULL | NULL | NULL | |
+---++-+--+--+--
-+-+--++-+
and the Explain
+
| v7_cust1 | ref | idx-cus-csn,idx-cus-cjn,idx-cus-cut | idx-cus-cjn |
5 | const | 14818 | where used; Using temporary; Using filesort
|
| v7_oe_invoice1 | ref | idx-oei-csn,idx-oei-pod,idx-oei-whn | idx-oei-csn |
9 | v7_cust1.cust-num |10 | where used
|
| v7_tax1| ALL | NULL| NULL|
NULL | NULL | 258 | where used
|
++--+-+-+---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Perl/mysql question
Has anyone written a perl script to get records from another database and write it to a file on mysql's LOAD DATA INFILE format? I just need some pointers in perl so a LOAD DATA INFILE will be read correctly. For example, a perl script: Select * from emp from a Progress database Will write to a text file called emp.txt in the LOAD DATA INFILE format, Then, I can to a direct LOAD DATA INFILE into my mysql database. I tried doing direct select/inserts in perl but found dumping to a text file and doing a LOAD DATA to be MUCH faster. Am I missing something here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Extraction/Load program needed
I am looking for a simple extraction/load program to load data daily from a Progress 8.3B database via ODBC to a mysql 3.23.57 database. It does not necessarily need to do any data-type transforms. It need to run on Windows 2000 and be able to run it as a service, or as a cron task if running under Unix. Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
