Re: LIKE sql optimization
On 12/02/2014 13:16, Morgan Tocker wrote: Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance. In addition to what Morgan writes, then with an index scan you will end up doing a lot of random I/O: even if the index scan itself is one sequential scan (which is not guaranteed) then for each match, it will be necessary to look up the actual row. On the other hand a table scan will generally be more of a sequential read as you already have all the data available for each match. Random I/O is more expensive than sequential I/O - particularly on spinning disks - so in general the optimizer will try to reduce the amount of random I/O. In some cases though, you may see the index scan be preferred. Assume you have a query like: SELECT val FROM table WHERE condition LIKE '%abcd'; and you have an index (condition, val) or (val, condition) then the whole query can be satisfied from the index (it's called a covering index). In that case the index scan is usually preferred over the table scan. For the purpose of using an index to do index lookups to find the matching rows rather than doing either a table or index scan for WHERE clauses like LIKE '%abcd' you can do a couple of things: * Duplicate the column used in the WHERE clause, but reverse the string. That way the above WHERE clause becomes: WHERE condition_revers LIKE 'dcba%' This can use an index as it is a left prefix. * If you always look for around the same number of characters at the end in your WHERE clause, you can create a column with just those last characters, e.g. so the WHERE clause becomes: WHERE condition_suffix = 'abcd' Do however be careful that you ensure you have enough selectivity that way. If for example 90% of the rows ends in 'abcd' an index will not do you much good (unless you are looking for the last 10% of the rows). Best regards, Jesper Krogh MySQL Support
RE: LIKE sql optimization
Done. Thand you very much! Zhigang _ From: Jesper Wisborg Krogh [mailto:my...@wisborg.dk] Sent: Wednesday, February 12, 2014 5:30 PM To: Morgan Tocker; Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: LIKE sql optimization On 12/02/2014 13:16, Morgan Tocker wrote: Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang mailto:zzgang2...@gmail.com zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance. In addition to what Morgan writes, then with an index scan you will end up doing a lot of random I/O: even if the index scan itself is one sequential scan (which is not guaranteed) then for each match, it will be necessary to look up the actual row. On the other hand a table scan will generally be more of a sequential read as you already have all the data available for each match. Random I/O is more expensive than sequential I/O - particularly on spinning disks - so in general the optimizer will try to reduce the amount of random I/O. In some cases though, you may see the index scan be preferred. Assume you have a query like: SELECT val FROM table WHERE condition LIKE '%abcd'; and you have an index (condition, val) or (val, condition) then the whole query can be satisfied from the index (it's called a covering index). In that case the index scan is usually preferred over the table scan. For the purpose of using an index to do index lookups to find the matching rows rather than doing either a table or index scan for WHERE clauses like LIKE '%abcd' you can do a couple of things: * Duplicate the column used in the WHERE clause, but reverse the string. That way the above WHERE clause becomes: WHERE condition_revers LIKE 'dcba%' This can use an index as it is a left prefix. * If you always look for around the same number of characters at the end in your WHERE clause, you can create a column with just those last characters, e.g. so the WHERE clause becomes: WHERE condition_suffix = 'abcd' Do however be careful that you ensure you have enough selectivity that way. If for example 90% of the rows ends in 'abcd' an index will not do you much good (unless you are looking for the last 10% of the rows). Best regards, Jesper Krogh MySQL Support
LIKE sql optimization
For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
Re: LIKE sql optimization
Am 12.02.2014 02:23, schrieb Zhigang Zhang: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan because ‘%abcd’ can't work in case of a index how do you imagine that? read how a index works technically 'abcd%' may work but '%abcd' is impossible independent what engine, this don't work and won't ever work you may have luck with fulltext search (and it's other drawbacks) https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html signature.asc Description: OpenPGP digital signature
Re: LIKE sql optimization
Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like '%abcd'; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
RE: LIKE sql optimization
I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
Re: LIKE sql optimization
*read how a index works technically* On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang zzgang2...@gmail.comwrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like '%abcd'; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang
Re: LIKE sql optimization
because a index is not just a dumb copy of the whole field and you simply can't seek in the middle of it? http://en.wikipedia.org/wiki/B-tree http://mattfleming.com/node/192 Am 12.02.2014 02:48, schrieb Zhigang Zhang: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. signature.asc Description: OpenPGP digital signature
Re: LIKE sql optimization
MySQL can't use index when '%' condition gives even oracle and you can try full-text search 2014-02-12 9:55 GMT+08:00 kitlenv kitl...@gmail.com: *read how a index works technically* On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like '%abcd'; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang -- Phone: +86 1868061 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
Re: LIKE sql optimization
Same reason as why composite index works only if you supply first field or fields .. example index on a,b,c if you have a query : select * from tbl where a = 'whatever' and b = 'something it will use the index .. but a query like this one : select * from tbl where b = 'something' and c = 'something else' won't use the index .. - Just like an index in a book ... 2014-02-11 21:03 GMT-05:00 louis liu yloui...@gmail.com: MySQL can't use index when '%' condition gives even oracle and you can try full-text search 2014-02-12 9:55 GMT+08:00 kitlenv kitl...@gmail.com: *read how a index works technically* On Wed, Feb 12, 2014 at 12:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. zhigang _ From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like '%abcd'; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan. Thanks. Zhigang -- Phone: +86 1868061 Email Gtalk: yloui...@gmail.com Personal Blog: http://www.vmcd.org
RE: LIKE sql optimization
I checked a myisam table index, the index is a copy of the whole field. Zhigang -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Wednesday, February 12, 2014 10:02 AM To: mysql@lists.mysql.com Subject: Re: LIKE sql optimization because a index is not just a dumb copy of the whole field and you simply can't seek in the middle of it? http://en.wikipedia.org/wiki/B-tree http://mattfleming.com/node/192 Am 12.02.2014 02:48, schrieb Zhigang Zhang: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. From: Mathieu Desharnais [mailto:mdesharn...@diffusion.cc] Sent: Wednesday, February 12, 2014 9:41 AM To: Zhigang Zhang; mysql@lists.mysql.com Subject: Re: LIKE sql optimization Sql database doesn't use index in like statement if it starts with % .. like 'abcd%' would work though... To use an index you can store your value using reverse function and index it .. then your like would use the index. 2014-02-11 20:23 GMT-05:00 Zhigang Zhang zzgang2...@gmail.com: For example: Select * from T where col like ‘%abcd’; The table T is myisam table and we created a index on col. As we known, this like sql does not use the index created on col, it confuse me, why? I think in mysiam engine, the index data is smaller, it can use index link list to optimize it so as to reduce the disk scan than to the whole table scan.
Re: LIKE sql optimization
Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: LIKE sql optimization
Thank you very much! Zhigang -Original Message- From: Morgan Tocker [mailto:morgan.toc...@oracle.com] Sent: Wednesday, February 12, 2014 10:16 AM To: Zhigang Zhang Cc: mysql@lists.mysql.com Subject: Re: LIKE sql optimization Hi Zhigang, On Feb 11, 2014, at 8:48 PM, Zhigang Zhang zzgang2...@gmail.com wrote: I want to know the reason, in my opinion, to scan the smaller index data has better performance than to scan the whole table data. I think I understand the question - you are asking why MySQL will not index scan, find matching records, and then look them up rather than table scan? I believe the answer is that there is no way of knowing if 1 row matches, or all rows match. In the worst case (where all rows match), it is much more expensive to traverse between index and data rows for-each-record. So a table scan is a “safe choice / has less variance.=
RE: [Suspected Spam][Characteristics] RE: Slow Response -- What Does This Sound Like to You?
1. MyISAM locks _tables_. That can cause other connections
to be blocked. Solution: switch to InnoDB. Caution: There
are a few caveats when switching; see
https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/
2. As mentioned by Shawn, the Query Cache can be more trouble
than it is worth. However 90 seconds cannot be blamed on the
QC. Still, shrink it or turn it off:
* If frequently writing to tables, turn it off (type=OFF _and_ size=0)
* If less frequently, then decide which queries will benefit,
add SQL_CACHE to them, set type=DEMAND and size=50M (no larger).
3. Meanwhile, try to make that long query more efficient.
Can you show it to us, together with SHOW CREATE TABLE, SHOW
TABLE STATUS, and EXPLAIN ?
Thanks for the feedback, Rick.
There are 1200+ tables in the database, so I don't think you want a SHOW CREATE
TABLE, SHOW TABLE STATUS, EXPLAIN for all of them. :-) The query in question is
always some variation of the following. From looking at this, which table(s)
would you like to see this information for?
# Time: 130507 18:14:26
# User@Host: site150_DbUser[site150_DbUser] @ cognos08.mycharts.md
[192.168.10.85]
# Query_time: 82 Lock_time: 0 Rows_sent: 1 Rows_examined: 914386
select (mod(dayofweek(`Query1`.`Appointment_Date`)+7-1,7)), {fn CONCAT({fn
CONCAT({fn CONCAT(cast(extract(hour from
`Time_Difference_Query`.`Created_Date`) as char(25)), ':')},
cast(extract(minute from `Time_Difference_Query`.`Created_Date`) as
char(25)))}, ':')}, `Time_Difference_Query`.`Created_Date`,
`Query1`.`Appointment_Provider_Name` from (select distinct
`EMR_ENCOUNTER`.`encType` as Encounter_Type , case when
`EMR_ENCOUNTER`.`encLock` = 0 then 'UnLocked' else 'Locked' end as
Chart_Lock_Status , `EMR_ENCOUNTER`.`notesDoneTime` as Notes_Done_Time ,
`EMR_ENCOUNTER`.`dateOut` as Notes_Done_Date , `EMR_ENCOUNTER`.`timeIn` as
Appointments_Checked_In , `EMR_ENCOUNTER`.`timeOut` as
Appointments_Checked_Out , `EMR_ENCOUNTER`.`depTime` as
Appointments_Departure_Time , `EMR_ENCOUNTER`.`arrivedTime` as
Appointments_Arrived_Time , `EMR_ENCOUNTER`.`endTime` as
Appointment_End_Time , `EMR_ENCOUNTER`.`startTime` as
Appointment_Start_Time , `EMR_ENCOUNTER`.`date` as Appointment_Date ,
`EMR_ENCOUNTER`.`encounterID` as Encounter_ID , `EDI_FACILITIES`.`Name` as
Facility_Name , `APPOINTMENT_PROVIDER`.`uid` as Appointment_Provider_ID ,
{fn CONCAT({fn CONCAT({fn CONCAT({fn CONCAT(`APPOINTMENT_PROVIDER`.`ulname`, ',
')}, `APPOINTMENT_PROVIDER`.`ufname`)}, ' ')},
`APPOINTMENT_PROVIDER`.`uminitial`)} as Appointment_Provider_Name from
(`enc` `EMR_ENCOUNTER` LEFT OUTER JOIN `edi_facilities` `EDI_FACILITIES` on
`EMR_ENCOUNTER`.`facilityId` = `EDI_FACILITIES`.`Id`) LEFT OUTER JOIN
(`doctors` `APPOINTMENT_DOCTOR` INNER JOIN `users` `APPOINTMENT_PROVIDER` on
`APPOINTMENT_DOCTOR`.`doctorID` = `APPOINTMENT_PROVIDER`.`uid`) on
`EMR_ENCOUNTER`.`doctorID` = `APPOINTMENT_DOCTOR`.`doctorID` where
`EMR_ENCOUNTER`.`encType` = 2 and `EMR_ENCOUNTER`.`date` between
cast('2011-01-01' as date) and cast('2013-05-07' as date) and
`EMR_ENCOUNTER`.`patientID` 8663 and `EMR_ENCOUNTER`.`VisitType` 'PTDASH'
and `EMR_ENCOUNTER`.`deleteFlag` = 0 and `APPOINTMENT_PROVIDER`.`UserType` = 1
and `APPOINTMENT_PROVIDER`.`delFlag` = 0 and `EDI_FACILITIES`.`DeleteFlag` = 0)
`Query1` LEFT OUTER JOIN (select distinct `Addressed_Query`.`moddate` as
Locked_Date , `Created_Query`.`moddate` as Created_Date ,
`Created_Query`.`encounterid` as encounterid , `Created_Query`.`reason` as
reason , `Created_Query`.`Patient_Name` as Patient_Name from (select
distinct `SQL1`.`moddate` as moddate , `SQL1`.`encounterid` as encounterid
, `SQL1`.`actionflag` as actionflag , `SQL1`.`ufname` as ufname ,
`SQL1`.`ulname` as ulname , `SQL1`.`reason` as reason , {fn CONCAT({fn
CONCAT(`SQL1`.`ulname`, ', ')}, `SQL1`.`ufname`)} as Patient_Name from
(select users.ufname,users.ulname,cast(reason as char(30)) as reason,
telenc_loghist.actionflag,telenc_loghist.encounterid,telenc_loghist.moddate
from telenc_loghist
inner join enc on
enc.encounterid=telenc_loghist.encounterid
inner join users on users.uid=enc.patientid where
actionflag in(0) and enc.date between '2011-01-01' and '2013-05-07') `SQL1`)
`Created_Query` LEFT OUTER JOIN (select distinct `Q2`.`moddate` as moddate ,
`Q2`.`encounterid` as encounterid , `Q2`.`actionflag` as actionflag from
(select
telenc_loghist.actionflag,telenc_loghist.encounterid,telenc_loghist.moddate
from telenc_loghist
inner join enc on
enc.encounterid=telenc_loghist.encounterid where actionflag in(4) and enc.date
between '2011-01-01' and '2013-05-07') `Q2`) `Addressed_Query` on
`Created_Query`.`encounterid` = `Addressed_Query`.`encounterid` where NOT
`Addressed_Query`.`moddate` is null) `Time_Difference_Query` on
`Query1`.`Encounter_ID` = `Time_Difference_Query`.`encounterid` where
`Query1
RE: Slow Response -- What Does This Sound Like to You?
`.`Facility_Name` in ('Fremont Family Care')
and `Query1`.`Appointment_Date` between cast(cast('2011-01-01' as date)
as date)
and cast(cast('2013-05-07' as date)
as date)
and `Query1`.`Appointment_Provider_ID` = 60922;
---
The big problem is
FROM ( SELECT ... ) JOIN ( SELECT ... ) ON ...
Neither of those subqueries has an index, so there will be table scans. The
solution is to CREATE TEMPORARY TABLE ... SELECT for each one, then add an
index.
You SELECT a bunch of rows as Query1, then filter?? Can't you move the
filtering into the subquery??
There is no need for either CAST in cast(cast('2013-05-07' as date) as date);
simply use '2013-05-07'.
What does the {} syntax do??
Contradictory:
where `Query1`.`Appointment_Provider_ID` in (9118, 9119, 60922, 9116,
47495)
and `Query1`.`Appointment_Provider_ID` = 60922;
The IN filter does nothing useful.
I think those changes will make the query run _much_ faster. If not, provide
the SHOW CREATE TABLE for the tables being used here, plus EXPLAIN SELECT.
-Original Message-
From: Robinson, Eric [mailto:eric.robin...@psmnv.com]
Sent: Friday, May 10, 2013 11:36 AM
To: Rick James; Bruce Ferrell; mysql@lists.mysql.com
Subject: RE: [Suspected Spam][Characteristics] RE: Slow Response --
What Does This Sound Like to You?
1. MyISAM locks _tables_. That can cause other connections to be
blocked. Solution: switch to InnoDB. Caution: There are a few
caveats when switching; see
https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/
2. As mentioned by Shawn, the Query Cache can be more trouble than it
is worth. However 90 seconds cannot be blamed on the QC. Still,
shrink it or turn it off:
* If frequently writing to tables, turn it off (type=OFF _and_
size=0)
* If less frequently, then decide which queries will benefit, add
SQL_CACHE to them, set type=DEMAND and size=50M (no larger).
3. Meanwhile, try to make that long query more efficient.
Can you show it to us, together with SHOW CREATE TABLE, SHOW TABLE
STATUS, and EXPLAIN ?
Thanks for the feedback, Rick.
There are 1200+ tables in the database, so I don't think you want a
SHOW CREATE TABLE, SHOW TABLE STATUS, EXPLAIN for all of them. :-) The
query in question is always some variation of the following. From
looking at this, which table(s) would you like to see this information
for?
# Time: 130507 18:14:26
# User@Host: site150_DbUser[site150_DbUser] @ cognos08.mycharts.md
[192.168.10.85] # Query_time: 82 Lock_time: 0 Rows_sent: 1
Rows_examined: 914386 select
(mod(dayofweek(`Query1`.`Appointment_Date`)+7-1,7)), {fn CONCAT({fn
CONCAT({fn CONCAT(cast(extract(hour from
`Time_Difference_Query`.`Created_Date`) as char(25)), ':')},
cast(extract(minute from `Time_Difference_Query`.`Created_Date`) as
char(25)))}, ':')}, `Time_Difference_Query`.`Created_Date`,
`Query1`.`Appointment_Provider_Name` from (select distinct
`EMR_ENCOUNTER`.`encType` as Encounter_Type , case when
`EMR_ENCOUNTER`.`encLock` = 0 then 'UnLocked' else 'Locked' end as
Chart_Lock_Status , `EMR_ENCOUNTER`.`notesDoneTime` as
Notes_Done_Time , `EMR_ENCOUNTER`.`dateOut` as Notes_Done_Date ,
`EMR_ENCOUNTER`.`timeIn` as Appointments_Checked_In ,
`EMR_ENCOUNTER`.`timeOut` as Appointments_Checked_Out ,
`EMR_ENCOUNTER`.`depTime` as Appointments_Departure_Time ,
`EMR_ENCOUNTER`.`arrivedTime` as Appointments_Arrived_Time ,
`EMR_ENCOUNTER`.`endTime` as Appointment_End_Time ,
`EMR_ENCOUNTER`.`startTime` as Appointment_Start_Time ,
`EMR_ENCOUNTER`.`date` as Appointment_Date ,
`EMR_ENCOUNTER`.`encounterID` as Encounter_ID ,
`EDI_FACILITIES`.`Name` as Facility_Name ,
`APPOINTMENT_PROVIDER`.`uid` as Appointment_Provider_ID , {fn
CONCAT({fn CONCAT({fn CONCAT({fn
CONCAT(`APPOINTMENT_PROVIDER`.`ulname`, ', ')},
`APPOINTMENT_PROVIDER`.`ufname`)}, ' ')},
`APPOINTMENT_PROVIDER`.`uminitial`)} as Appointment_Provider_Name
from (`enc` `EMR_ENCOUNTER` LEFT OUTER JOIN `edi_facilities`
`EDI_FACILITIES` on `EMR_ENCOUNTER`.`facilityId` =
`EDI_FACILITIES`.`Id`) LEFT OUTER JOIN (`doctors` `APPOINTMENT_DOCTOR`
INNER JOIN `users` `APPOINTMENT_PROVIDER` on
`APPOINTMENT_DOCTOR`.`doctorID` = `APPOINTMENT_PROVIDER`.`uid`) on
`EMR_ENCOUNTER`.`doctorID` = `APPOINTMENT_DOCTOR`.`doctorID` where
`EMR_ENCOUNTER`.`encType` = 2 and `EMR_ENCOUNTER`.`date` between
cast('2011-01-01' as date) and cast('2013-05-07' as date) and
`EMR_ENCOUNTER`.`patientID` 8663 and `EMR_ENCOUNTER`.`VisitType`
'PTDASH' and `EMR_ENCOUNTER`.`deleteFlag` = 0 and
`APPOINTMENT_PROVIDER`.`UserType` = 1 and
`APPOINTMENT_PROVIDER`.`delFlag` = 0 and `EDI_FACILITIES`.`DeleteFlag`
= 0) `Query1` LEFT OUTER JOIN (select distinct
`Addressed_Query`.`moddate` as Locked_Date ,
`Created_Query`.`moddate` as Created_Date ,
`Created_Query`.`encounterid` as encounterid ,
`Created_Query`.`reason` as reason
RE: Slow Response -- What Does This Sound Like to You?
MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM -- Eric Robinson Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
-Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for Rick James,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Slow Response -- What Does This Sound Like to You?
I delt with a similar situation where users complained the system would freeze up for 30-60 seconds at random intervals. After days of looking at queries, logs, error logs, etc.. We were no closer to finding a solution. We do have a service that runs every 15 minutes to cache some data in our system, in one app it creates some temporary tables. In this app the tables were not being created as memory tables. Since we also use connection pooling, the temporary tables created evey 15 minutes were not dropped when the task completed. When the connection was finally closed there were a lot of temporary tables to drop and the MySQL server would hang while this process was completed. Changing to memory tables solved the problem. Might not be your issue but it reminded me of this. On Thursday, May 9, 2013, Robinson, Eric wrote: We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM -- Eric Robinson Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com javascript:;. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Slow Response -- What Does This Sound Like to You?
Am 09.05.2013 22:58, schrieb Robinson, Eric: Q: What conditions could cause single query to lock up a database for a while for all users From http://docs.oracle.com/cd/E17952_01/refman-5.5-en/table-locking.html : A SELECT statement that takes a long time to run prevents other sessions from updating the table in the meantime, making the other sessions appear slow or unresponsive. While a session is waiting to get exclusive access to the table for updates, other sessions that issue SELECT statements will queue up behind it, reducing concurrency even for read-only sessions. You might try using low_priority_updates to mitigate this. Regards, -- Denis Jedig syneticon networks gmbh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
On Thu, May 9, 2013 15:25, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric This may be a dumb question, but have you verified that the applications do not issue a Lock TABLES ...? Either the big one or one of the others. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
-Original Message- From: Wm Mussatto [mailto:mussa...@csz.com] Sent: Thursday, May 09, 2013 3:50 PM To: Robinson, Eric Cc: Rick James; mysql@lists.mysql.com Subject: RE: Slow Response -- What Does This Sound Like to You? On Thu, May 9, 2013 15:25, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric This may be a dumb question, but have you verified that the applications do not issue a Lock TABLES ...? Either the big one or one of the others. I have not verified this, but it should be easy to find out. Hopefully that is not the case as it is a canned application and we don't have access to the code. --Eric Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for Wm Mussatto,Rick James,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Slow Response -- What Does This Sound Like to You?
Hello Eric, On 5/9/2013 7:13 PM, Robinson, Eric wrote: -Original Message- From: Wm Mussatto [mailto:mussa...@csz.com] Sent: Thursday, May 09, 2013 3:50 PM To: Robinson, Eric Cc: Rick James; mysql@lists.mysql.com Subject: RE: Slow Response -- What Does This Sound Like to You? On Thu, May 9, 2013 15:25, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric This may be a dumb question, but have you verified that the applications do not issue a Lock TABLES ...? Either the big one or one of the others. I have not verified this, but it should be easy to find out. Hopefully that is not the case as it is a canned application and we don't have access to the code. --Eric Another option to keep in mind is the effect of a very large Query Cache. Each change to a table must invalidate every query (and their results) that derived from that table. For large caches, that can bring the server to a cold halt until the purge complete. Try disabling it entirely and see how that affects performance or make it much smaller. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Slow Response -- What Does This Sound Like to You?
On 05/09/2013 03:25 PM, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDBm to have been finished Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric One thing I'd look at to start is the error log, if enabled. After that, I'd look at running mysqltuner to get a look at statistics before and after one of these events. I know there are those who prefer the Percona toolkit, but those pull lots raw stats and offers little in terms of suggestions... Unless you wish to engage Percona. Be aware, there are two versions of mysqltuner. The one I use is found at http://mysqltuner.pl. I know, it's old, but it at least runs. The newer one doesn't seem to have been brought to completion. You might want to enable the slow query option that logs queries that execute without indexes. They can be real killers. Reports that use views often cause this as views become complex joins under the hood that can easily miss your indexes resulting in full table scans. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
1. MyISAM locks _tables_. That can cause other connections to be blocked. Solution: switch to InnoDB. Caution: There are a few caveats when switching; see https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/ 2. As mentioned by Shawn, the Query Cache can be more trouble than it is worth. However 90 seconds cannot be blamed on the QC. Still, shrink it or turn it off: * If frequently writing to tables, turn it off (type=OFF _and_ size=0) * If less frequently, then decide which queries will benefit, add SQL_CACHE to them, set type=DEMAND and size=50M (no larger). 3. Meanwhile, try to make that long query more efficient. Can you show it to us, together with SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN ? -Original Message- From: Bruce Ferrell [mailto:bferr...@baywinds.org] Sent: Thursday, May 09, 2013 6:05 PM To: mysql@lists.mysql.com Subject: Re: Slow Response -- What Does This Sound Like to You? On 05/09/2013 03:25 PM, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDBm to have been finished Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric One thing I'd look at to start is the error log, if enabled. After that, I'd look at running mysqltuner to get a look at statistics before and after one of these events. I know there are those who prefer the Percona toolkit, but those pull lots raw stats and offers little in terms of suggestions... Unless you wish to engage Percona. Be aware, there are two versions of mysqltuner. The one I use is found at http://mysqltuner.pl. I know, it's old, but it at least runs. The newer one doesn't seem to have been brought to completion. You might want to enable the slow query option that logs queries that execute without indexes. They can be real killers. Reports that use views often cause this as views become complex joins under the hood that can easily miss your indexes resulting in full table scans. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
Hi everybody, I think we need to focus on three things:- A) temp tables created on disk B) table cache size C) buffer sizes If you find the number of temp tables created on disk is very large, please increase the temp_table_size. Enable the slow query log And check if sort buffer size and join buffer size needss to be increased if multiple joins are used. Also check whether the tables used in the slow queries do have index build on them or not. This heavily impacts the performance. If not create index on frequently used tables. Please try the above and let us know if resolved. Regards Vikas shukla -Original Message- From: Rick James rja...@yahoo-inc.com Sent: 10-05-2013 07:24 To: Bruce Ferrell bferr...@baywinds.org; mysql@lists.mysql.com mysql@lists.mysql.com Subject: RE: Slow Response -- What Does This Sound Like to You? 1. MyISAM locks _tables_. That can cause other connections to be blocked. Solution: switch to InnoDB. Caution: There are a few caveats when switching; see https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/ 2. As mentioned by Shawn, the Query Cache can be more trouble than it is worth. However 90 seconds cannot be blamed on the QC. Still, shrink it or turn it off: * If frequently writing to tables, turn it off (type=OFF _and_ size=0) * If less frequently, then decide which queries will benefit, add SQL_CACHE to them, set type=DEMAND and size=50M (no larger). 3. Meanwhile, try to make that long query more efficient. Can you show it to us, together with SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN ? -Original Message- From: Bruce Ferrell [mailto:bferr...@baywinds.org] Sent: Thursday, May 09, 2013 6:05 PM To: mysql@lists.mysql.com Subject: Re: Slow Response -- What Does This Sound Like to You? On 05/09/2013 03:25 PM, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDBm to have been finished Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric One thing I'd look at to start is the error log, if enabled. After that, I'd look at running mysqltuner to get a look at statistics before and after one of these events. I know there are those who prefer the Percona toolkit, but those pull lots raw stats and offers little in terms of suggestions... Unless you wish to engage Percona. Be aware, there are two versions of mysqltuner. The one I use is found at http://mysqltuner.pl. I know, it's old, but it at least runs. The newer one doesn't seem to have been brought to completion. You might want to enable the slow query option that logs queries that execute without indexes. They can be real killers. Reports that use views often cause this as views become complex joins under the hood that can easily miss your indexes resulting in full table scans. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Still struggling witn like 'CTV%' over varchar.... I simple cannot understand..
- Original Message - From: Andrés Tello mr.crip...@gmail.com showed the usage of the index, then, some time later, it show, for the same query, the usage of no index... Look at the rows field. It's obvious that this table is live and rather on the active side; and the data has changed in such a way that on the second explain, the optimizer estimates that there would be little benefit from using that key - most likely due to cardinality. Make a copy of the table so your data is static, and you'll get the same explain every time. That will, however, apparently not simulate the real world for you. If this is a MyISAM table you may need to run ANALYZE TABLE to update the statistics; or you may just have to accept that the same query on different data may benefit from a different execution plan - just as a different query on the same data would. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Still struggling witn like 'CTV%' over varchar.... I simple cannot understand..
mysql explain select * from cuenta where rutaCuenta like 'CTV%'; ++-++---+---++-+--++-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-++---+---++-+--++-+ | 1 | SIMPLE | cuenta | range | rutaCuenta| rutaCuenta | 258 | NULL | 876824 | Using where | ++-++---+---++-+--++-+ 1 row in set (0.00 sec) mysql explain select * from cuenta where rutaCuenta like 'CTV%'; ++-++--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-++--+---+--+-+--+-+-+ | 1 | SIMPLE | cuenta | ALL | rutaCuenta| NULL | NULL| NULL | 5274306 | Using where | ++-++--+---+--+-+--+-+-+ 1 row in set (0.00 sec) Any ideas? I'm creating running a process which populates the rutaCuenta field with some codification to retrieve some hierarchical based in that field... I did the explain meanwhile doing the populating process, and it showed the usage of the index, then, some time later, it show, for the same query, the usage of no index... Why? I really appreciate some guidance... I find no logic at all...
Re: 'myisam_use_mmap' unstable like hell
Am 15.12.2011 08:47, schrieb Rob Wultsch: To be brutally honest, if you want stability you should not be using MyISAM this is bullshit without 'myisam_use_mmap' i never saw mysqld crashing in the past 10 years, independent of the storage engine much less a not particularly commonly used feature. mmap is not rocket science, so i do not understnd why this is not properly debugged and EFAULT on signature.asc Description: OpenPGP digital signature
Re: 'myisam_use_mmap' unstable like hell
When I had memory issues, with something relatively stable, mostly is due faulty ram... Can you use or less ram or change fisically the ram? On Thu, Dec 15, 2011 at 2:23 AM, Reindl Harald h.rei...@thelounge.netwrote: Am 15.12.2011 08:47, schrieb Rob Wultsch: To be brutally honest, if you want stability you should not be using MyISAM this is bullshit without 'myisam_use_mmap' i never saw mysqld crashing in the past 10 years, independent of the storage engine much less a not particularly commonly used feature. mmap is not rocket science, so i do not understnd why this is not properly debugged and EFAULT on
Re: 'myisam_use_mmap' unstable like hell
this is NOT a memory issue 'myisam_use_mmap' in mysqld is buggy since a long time http://bugs.mysql.com/bug.php?id=48726 we are speaking of a HP ProLiant DL 380G7 in a VMware-Cluster with 36 GB ECC-RAM while there are machines using InnoDB with 'large-pages' and some GB buffer_pool_size on the same host and not about some customer hardware Am 15.12.2011 18:22, schrieb Andrés Tello: When I had memory issues, with something relatively stable, mostly is due faulty ram... Can you use or less ram or change fisically the ram? On Thu, Dec 15, 2011 at 2:23 AM, Reindl Harald h.rei...@thelounge.netwrote: Am 15.12.2011 08:47, schrieb Rob Wultsch: To be brutally honest, if you want stability you should not be using MyISAM this is bullshit without 'myisam_use_mmap' i never saw mysqld crashing in the past 10 years, independent of the storage engine much less a not particularly commonly used feature. mmap is not rocket science, so i do not understnd why this is not properly debugged and DEFAULT on signature.asc Description: OpenPGP digital signature
Re: 'myisam_use_mmap' unstable like hell
On Dec 15, 2011, at 12:02 PM, Reindl Harald wrote: this is NOT a memory issue 'myisam_use_mmap' in mysqld is buggy since a long time http://bugs.mysql.com/bug.php?id=48726 This is fixed in 5.1.61, 5.5.20, 5.6.5: http://dev.mysql.com/doc/refman/5.6/en/news-5-6-5.html we are speaking of a HP ProLiant DL 380G7 in a VMware-Cluster with 36 GB ECC-RAM while there are machines using InnoDB with 'large-pages' and some GB buffer_pool_size on the same host and not about some customer hardware Am 15.12.2011 18:22, schrieb Andrés Tello: When I had memory issues, with something relatively stable, mostly is due faulty ram... Can you use or less ram or change fisically the ram? On Thu, Dec 15, 2011 at 2:23 AM, Reindl Harald h.rei...@thelounge.netwrote: Am 15.12.2011 08:47, schrieb Rob Wultsch: To be brutally honest, if you want stability you should not be using MyISAM this is bullshit without 'myisam_use_mmap' i never saw mysqld crashing in the past 10 years, independent of the storage engine much less a not particularly commonly used feature. mmap is not rocket science, so i do not understnd why this is not properly debugged and DEFAULT on -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: 'myisam_use_mmap' unstable like hell
Am 15.12.2011 19:48, schrieb Paul DuBois: On Dec 15, 2011, at 12:02 PM, Reindl Harald wrote: this is NOT a memory issue 'myisam_use_mmap' in mysqld is buggy since a long time http://bugs.mysql.com/bug.php?id=48726 This is fixed in 5.1.61, 5.5.20, 5.6.5: http://dev.mysql.com/doc/refman/5.6/en/news-5-6-5.html hopefully you understand that i do not trust here since it was buggy like hell more than two years and from one major-release to the next signature.asc Description: OpenPGP digital signature
Re: 'myisam_use_mmap' unstable like hell
To be brutally honest, if you want stability you should not be using MyISAM, much less a not particularly commonly used feature. On Thu, Nov 24, 2011 at 12:58 AM, Reindl Harald h.rei...@thelounge.net wrote: and the next one without memlock 24 09:50:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 24 09:50:35 mysqld_safe Starting mysqld daemon with databases from /Volumes/dune/mysql_data 24 9:50:35 [Note] Plugin 'InnoDB' is disabled. 24 9:50:35 [Note] Plugin 'FEDERATED' is disabled. 24 9:50:35 [Note] Plugin 'BLACKHOLE' is disabled. 24 9:50:35 [Note] Plugin 'ARCHIVE' is disabled. 24 9:50:35 [Note] Plugin 'partition' is disabled. 24 9:50:35 [Note] Event Scheduler: Loaded 0 events 24 9:50:35 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.18-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 thelounge.net build 24 9:53:12 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:12 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:17 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:17 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:22 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:22 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:32 [ERROR] Got error 127 when reading table './afi/cms1_sub2' 24 9:53:32 [ERROR] Got error 127 when reading table './afi/cms1_sub2' 24 9:55:02 [ERROR] Got error 127 when reading table './hurnaus/cms1_galerie_sub' 24 9:55:02 [ERROR] Got error 127 when reading table './hurnaus/cms1_galerie_sub' 24 9:55:14 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=268435456 read_buffer_size=262144 max_used_connections=12 max_threads=200 thread_count=3 connection_count=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 418015 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x2ea7080 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7ffd2ea39d40 thread_stack 0x4 /usr/libexec/mysqld(my_print_stacktrace+0x33)[0x7ab8f3] /usr/libexec/mysqld(handle_segfault+0x470)[0x50f190] /lib64/libpthread.so.0(+0xeeb0)[0x7ffdaae93eb0] /lib64/libc.so.6(+0x12ffa5)[0x7ffda920cfa5] /usr/libexec/mysqld(mi_mmap_pread+0x15a)[0x90880a] /usr/libexec/mysqld(_mi_read_dynamic_record+0x1fe)[0x90ac5e] /usr/libexec/mysqld(mi_rkey+0x378)[0x930f48] /usr/libexec/mysqld(_ZN9ha_myisam14index_read_mapEPhPKhm16ha_rkey_function+0x59)[0x8f1fe9] /usr/libexec/mysqld[0x5b3f35] /usr/libexec/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x61)[0x5a4721] /usr/libexec/mysqld[0x5b2c65] /usr/libexec/mysqld(_ZN4JOIN4execEv+0xbe1)[0x5c39b1] /usr/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x152)[0x5bf182] /usr/libexec/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x184)[0x5c5074] /usr/libexec/mysqld[0x57df97] /usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x2438)[0x585808] /usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x186)[0x589ef6] /usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x15e5)[0x58b505] /usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x117)[0x61fff7] /usr/libexec/mysqld(handle_one_connection+0x50)[0x6200a0] /lib64/libpthread.so.0(+0x6ccb)[0x7ffdaae8bccb] /lib64/libc.so.6(clone+0x6d)[0x7ffda91bdc2d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7ffd20021720): is an invalid pointer Connection ID (thread ID): 647 Status: NOT_KILLED Original-Nachricht Betreff: 'myisam_use_mmap' unstable like hell Datum: Thu, 24 Nov 2011 09:20:28 +0100 Von: Reindl Harald h.rei...@thelounge.net Organisation: the lounge interactive design An: Mailing-List mysql mysql@lists.mysql.com introduced with 5.1 myisam_use_mmap leads in 5.5.18 after some days to table crashes - will this be ever useful on servers with thousands of tables? 24 8:20:17 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one
Fwd: 'myisam_use_mmap' unstable like hell
and the next one without memlock 24 09:50:30 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 24 09:50:35 mysqld_safe Starting mysqld daemon with databases from /Volumes/dune/mysql_data 24 9:50:35 [Note] Plugin 'InnoDB' is disabled. 24 9:50:35 [Note] Plugin 'FEDERATED' is disabled. 24 9:50:35 [Note] Plugin 'BLACKHOLE' is disabled. 24 9:50:35 [Note] Plugin 'ARCHIVE' is disabled. 24 9:50:35 [Note] Plugin 'partition' is disabled. 24 9:50:35 [Note] Event Scheduler: Loaded 0 events 24 9:50:35 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.18-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 thelounge.net build 24 9:53:12 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:12 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:17 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:17 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:22 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:22 [ERROR] Got error 127 when reading table './aume/skefonds2009_ext_content' 24 9:53:32 [ERROR] Got error 127 when reading table './afi/cms1_sub2' 24 9:53:32 [ERROR] Got error 127 when reading table './afi/cms1_sub2' 24 9:55:02 [ERROR] Got error 127 when reading table './hurnaus/cms1_galerie_sub' 24 9:55:02 [ERROR] Got error 127 when reading table './hurnaus/cms1_galerie_sub' 24 9:55:14 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=268435456 read_buffer_size=262144 max_used_connections=12 max_threads=200 thread_count=3 connection_count=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 418015 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x2ea7080 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7ffd2ea39d40 thread_stack 0x4 /usr/libexec/mysqld(my_print_stacktrace+0x33)[0x7ab8f3] /usr/libexec/mysqld(handle_segfault+0x470)[0x50f190] /lib64/libpthread.so.0(+0xeeb0)[0x7ffdaae93eb0] /lib64/libc.so.6(+0x12ffa5)[0x7ffda920cfa5] /usr/libexec/mysqld(mi_mmap_pread+0x15a)[0x90880a] /usr/libexec/mysqld(_mi_read_dynamic_record+0x1fe)[0x90ac5e] /usr/libexec/mysqld(mi_rkey+0x378)[0x930f48] /usr/libexec/mysqld(_ZN9ha_myisam14index_read_mapEPhPKhm16ha_rkey_function+0x59)[0x8f1fe9] /usr/libexec/mysqld[0x5b3f35] /usr/libexec/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x61)[0x5a4721] /usr/libexec/mysqld[0x5b2c65] /usr/libexec/mysqld(_ZN4JOIN4execEv+0xbe1)[0x5c39b1] /usr/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x152)[0x5bf182] /usr/libexec/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x184)[0x5c5074] /usr/libexec/mysqld[0x57df97] /usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x2438)[0x585808] /usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x186)[0x589ef6] /usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x15e5)[0x58b505] /usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x117)[0x61fff7] /usr/libexec/mysqld(handle_one_connection+0x50)[0x6200a0] /lib64/libpthread.so.0(+0x6ccb)[0x7ffdaae8bccb] /lib64/libc.so.6(clone+0x6d)[0x7ffda91bdc2d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7ffd20021720): is an invalid pointer Connection ID (thread ID): 647 Status: NOT_KILLED Original-Nachricht Betreff: 'myisam_use_mmap' unstable like hell Datum: Thu, 24 Nov 2011 09:20:28 +0100 Von: Reindl Harald h.rei...@thelounge.net Organisation: the lounge interactive design An: Mailing-List mysql mysql@lists.mysql.com introduced with 5.1 myisam_use_mmap leads in 5.5.18 after some days to table crashes - will this be ever useful on servers with thousands of tables? 24 8:20:17 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong
Using @ variables with LIKE,CONCAT
This used to work fine in Mysql 4.3, but no longer works in 5.5.8:
set @txt='needle';
select * from table where field like CONCAT('%',@txt,'%');
--returns the null set. If I substitute like this:
select * from table where field like '%needle%';
it works perfectly (and as it did in 4.x).
How can I get this to work in 5.5.x?
Thanks,
-Hank
Re: Using @ variables with LIKE,CONCAT
Hi, I just tried this on a schema I had laying about and it worked fine:
mysql SET @dude='pilgrim';
Query OK, 0 rows affected (0.00 sec)
mysql SELECT namefield FROM mytable WHERE namefield LIKE
CONCAT('%',@dude,'%');
+---+
| name |
+---+
| Blood Elf Pilgrim |
| Blood Elf Pilgrim |
| Draenei Pilgrim |
| High Elf Pilgrim |
| Pilgrim Gal'ressa |
| Recovering Pilgrim|
| Wounded Blood Elf Pilgrim |
| Young Pilgrim |
+---+
I am running 5.5.6 x64 on Mac OS X.
Rich
On 11 May 2011 20:03, Hank hes...@gmail.com wrote:
This used to work fine in Mysql 4.3, but no longer works in 5.5.8:
set @txt='needle';
select * from table where field like CONCAT('%',@txt,'%');
--returns the null set. If I substitute like this:
select * from table where field like '%needle%';
it works perfectly (and as it did in 4.x).
How can I get this to work in 5.5.x?
Thanks,
-Hank
--
*Richard Bensley*
*Database Administrator*
*
*
richard.bens...@photobox.com
skype: richardbensley
Mobile: 07540878285
RE: Join based upon LIKE
-Original Message- From: Nuno Tavares [mailto:nuno.tava...@dri.pt] Sent: Tuesday, May 03, 2011 6:21 PM To: mysql@lists.mysql.com Subject: Re: Join based upon LIKE Dear Jerry, I've been silently following this discussion because I've missed the original question. But from your last explanation, now it really looks you have a data quality kind of issue, which is by far related with MySQL. [JS] Definitely -- but I have to work with the tools available. This is only one part of the process, there is more trouble further on that is not related to our database at all. Indeed, in Data Quality, there is *never* a ready solution, because the source is tipically chaotic May I suggest you to explore Google Refine? It seems to be able to address all those issues quite nicely, and the clustering might solve your problem at once. You shall know, however, how to export the tables (or a usable JOIN) as a CSV, see SELECT ... INTO OUTFILE for that. [JS] I never heard of Google Refine. Thanks for bringing to my attention. Hope it helps, -NT [JS] Thank you. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Em 03-05-2011 21:34, Jerry Schwartz escreveu: My situation is sounds rather simple. All I am doing is matching a spreadsheet of products against our database. My job is to find any matches against existing products and determine which ones are new, which ones are replacements for older products, and which ones just need to have the publication date (and page count, price, whatever) refreshed. Publisher is no problem. What I have for each feed is a title and (most of the time) an ISBN or other identification assigned by the publisher. Matching by product ID is easy (assuming there aren't any mistakes in the current or previous feeds); but the publisher might or might not change the product ID when they update a report. That's why I also run a match by title, and that's where all the trouble comes from. The publisher might or might not include a mix of old and new products in a feed. The publisher might change the title of an existing product, either on purpose or by accident; they might simply be sloppy about their spelling; or (and this is where it is critical) the title might include a reference to some time period such as a year or a quarter. I think we'd better pull the plug on this discussion. It doesn't seem like there's a ready solution. Fortunately our database is small, and most feeds are only a few hundred products. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: shawn wilson [mailto:ag4ve...@gmail.com] Sent: Tuesday, May 03, 2011 4:08 PM Cc: mysql mailing list Subject: Re: Join based upon LIKE I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should
Re: Join based upon LIKE
http://www.gedpage.com/soundex.html offers a simple explanation of what it does. One possibility would be building a referential table with only a recordID and soundex column, unique over both; and filling that with the soundex of individual nonjunk words. So, from the titles 1 | Rain in Spain 2 | Spain's Rain you'd get 1 | R500 1 | S150 2 | S150 2 | R500 From thereon, you can see that all the same words have been used - ignoring a lot of spelling errors like Spian. Obviously not a magic solution, but it's a start. - Original Message - From: Jerry Schwartz je...@gii.co.jp To: Johan De Meersman vegiv...@tuxera.be Cc: Jim McNeely j...@newcenturydata.com, mysql mailing list mysql@lists.mysql.com Sent: Monday, 2 May, 2011 4:09:36 PM Subject: RE: Join based upon LIKE [JS] I've thought about using soundex(), but I'm not quite sure how. I didn't pursue it much because there are so many odd terms such as chemical names, but perhaps I should give it a try in my infinite free time. [JS] Thanks for your condolences. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Tuesday, May 03, 2011 5:31 AM To: Jerry Schwartz Cc: Jim McNeely; mysql mailing list; Johan De Meersman Subject: Re: Join based upon LIKE http://www.gedpage.com/soundex.html offers a simple explanation of what it does. One possibility would be building a referential table with only a recordID and soundex column, unique over both; and filling that with the soundex of individual nonjunk words. So, from the titles 1 | Rain in Spain 2 | Spain's Rain you'd get 1 | R500 1 | S150 2 | S150 2 | R500 From thereon, you can see that all the same words have been used - ignoring a lot of spelling errors like Spian. Obviously not a magic solution, but it's a start. [JS] Thanks. I'm not sure that I could easily build a dictionary of non-junk words, since some of these reports have titles like Toluene Diisocyanate Market Outlook 2008, Toluene Market Outlook 2008, and Toluene: 2009 World Market Outlook And Forecast (Special Crisis Edition). I shall ponder this when I am caught up, or (more likely) in the afterlife. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com - Original Message - From: Jerry Schwartz je...@gii.co.jp To: Johan De Meersman vegiv...@tuxera.be Cc: Jim McNeely j...@newcenturydata.com, mysql mailing list mysql@lists.mysql.com Sent: Monday, 2 May, 2011 4:09:36 PM Subject: RE: Join based upon LIKE [JS] I've thought about using soundex(), but I'm not quite sure how. I didn't pursue it much because there are so many odd terms such as chemical names, but perhaps I should give it a try in my infinite free time. [JS] Thanks for your condolences. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
- Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com
RE: Join based upon LIKE
My situation is sounds rather simple. All I am doing is matching a spreadsheet of products against our database. My job is to find any matches against existing products and determine which ones are new, which ones are replacements for older products, and which ones just need to have the publication date (and page count, price, whatever) refreshed. Publisher is no problem. What I have for each feed is a title and (most of the time) an ISBN or other identification assigned by the publisher. Matching by product ID is easy (assuming there aren't any mistakes in the current or previous feeds); but the publisher might or might not change the product ID when they update a report. That's why I also run a match by title, and that's where all the trouble comes from. The publisher might or might not include a mix of old and new products in a feed. The publisher might change the title of an existing product, either on purpose or by accident; they might simply be sloppy about their spelling; or (and this is where it is critical) the title might include a reference to some time period such as a year or a quarter. I think we'd better pull the plug on this discussion. It doesn't seem like there's a ready solution. Fortunately our database is small, and most feeds are only a few hundred products. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: shawn wilson [mailto:ag4ve...@gmail.com] Sent: Tuesday, May 03, 2011 4:08 PM Cc: mysql mailing list Subject: Re: Join based upon LIKE I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
Dear Jerry, I've been silently following this discussion because I've missed the original question. But from your last explanation, now it really looks you have a data quality kind of issue, which is by far related with MySQL. Indeed, in Data Quality, there is *never* a ready solution, because the source is tipically chaotic May I suggest you to explore Google Refine? It seems to be able to address all those issues quite nicely, and the clustering might solve your problem at once. You shall know, however, how to export the tables (or a usable JOIN) as a CSV, see SELECT ... INTO OUTFILE for that. Hope it helps, -NT Em 03-05-2011 21:34, Jerry Schwartz escreveu: My situation is sounds rather simple. All I am doing is matching a spreadsheet of products against our database. My job is to find any matches against existing products and determine which ones are new, which ones are replacements for older products, and which ones just need to have the publication date (and page count, price, whatever) refreshed. Publisher is no problem. What I have for each feed is a title and (most of the time) an ISBN or other identification assigned by the publisher. Matching by product ID is easy (assuming there aren't any mistakes in the current or previous feeds); but the publisher might or might not change the product ID when they update a report. That's why I also run a match by title, and that's where all the trouble comes from. The publisher might or might not include a mix of old and new products in a feed. The publisher might change the title of an existing product, either on purpose or by accident; they might simply be sloppy about their spelling; or (and this is where it is critical) the title might include a reference to some time period such as a year or a quarter. I think we'd better pull the plug on this discussion. It doesn't seem like there's a ready solution. Fortunately our database is small, and most feeds are only a few hundred products. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: shawn wilson [mailto:ag4ve...@gmail.com] Sent: Tuesday, May 03, 2011 4:08 PM Cc: mysql mailing list Subject: Re: Join based upon LIKE I'm actually enjoying this discussion because I have the same type of issue. However, I have done away with trying to do a full text search in favor of making a table with unique fields where all fields should uniquely identify the group. If I get a dupe, I can clean it up. However, like you, they don't want me to mess with the original data. So, what I have is another table with my good data that my table with my unique data refers to. If a bad record is creased, I don't care I just create my relationship to the table of data I know (read think - I rarely look at this stuff) is good. So, I have 4 fields that should be unique for a group. Two chats and two ints. If three of these match a record in the 'good data' table - there's my relationship. If two or less match, I create a new record in my 'good data' table and log the event. (I haven't gotten to the logging part yet though, easy enough just to look sense none of the fields in 'good data' should match) I'm thinking you might have to dig deeper than me to find 'good data' but I think its there. Maybe isbn, name, publisher + address, price, average pages, name of sales person, who you guys pay for the material, etc etc etc. On May 3, 2011 10:59 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Jerry Schwartz je...@gii.co.jp I'm not sure that I could easily build a dictionary of non-junk words, since The traditional way is to build a database of junk words. The list tends to be shorter :-) Think and/or/it/the/with/like/... Percentages of mutual and non-mutual words between two titles should be a reasonable indicator of likeness. You could conceivably even assign value to individual words, so polypropylbutanate is more useful than synergy for comparison purposes. All very theoretical, though, I haven't actually done much of it to this level. My experience in data mangling is limited to mostly should-be-fixed-format data like sports results. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ag4ve...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Sunday, May 01, 2011 4:01 AM To: Jerry Schwartz Cc: Jim McNeely; mysql mailing list Subject: Re: Join based upon LIKE - Original Message - From: Jerry Schwartz je...@gii.co.jp I shove those modified titles into a table and do a JOIN ON `prod_title` LIKE `wild_title`. Roughly what I meant with the shadow fields, yes - keep your own set of data around :-) I have little more to offer, then, I'm afraid. The soundex() algorithm may or may not be of some use to you; it offers comparison based (roughly) on pronounciation instead of spelling. [JS] I've thought about using soundex(), but I'm not quite sure how. I didn't pursue it much because there are so many odd terms such as chemical names, but perhaps I should give it a try in my infinite free time. Apart from that, you have my deepest sympathy. I hope you can wake up from the nightmare soon :-) [JS] Thanks for your condolences. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
- Original Message - From: Jerry Schwartz je...@gii.co.jp I shove those modified titles into a table and do a JOIN ON `prod_title` LIKE `wild_title`. Roughly what I meant with the shadow fields, yes - keep your own set of data around :-) I have little more to offer, then, I'm afraid. The soundex() algorithm may or may not be of some use to you; it offers comparison based (roughly) on pronounciation instead of spelling. Apart from that, you have my deepest sympathy. I hope you can wake up from the nightmare soon :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: FW: Join based upon LIKE
2011/04/28 15:28 -0400, Jerry Schwartz No takers? And this is not real taking, because the algorithm of which I am thinking, the edit-distance (Levens(h)tein-distance) algorithm costs too much for you (see the Wikipedia entry). The obvious implementation takes as many steps as the product of the two compared strings s length. On the other hand, a good implementation of LIKE costs the pattern s length added to all the strings against which it matches s length, a sum, not product, of lengths. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: FW: Join based upon LIKE
2011/04/28 15:28 -0400, Jerry Schwartz No takers? And this is not real taking, because the algorithm of which I am thinking, the edit-distance (Levens(h)tein-distance) algorithm costs too much for you (see the Wikipedia entry), but it yields, I believe, much more nearly such answer as you want. The obvious implementation takes as many steps as the product of the two compared strings s length. On the other hand, a good implementation of LIKE costs the pattern s length added to all the strings against which it matches s length, a sum, not product, of lengths. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
- Original Message - From: Jerry Schwartz je...@gii.co.jp [JS] This isn't the only place I have to deal with fuzzy data. :-( Discretion prohibits further comment. Heh. What you *really* need, is a LART. Preferably one of the spiked variety. A full-text index would work if I were only looking for one title at a time, but I don't know if that would be a good idea if I have a list of 1 titles. That would pretty much require either 1 separate queries or a very, very long WHERE clause. Yes, unfortunately. You should see if you can introduce a form of data normalisation - say, shadow fields with corrected entries, or functionality in the application that suggests correct entries based on what the user typed. Or, if the money's there, you could have a look at Amazon Mechanical Turk (yes, really) for cheap-ish data correction. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Friday, April 29, 2011 5:56 AM To: Jerry Schwartz Cc: mysql mailing list Subject: Re: Join based upon LIKE - Original Message - From: Jerry Schwartz je...@gii.co.jp [JS] This isn't the only place I have to deal with fuzzy data. :-( Discretion prohibits further comment. Heh. What you *really* need, is a LART. Preferably one of the spiked variety. [JS] Unless a LART is a demon of some kind, I don't know what it is. A full-text index would work if I were only looking for one title at a time, but I don't know if that would be a good idea if I have a list of 1 titles. That would pretty much require either 1 separate queries or a very, very long WHERE clause. Yes, unfortunately. You should see if you can introduce a form of data normalisation - say, shadow fields with corrected entries, or functionality in the application that suggests correct entries based on what the user typed. [JS] Except for obvious misspellings and non-ASCII characters, I do not have the freedom to muck with the text. If the data were created in-house, I could correct it on the way in; but it comes from myriad other companies. Or, if the money's there, you could have a look at Amazon Mechanical Turk (yes, really) for cheap-ish data correction. [JS] Again, I can't change the data. The titles are assigned by the publishers. Think what would happen if Amazon decided to fix the titles of books. Ain't Misbehavin would, at best, turn into I am not misbehaving. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message-
From: Jim McNeely [mailto:j...@newcenturydata.com]
Sent: Thursday, April 28, 2011 6:43 PM
To: Jerry Schwartz
Subject: Re: Join based upon LIKE
It just smells wrong, a nicer system would have you joining on ID's of some
kind so that spelling wouldn't matter. I don't know the full situation for
you
though.
[JS] That would be nice, wouldn't it.
In a nutshell, we sell publications. Publishers send us lists of publications.
Some are new, some replace previous editions. (Think of books, almanacs, and
newsletters.) Some publishers make do without any product IDs at all, but most
do use product IDs of some kind.
The problem is that the March edition of a publication might or might not have
the same product ID as the February edition. I try to match them both by
product ID and by title. Sometimes the title will fuzzy match, but the ID
won't; sometimes the ID will match but the title won't; sometimes (if I'm
really lucky) they both match; and sometimes the ID matches one product and
the title matches another.
It's the fuzzy match by title that gives me fits:
- The title might have a date in it (Rain in Spain in 2010 Q2), but not
necessarily in a uniform way (Rain in Spain Q3 2010).
- The title might have differences in wording or punctuation (Rain in Spain -
2010Q2).
- The title might have simple misspellings (Rain in Spian - Q2 2010).
I've written code that looks for troublesome constructs and replaces them with
%: in , -, to , Q2, 2Q, and more and more. So Rain in Spain -
2010 Q2 becomes Rain%Spain%.
I shove those modified titles into a table and do a JOIN ON `prod_title` LIKE
`wild_title`.
This will miss actual misspellings (Spain, Spian). It will also produce a
large number of false positives.
On the back end, I have other code that compares the new titles against the
titles retrieved by that query and decides if they are exact matches,
approximate matches (here I do use regular expressions, as well as lists of
known bad boys), or false positives. From there on, it's all hand work.
Pretty big nut, eh?
So that's why I need to use LIKE in my JOIN.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
Jim McNeely
On Apr 28, 2011, at 12:28 PM, Jerry Schwartz wrote:
No takers?
-Original Message-
From: Jerry Schwartz [mailto:je...@gii.co.jp]
Sent: Monday, April 25, 2011 2:34 PM
To: 'Mailing-List mysql'
Subject: Join based upon LIKE
I have to match lists of new publications against our database, so that I
can
replace the existing publications in our catalog. For example,
The UK Market for Puppies in February 2011
would be a replacement for
The UK Market for Puppies in December 2010
Unfortunately, the publishers aren't particularly careful with their
titles.
One might even say they are perverse. I am likely to get
UK Market: Puppies - Feb 2011
as replacement for
The UK Market for Puppies in December 2010
You can see that a straight match by title is not going to work.
Here's what I've been doing:
=
SET @PUBID = (SELECT pub.pub_id FROM pub WHERE pub.pub_code = 'GD');
CREATE TEMPORARY TABLE new_titles (
new_title VARCHAR(255), INDEX (new_title),
new_title_like VARCHAR(255), INDEX (new_title_like)
);
INSERT INTO new_titles
VALUES
('Alternative Energy Monthly Deal Analysis - MA and Investment Trends,
April
2011', 'Alternative Energy Monthly Deal Analysis%MA%Investment Trends%'),
('Asia Pacific Propylene Industry Outlook to 2015 - Market Size, Company
Share, Price Trends, Capacity Forecasts of All Active and Planned Plants',
'Asia Pacific Propylene Industry Outlook to%Market Size%Company Share%Price
Trends%Capacity Forecasts of All Active%Planned Plants'),
...
('Underground Gas Storage Industry Outlook in North America, 2011 - Details
of
All Operating and Planned Gas Storage Sites to 2014', 'Underground Gas
Storage
Industry Outlook%North America%Details of All Operating%Planned Gas Storage
Sites to%'),
('Uveitis Therapeutics - Pipeline Assessment and Market Forecasts to 2017',
'Uveitis Therapeutics%Pipeline Assessment%Market Forecasts to%');
SELECT prod.prod_title AS `Title IN Database`,
new_titles.new_title AS `Title IN Feed`,
prod.prod_num AS `ID`
FROM new_titles JOIN prod ON prod.prod_title LIKE
(new_titles.new_title_like)
AND prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY new_titles.new_title;
==
(I've written code that substitutes % for certain strings that I specify,
and there is some trial and error involved.)
Here's how MySQL handles that SELECT:
*** 1. row ***
id: 1
select_type: SIMPLE
table: new_titles
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows
FW: Join based upon LIKE
No takers?
-Original Message-
From: Jerry Schwartz [mailto:je...@gii.co.jp]
Sent: Monday, April 25, 2011 2:34 PM
To: 'Mailing-List mysql'
Subject: Join based upon LIKE
I have to match lists of new publications against our database, so that I can
replace the existing publications in our catalog. For example,
The UK Market for Puppies in February 2011
would be a replacement for
The UK Market for Puppies in December 2010
Unfortunately, the publishers aren't particularly careful with their titles.
One might even say they are perverse. I am likely to get
UK Market: Puppies - Feb 2011
as replacement for
The UK Market for Puppies in December 2010
You can see that a straight match by title is not going to work.
Here's what I've been doing:
=
SET @PUBID = (SELECT pub.pub_id FROM pub WHERE pub.pub_code = 'GD');
CREATE TEMPORARY TABLE new_titles (
new_title VARCHAR(255), INDEX (new_title),
new_title_like VARCHAR(255), INDEX (new_title_like)
);
INSERT INTO new_titles
VALUES
('Alternative Energy Monthly Deal Analysis - MA and Investment Trends, April
2011', 'Alternative Energy Monthly Deal Analysis%MA%Investment Trends%'),
('Asia Pacific Propylene Industry Outlook to 2015 - Market Size, Company
Share, Price Trends, Capacity Forecasts of All Active and Planned Plants',
'Asia Pacific Propylene Industry Outlook to%Market Size%Company Share%Price
Trends%Capacity Forecasts of All Active%Planned Plants'),
...
('Underground Gas Storage Industry Outlook in North America, 2011 - Details of
All Operating and Planned Gas Storage Sites to 2014', 'Underground Gas Storage
Industry Outlook%North America%Details of All Operating%Planned Gas Storage
Sites to%'),
('Uveitis Therapeutics - Pipeline Assessment and Market Forecasts to 2017',
'Uveitis Therapeutics%Pipeline Assessment%Market Forecasts to%');
SELECT prod.prod_title AS `Title IN Database`,
new_titles.new_title AS `Title IN Feed`,
prod.prod_num AS `ID`
FROM new_titles JOIN prod ON prod.prod_title LIKE (new_titles.new_title_like)
AND prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY new_titles.new_title;
==
(I've written code that substitutes % for certain strings that I specify,
and there is some trial and error involved.)
Here's how MySQL handles that SELECT:
*** 1. row ***
id: 1
select_type: SIMPLE
table: new_titles
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 47
Extra: Using filesort
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id
key: pub_id
key_len: 48
ref: const
rows: 19607
Extra: Using where
=
Here's the important part of the table `prod`:
=
Table: prod
Create Table: CREATE TABLE `prod` (
`prod_id` varchar(15) NOT NULL DEFAULT '',
`prod_num` mediumint(6) unsigned DEFAULT NULL,
`prod_title` varchar(255) DEFAULT NULL,
`prod_type` varchar(2) DEFAULT NULL,
`prod_vat_pct` decimal(5,2) DEFAULT NULL,
`prod_discont` tinyint(1) DEFAULT NULL,
`prod_replacing` mediumint(6) unsigned DEFAULT NULL,
`prod_replaced_by` mediumint(6) unsigned DEFAULT NULL,
`prod_ready` tinyint(1) DEFAULT NULL,
`pub_id` varchar(15) DEFAULT NULL,
...
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_num` (`prod_num`),
KEY `prod_pub_prod_id` (`prod_pub_prod_id`),
KEY `pub_id` (`pub_id`),
KEY `prod_title` (`prod_title`),
FULLTEXT KEY `prod_title_fulltext` (`prod_title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
=
This works reasonably well for a small number (perhaps 200-300) of new
products; but now I've been handed a list of over 15000 to stuff into the
table `new_titles`! This motivates me to wonder if there is a better way,
since I expect this to take a very long time.
Suggestions?
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Join based upon LIKE
- Original Message - From: Jerry Schwartz je...@gii.co.jp No takers? Not willingly, no :-p This is a pretty complex problem, as SQL itself isn't particularly well-equipped to deal with fuzzy data. One approach that might work is using a fulltext indexing engine (MySQL's built-in ft indices, or an external one like Solr or something) and doing best-fit matches on the keywords of the title you're looking for. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join based upon LIKE
-Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Thursday, April 28, 2011 4:18 PM To: Jerry Schwartz Cc: mysql mailing list Subject: Re: Join based upon LIKE - Original Message - From: Jerry Schwartz je...@gii.co.jp No takers? Not willingly, no :-p This is a pretty complex problem, as SQL itself isn't particularly well- equipped to deal with fuzzy data. One approach that might work is using a fulltext indexing engine (MySQL's built-in ft indices, or an external one like Solr or something) and doing best-fit matches on the keywords of the title you're looking for. [JS] This isn't the only place I have to deal with fuzzy data. :-( Discretion prohibits further comment. A full-text index would work if I were only looking for one title at a time, but I don't know if that would be a good idea if I have a list of 1 titles. That would pretty much require either 1 separate queries or a very, very long WHERE clause. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Join based upon LIKE
I have to match lists of new publications against our database, so that I can
replace the existing publications in our catalog. For example,
The UK Market for Puppies in February 2011
would be a replacement for
The UK Market for Puppies in December 2010
Unfortunately, the publishers aren't particularly careful with their titles.
One might even say they are perverse. I am likely to get
UK Market: Puppies - Feb 2011
as replacement for
The UK Market for Puppies in December 2010
You can see that a straight match by title is not going to work.
Here's what I've been doing:
=
SET @PUBID = (SELECT pub.pub_id FROM pub WHERE pub.pub_code = 'GD');
CREATE TEMPORARY TABLE new_titles (
new_title VARCHAR(255), INDEX (new_title),
new_title_like VARCHAR(255), INDEX (new_title_like)
);
INSERT INTO new_titles
VALUES
('Alternative Energy Monthly Deal Analysis - MA and Investment Trends, April
2011', 'Alternative Energy Monthly Deal Analysis%MA%Investment Trends%'),
('Asia Pacific Propylene Industry Outlook to 2015 - Market Size, Company
Share, Price Trends, Capacity Forecasts of All Active and Planned Plants',
'Asia Pacific Propylene Industry Outlook to%Market Size%Company Share%Price
Trends%Capacity Forecasts of All Active%Planned Plants'),
...
('Underground Gas Storage Industry Outlook in North America, 2011 - Details of
All Operating and Planned Gas Storage Sites to 2014', 'Underground Gas Storage
Industry Outlook%North America%Details of All Operating%Planned Gas Storage
Sites to%'),
('Uveitis Therapeutics - Pipeline Assessment and Market Forecasts to 2017',
'Uveitis Therapeutics%Pipeline Assessment%Market Forecasts to%');
SELECT prod.prod_title AS `Title IN Database`,
new_titles.new_title AS `Title IN Feed`,
prod.prod_num AS `ID`
FROM new_titles JOIN prod ON prod.prod_title LIKE (new_titles.new_title_like)
AND prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY new_titles.new_title;
==
(I've written code that substitutes % for certain strings that I specify,
and there is some trial and error involved.)
Here's how MySQL handles that SELECT:
*** 1. row ***
id: 1
select_type: SIMPLE
table: new_titles
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 47
Extra: Using filesort
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id
key: pub_id
key_len: 48
ref: const
rows: 19607
Extra: Using where
=
Here's the important part of the table `prod`:
=
Table: prod
Create Table: CREATE TABLE `prod` (
`prod_id` varchar(15) NOT NULL DEFAULT '',
`prod_num` mediumint(6) unsigned DEFAULT NULL,
`prod_title` varchar(255) DEFAULT NULL,
`prod_type` varchar(2) DEFAULT NULL,
`prod_vat_pct` decimal(5,2) DEFAULT NULL,
`prod_discont` tinyint(1) DEFAULT NULL,
`prod_replacing` mediumint(6) unsigned DEFAULT NULL,
`prod_replaced_by` mediumint(6) unsigned DEFAULT NULL,
`prod_ready` tinyint(1) DEFAULT NULL,
`pub_id` varchar(15) DEFAULT NULL,
...
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_num` (`prod_num`),
KEY `prod_pub_prod_id` (`prod_pub_prod_id`),
KEY `pub_id` (`pub_id`),
KEY `prod_title` (`prod_title`),
FULLTEXT KEY `prod_title_fulltext` (`prod_title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
=
This works reasonably well for a small number (perhaps 200-300) of new
products; but now I've been handed a list of over 15000 to stuff into the
table `new_titles`! This motivates me to wonder if there is a better way,
since I expect this to take a very long time.
Suggestions?
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problem filtering with a like expression
I ran into this case where a like expression is not evaluated correctly if
the pattern is an expression.
The example below shows a case where *AAA* is not considered *like 'A' ||
'%'*
Is this a known limitation? Or a bug?
create table lookup (
name varchar(60)
);
insert into lookup (name) values ('AAA');
select * from lookup where name like 'A%';
= 1 record returned. OK
select * from lookup where name like 'A' || '%';
= returns nothing. INCORRECT!
select * from lookup where name like ('A' || '%');
= same as previous and returns nothing. INCORRECT!
I reproduced this problem on win32 using versions 5.1 and 5.5.10
Best regards,
Johan
Re: Problem filtering with a like expression
Hi,
|| isn't the concatenation operator by default. If you want it to be set
sql_mode=PIPE_AS_CONCAT. Otherwise, use the CONCAT() function instead of
|| operator.
Peter Boros
On 03/21/2011 11:51 AM, Johan De Taeye wrote:
I ran into this case where a like expression is not evaluated correctly if
the pattern is an expression.
The example below shows a case where *AAA* is not considered *like 'A' ||
'%'*
Is this a known limitation? Or a bug?
create table lookup (
name varchar(60)
);
insert into lookup (name) values ('AAA');
select * from lookup where name like 'A%';
= 1 record returned. OK
select * from lookup where name like 'A' || '%';
= returns nothing. INCORRECT!
select * from lookup where name like ('A' || '%');
= same as previous and returns nothing. INCORRECT!
I reproduced this problem on win32 using versions 5.1 and 5.5.10
Best regards,
Johan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem filtering with a like expression
On Mon, 21 Mar 2011 11:51:27 +0100
Johan De Taeye johan.de.ta...@gmail.com wrote:
insert into lookup (name) values ('AAA');
select * from lookup where name like 'A%';
= 1 record returned. OK
select * from lookup where name like 'A' || '%';
= returns nothing. INCORRECT!
The query is incorrect. The OR switch does not act as an ellipsis, and does not
apply both values to the LIKE. You need to write LIKE X OR LIKE Y, as
select * from lookup where name like 'A' || or name like '%';
select * from lookup where name like ('A' || '%');
= same as previous and returns nothing. INCORRECT!
Again correct, you tried to match `name` against boolean TRUE (the evaluation
of you expression).
Best regards,
Johan
--
Simcha Younger sim...@syounger.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Problem filtering with a like expression
After updating the SQL_MODE, it works as I expect.
Thanks for your prompt replies!
Johan
-Original Message-
From: petya [mailto:pe...@petya.org.hu]
Sent: Monday, March 21, 2011 12:10 PM
To: Johan De Taeye
Cc: mysql@lists.mysql.com
Subject: Re: Problem filtering with a like expression
Hi,
|| isn't the concatenation operator by default. If you want it to be set
sql_mode=PIPE_AS_CONCAT. Otherwise, use the CONCAT() function instead of
|| operator.
Peter Boros
On 03/21/2011 11:51 AM, Johan De Taeye wrote:
I ran into this case where a like expression is not evaluated
correctly if the pattern is an expression.
The example below shows a case where *AAA* is not considered *like 'A'
||
'%'*
Is this a known limitation? Or a bug?
create table lookup (
name varchar(60)
);
insert into lookup (name) values ('AAA');
select * from lookup where name like 'A%';
= 1 record returned. OK
select * from lookup where name like 'A' || '%';
= returns nothing. INCORRECT!
select * from lookup where name like ('A' || '%');
= same as previous and returns nothing. INCORRECT!
I reproduced this problem on win32 using versions 5.1 and 5.5.10
Best regards,
Johan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SUM value like 10,23,15,10
Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil
Re: SUM value like 10,23,15,10
If you're looking at the string 10,23,15,10 in a single field, you'll have to do it the hard way. If you have an int field, and four rows with those values, you can do a group by that field and select the count() of it. On Tue, Aug 24, 2010 at 3:53 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SUM value like 10,23,15,10
Yeah these values are held with a varchar field. On Tue, Aug 24, 2010 at 2:56 PM, Johan De Meersman vegiv...@tuxera.bewrote: If you're looking at the string 10,23,15,10 in a single field, you'll have to do it the hard way. If you have an int field, and four rows with those values, you can do a group by that field and select the count() of it. On Tue, Aug 24, 2010 at 3:53 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SUM value like 10,23,15,10
Then you're pretty much on your own, I'm afraid. Not a very good way to store data :-) You could maybe build a stored procedure, or do it in the app; but it's gonna be code either way. On Tue, Aug 24, 2010 at 3:58 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Yeah these values are held with a varchar field. On Tue, Aug 24, 2010 at 2:56 PM, Johan De Meersman vegiv...@tuxera.be wrote: If you're looking at the string 10,23,15,10 in a single field, you'll have to do it the hard way. If you have an int field, and four rows with those values, you can do a group by that field and select the count() of it. On Tue, Aug 24, 2010 at 3:53 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SUM value like 10,23,15,10
The application is still being developed, so I will probably look at storing it in separate tables so that it can easily be computed. On Tue, Aug 24, 2010 at 3:01 PM, Johan De Meersman vegiv...@tuxera.bewrote: Then you're pretty much on your own, I'm afraid. Not a very good way to store data :-) You could maybe build a stored procedure, or do it in the app; but it's gonna be code either way. On Tue, Aug 24, 2010 at 3:58 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Yeah these values are held with a varchar field. On Tue, Aug 24, 2010 at 2:56 PM, Johan De Meersman vegiv...@tuxera.be wrote: If you're looking at the string 10,23,15,10 in a single field, you'll have to do it the hard way. If you have an int field, and four rows with those values, you can do a group by that field and select the count() of it. On Tue, Aug 24, 2010 at 3:53 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: SUM value like 10,23,15,10
The proper way to do this would indeed be a separate table that has (itemID, property, value) or something like that. On Tue, Aug 24, 2010 at 4:04 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: The application is still being developed, so I will probably look at storing it in separate tables so that it can easily be computed. On Tue, Aug 24, 2010 at 3:01 PM, Johan De Meersman vegiv...@tuxera.bewrote: Then you're pretty much on your own, I'm afraid. Not a very good way to store data :-) You could maybe build a stored procedure, or do it in the app; but it's gonna be code either way. On Tue, Aug 24, 2010 at 3:58 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Yeah these values are held with a varchar field. On Tue, Aug 24, 2010 at 2:56 PM, Johan De Meersman vegiv...@tuxera.be wrote: If you're looking at the string 10,23,15,10 in a single field, you'll have to do it the hard way. If you have an int field, and four rows with those values, you can do a group by that field and select the count() of it. On Tue, Aug 24, 2010 at 3:53 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi In MySQL is it possible to SUM a field which contains like 10,23,15,10. The result I'd be looking for is 10 = count of 2 23 = count of 1 15 = count of 1 Cheers Neil -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Seems like an easy query, but isn't to me. Help?
On 20/08/2010 2:45 a, George Larson wrote: I hope I've come to right place, and I'm asking in the right way -- please accept my apologies if not. We have some dates missing and I need to populate those fields with dates from the record just before them. I've gotten this far: SELECT UUid, MIN(DDenteredDate) minDate FROM UUtable JOIN DDdetail on DDid = UUid WHERE UUdate IS NULL GROUP BY UUid; I can make this a sub-query and get the UUid of the record that I want to copy UUdate from: SELECT sub.UUid-1 as previous, sub.* FROM ( SELECT UUid, MIN(DDenteredDate) minDate FROM UUtable JOIN DDdetail on DDid = UUid WHERE UUdate IS NULL GROUP BY UUid; ) as sub; In this case, the field 'previous' is the UUid that I want to copy the UUdate from and sub.UUid is where I want to copy to. Does that even make sense? Thanks, George Can you send the table create statement so that we can see the structure? I'm guessing the date field is called uudate? (also specify the field that you want to populate with the record before) Is the primary key field uuid? are all the numbers in the primary key field sequential (1,2,3,4) with no gaps? I do have an idea but i need this info to see if it can work. -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Seems like an easy query, but isn't to me. Help?
On 8/19/2010 8:45 PM, George Larson wrote: I hope I've come to right place, and I'm asking in the right way -- please accept my apologies if not. We have some dates missing and I need to populate those fields with dates from the record just before them. I've gotten this far: SELECT UUid, MIN(DDenteredDate) minDate FROM UUtable JOIN DDdetail on DDid = UUid WHERE UUdate IS NULL GROUP BY UUid; I can make this a sub-query and get the UUid of the record that I want to copy UUdate from: SELECT sub.UUid-1 as previous, sub.* FROM ( SELECT UUid, MIN(DDenteredDate) minDate FROM UUtable JOIN DDdetail on DDid = UUid WHERE UUdate IS NULL GROUP BY UUid; ) as sub; In this case, the field 'previous' is the UUid that I want to copy the UUdate from and sub.UUid is where I want to copy to. Does that even make sense? As you discovered, the SQL language is not an ordinal, procedural language. It is a SET-oriented language. The sequence of rows in any one set of results completely depends on either how those rows were isolated from the table(s) on which they reside (random) or by an ORDER BY or similar secondary processing step. Without an ORDER BY, it is perfectly legal for the same query to return the same set of rows in completely different sequences for queries that are executed one immediately after the other. If you want to say the record just before when referring to SQL data and have it mean anything, you must be specific about how you are sequencing your rows. Only then do the concepts of before and after have any meaning. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Seems like an easy query, but isn't to me. Help?
I hope I've come to right place, and I'm asking in the right way -- please accept my apologies if not. We have some dates missing and I need to populate those fields with dates from the record just before them. I've gotten this far: SELECT UUid, MIN(DDenteredDate) minDate FROM UUtable JOIN DDdetail on DDid = UUid WHERE UUdate IS NULL GROUP BY UUid; I can make this a sub-query and get the UUid of the record that I want to copy UUdate from: SELECT sub.UUid-1 as previous, sub.* FROM ( SELECT UUid, MIN(DDenteredDate) minDate FROM UUtable JOIN DDdetail on DDid = UUid WHERE UUdate IS NULL GROUP BY UUid; ) as sub; In this case, the field 'previous' is the UUid that I want to copy the UUdate from and sub.UUid is where I want to copy to. Does that even make sense? Thanks, George
Re: [PHP] newbie sequel question: how do we search for multiple things on 1 field like:
On Fri, Jun 18, 2010 at 16:30, Dave deal...@gmail.com wrote:
SELECT * FROM contacts WHERE state = 'CA' and name = 'bob' or
name = 'sam' or name = 'sara'
We begin by asking on the right list (mysql@lists.mysql.com, CC'd
by courtesy).
You're on the right track though. Try a WHERE...IN statement:
SELECT * FROM contacts WHERE state='CA' AND name IN ('bob','sam','sara');
--
/Daniel P. Brown
daniel.br...@parasane.net || danbr...@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
We now offer SAME-DAY SETUP on a new line of servers!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I would like to post on lists.mysql.com
On Sat, Jan 30, 2010 at 01:49, Vikram A vikkiatb...@yahoo.in wrote: Dear Admin, I would like to share and get inputs from experts on MYSQL Db. I request you to grant access to me. You may not have noticed, but you're already posting to the list. All you have to do is subscribe and you have full access. -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ Looking for hosting or dedicated servers? Ask me how we can fit your budget! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I would like to post on lists.mysql.com
Hi, if you did your subscription in any lists, you will automatically added to send and receive e-mails from professionals that are connected in it. See lists here: http://lists.mysql.com/ Wagner Bianchi 2010/1/30 Daniel Brown danbr...@php.net On Sat, Jan 30, 2010 at 01:49, Vikram A vikkiatb...@yahoo.in wrote: Dear Admin, I would like to share and get inputs from experts on MYSQL Db. I request you to grant access to me. You may not have noticed, but you're already posting to the list. All you have to do is subscribe and you have full access. -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ Looking for hosting or dedicated servers? Ask me how we can fit your budget! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
I would like to post on lists.mysql.com
Dear Admin, I would like to share and get inputs from experts on MYSQL Db. I request you to grant access to me. Thank you Regards, Vikki A Your Mail works best with the New Yahoo Optimized IE8. Get it NOW! http://downloads.yahoo.com/in/internetexplorer/
Re: Like Syntax
Have you considered Reading The *Fine* Manual at http://dev.mysql.com/doc/#manual ? On Sat, Dec 5, 2009 at 4:59 PM, Victor Subervi victorsube...@gmail.comwrote: Hi; I remember vaguely how to do this but don't know how to google it: show tables like categories$; such that it will return tables such as: categoriesProducts, categoriesPrescriptions, etc. TIA, Victor
Like Syntax
Hi; I remember vaguely how to do this but don't know how to google it: show tables like categories$; such that it will return tables such as: categoriesProducts, categoriesPrescriptions, etc. TIA, Victor
Re: Like Syntax
On Sat, Dec 5, 2009 at 11:09 AM, Michael Dykman mdyk...@gmail.com wrote: show tables like 'categories%'; Thanks. V
Select clause using from and to (like rownum in Oracle)
Hi All, I am facing a problem in porting an application written for oracle to run on mysql. The application uses a sqlmap (ibatis) at the heart of which is basically a file that defines all sql's used in the application. It is very well organized this way. The application uses Oracle as the database. The problem is that for pagination purposes the sql's written use rownum and accept 2 arguments - the from rownum and the to rownum. I am trying to run the same application on my laptop that runs mysql. I have migrated all data and all the sql queries work perfectly except the one that use pagination and the rownum. I know in mysql there is support for sql using the LIMIT clause, but the LIMIT seems to take 2 arguments, the first one being the start rownum and the second being the number of rows to output. I need the second to be the to rownum. I have done a lot of googling, but apart from just putting a rownum for the sql output there was no real usages for pagination purposes. I cannot use the LIMIT as it is in mysql, because that would mean I would have to change the application logic which I do not want to do. I also do not want to install Oracle on my laptop, just too heavy. I have found this to work except I am not sure how to pass a where clause for the rownum part: SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t I was trying something like: SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t where r.rownum between 10, 20; or even SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t where r.rownum=1; I get the error: ERROR 1054 (42S22): Unknown column 'r.rownum' in 'where clause' Is there anyway the SELECT query can be forced to use the from and to rownum parameters? Thanks a lot for any help, Anoop
Re: Select clause using from and to (like rownum in Oracle)
Is there anyway the SELECT query can be forced to use the from and to rownum parameters? 1st LIMIT arg = OracleFromArg 2nd LIMIT arg = OracleToArg - OracleFromArg + 1 so 'from 11 to 20' becomes LIMIT 11,10. PB - Anoop kumar V wrote: Hi All, I am facing a problem in porting an application written for oracle to run on mysql. The application uses a sqlmap (ibatis) at the heart of which is basically a file that defines all sql's used in the application. It is very well organized this way. The application uses Oracle as the database. The problem is that for pagination purposes the sql's written use rownum and accept 2 arguments - the from rownum and the to rownum. I am trying to run the same application on my laptop that runs mysql. I have migrated all data and all the sql queries work perfectly except the one that use pagination and the rownum. I know in mysql there is support for sql using the LIMIT clause, but the LIMIT seems to take 2 arguments, the first one being the start rownum and the second being the number of rows to output. I need the second to be the to rownum. I have done a lot of googling, but apart from just putting a rownum for the sql output there was no real usages for pagination purposes. I cannot use the LIMIT as it is in mysql, because that would mean I would have to change the application logic which I do not want to do. I also do not want to install Oracle on my laptop, just too heavy. I have found this to work except I am not sure how to pass a where clause for the rownum part: SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t I was trying something like: SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t where r.rownum between 10, 20; or even SELECT @rownum:=...@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, user_approvers t where r.rownum=1; I get the error: ERROR 1054 (42S22): Unknown column 'r.rownum' in 'where clause' Is there anyway the SELECT query can be forced to use the from and to rownum parameters? Thanks a lot for any help, Anoop No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.63/2317 - Release Date: 08/21/09 06:04:00
Select clause using from and to (like rownum in Oracle)
Never mind. I got it to work.. I had to really trim down the entire statement: set @sql = concat( select iams_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from user_approvers limit , #from#, ,, (#from#-#to#+1) ); prepare stmt from @sql; execute stmt; drop prepare stmt; But I am not able to use it as a sqlmapped statement in iBatis, but that is a separate problem for a different user list.. but you gave me the idea so far and it works. Thanks very much. Thanks, Anoop On Fri, Aug 21, 2009 at 8:26 PM, Anoop kumar V anoopkum...@gmail.comwrote: I am having trouble executing what you have sent. Below is output mysql set @sql = concat( select iams_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select iams_id ,division_name ,region_name ,isactive from user_approvers ) order by rn limit , 10, ,, (20-10+1) ); Query OK, 0 rows affected (0.03 sec) mysql prepare stmt from @sql; ERROR 1248 (42000): Every derived table must have its own alias mysql execute stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE mysql drop prepare stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE mysql mysql set @sql = concat( select iams_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select iams_id ,division_name ,region_name ,isactive from user_approvers ) a order by rn limit , 10, ,, (20-10+1) ); Query OK, 0 rows affected (0.00 sec) mysql prepare stmt from @sql; ERROR 1054 (42S22): Unknown column 'rn' in 'order clause' mysql execute stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE mysql drop prepare stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE mysql mysql set @sql = concat( select iams_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select iams_id ,division_name ,region_name ,isactive from user_approvers ) a limit , 10, ,, (20-10+1) ); Query OK, 0 rows affected (0.00 sec) mysql prepare stmt from @sql; ERROR 1064 (42000): 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 'limit 10,11' at line 13 mysql execute stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to EXECUTE mysql drop prepare stmt; ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE mysql mysql Thanks, Anoop On Fri, Aug 21, 2009 at 7:22 PM, Peter Brawley peter.braw...@earthlink.net wrote: I think you'd need to use Prepare, eg replace the query with ... set @sql = concat( select user_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select user_id ,division_name ,region_name ,isactive from user_approvers ) order by rn limit , #from, ,, (#to-#from+1) ); prepare stmt from @sql; execute stmt; drop prepare stmt; PB - Anoop kumar V wrote: Thanks very much Peter. But I think I did figure that much. What I am lacking is the integration of that logic into the sql. The current sql (made for oracle) is like this - I can change it all I want because of the sql map which is configurable... select user_id as iamsId ,division_name as divisionName ,region_name as regionName ,isactive as isActive from ( select user_id ,division_name ,region_name ,isactive ,row_number() over (order by division_name, region_name) rn from user_approvers ) where rn between #from# and #to# order by rn I can change everything but the parameters to the sql: #from# and #to#. These come from the application logic and is user enterred (not directly, but through pagination etc - you get the idea) I tried things like the following (to get rows from 11 to 20): select * from user_approvers limit 10, 20-10; Also tried assigning variables.. still no go
Re: How to use LIKE for detecting numbers with commas?
LIKE '%,8,%' ? Probably not as elegant as you were looking for, but it works :) Colin On Monday 06 July 2009 21:31:51 Highviews wrote: Hi, I have numbers separated with commas saved into a TEXT Field, for example: ROW1: 10,5,2,8, ROW2: 2,7,9,65 ROW3: 99,100,55,10,88, etc... Now i want to make a query like this: SELECT * FROM table where numbers LIKE '%8%'; The above query when executed returned the following: ROW1: 10,5,2,8, ROW3: 99,100,55,10,88, Where it should only return ROW1: ROW1: 10,5,2,8, But it is also detecting '88' from ROW2. Any solution to this? I only want exact numbers to be searched out. Thanks! --- http://www.visualbooks.com.pk/ -- Your love life will be... interesting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use LIKE for detecting numbers with commas?
Thats Great!
Thanks a Ton!
:)
On Mon, Jul 6, 2009 at 7:55 PM, Dan Nelson dnel...@allantgroup.com wrote:
In the last episode (Jul 06), avrom...@whyisitthat.com said:
From: Highviews highvi...@gmail.com
I have numbers separated with commas saved into a TEXT Field, for
example:
ROW1: 10,5,2,8,
ROW2: 2,7,9,65
ROW3: 99,100,55,10,88,
etc...
Now i want to make a query like this:
SELECT * FROM table where numbers LIKE '%8%';
Any solution to this?
I only want exact numbers to be searched out.
It's ugly, but this should work:
SELECT * FROM table where numbers LIKE '8,%' or numbers LIKE '%,8,%' or
numbers LIKE '%,8'
Even better:
SELECT * FROM table WHERE find_in_set('8',numbers);
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set
--
Dan Nelson
dnel...@allantgroup.com
How to use LIKE for detecting numbers with commas?
Hi, I have numbers separated with commas saved into a TEXT Field, for example: ROW1: 10,5,2,8, ROW2: 2,7,9,65 ROW3: 99,100,55,10,88, etc... Now i want to make a query like this: SELECT * FROM table where numbers LIKE '%8%'; The above query when executed returned the following: ROW1: 10,5,2,8, ROW3: 99,100,55,10,88, Where it should only return ROW1: ROW1: 10,5,2,8, But it is also detecting '88' from ROW2. Any solution to this? I only want exact numbers to be searched out. Thanks! --- http://www.visualbooks.com.pk/
Re: How to use LIKE for detecting numbers with commas?
It's ugly, but this should work: SELECT * FROM table where numbers LIKE '8,%' or numbers LIKE '%,8,%' or numbers LIKE '%,8' -- B - Original Message - From: Highviews highvi...@gmail.com To: mysql@lists.mysql.com Sent: Monday, July 06, 2009 6:31 PM Subject: How to use LIKE for detecting numbers with commas? Hi, I have numbers separated with commas saved into a TEXT Field, for example: ROW1: 10,5,2,8, ROW2: 2,7,9,65 ROW3: 99,100,55,10,88, etc... Now i want to make a query like this: SELECT * FROM table where numbers LIKE '%8%'; The above query when executed returned the following: ROW1: 10,5,2,8, ROW3: 99,100,55,10,88, Where it should only return ROW1: ROW1: 10,5,2,8, But it is also detecting '88' from ROW2. Any solution to this? I only want exact numbers to be searched out. Thanks! --- http://www.visualbooks.com.pk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use LIKE for detecting numbers with commas?
In the last episode (Jul 06), avrom...@whyisitthat.com said:
From: Highviews highvi...@gmail.com
I have numbers separated with commas saved into a TEXT Field, for
example:
ROW1: 10,5,2,8,
ROW2: 2,7,9,65
ROW3: 99,100,55,10,88,
etc...
Now i want to make a query like this:
SELECT * FROM table where numbers LIKE '%8%';
Any solution to this?
I only want exact numbers to be searched out.
It's ugly, but this should work:
SELECT * FROM table where numbers LIKE '8,%' or numbers LIKE '%,8,%' or
numbers LIKE '%,8'
Even better:
SELECT * FROM table WHERE find_in_set('8',numbers);
http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select field with multiple values using LIKE
AFAIK, repeated LIKEs.
On Tue, Mar 24, 2009 at 6:24 AM, Yariv Omer yar...@jungo.com wrote:
Hi
when I am using a query for several field's values I am using the following
query:
Select field from table where in ('11', '22')
I need to do a LIKE search (not exact match but like match)
How can I do it
Thanks, Yariv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
--
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.
Select field with multiple values using LIKE
Hi
when I am using a query for several field's values I am using the
following query:
Select field from table where in ('11', '22')
I need to do a LIKE search (not exact match but like match)
How can I do it
Thanks, Yariv
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
like isn't behave as expected
Hi i have one row in the cpe_id column of the cpe_users table in my database with the value: d\d. when i am doing:select cpe_id from cpe_users where cpe_id = 'd\\d' I got the one result. when i am doing:select cpe_id from cpe_users where cpe_id like 'd\\d' I don't get any result! why? also when i am doing: when i am doing:select cpe_id from cpe_users where cpe_id = 'dd' I do get the one result why? Thanks, Yariv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: like isn't behave as expected
-Original Message- From: Yariv Omer [mailto:yar...@jungo.com] Sent: Wednesday, February 04, 2009 10:50 AM To: mysql@lists.mysql.com Subject: like isn't behave as expected Hi i have one row in the cpe_id column of the cpe_users table in my database with the value: d\d. when i am doing:select cpe_id from cpe_users where cpe_id = 'd\\d' I got the one result. when i am doing:select cpe_id from cpe_users where cpe_id like 'd\\d' I don't get any result! why? [JS] Strings that are used in LIKE operations are parsed twice, so you need four back-slashes. That's just the way it works. also when i am doing: when i am doing:select cpe_id from cpe_users where cpe_id = 'dd' I do get the one result why? Thanks, Yariv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: like isn't behave as expected
From http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#oper ator_like: Note Because MySQL uses C escape syntax in strings (for example, ³\n² to represent a newline character), you must double any ³\² that you use in LIKE strings. For example, to search for ³\n², specify it as ³\\n². To search for ³\², specify it as ³²; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. (Exception: At the end of the pattern string, backslash can be specified as ³\\². At the end of the string, backslash stands for itself because there is nothing following to escape.) On 2/4/09 10:49 AM, Yariv Omer yar...@jungo.com wrote: Hi i have one row in the cpe_id column of the cpe_users table in my database with the value: d\d. when i am doing:select cpe_id from cpe_users where cpe_id = 'd\\d' I got the one result. when i am doing:select cpe_id from cpe_users where cpe_id like 'd\\d' I don't get any result! why? also when i am doing: when i am doing:select cpe_id from cpe_users where cpe_id = 'dd' I do get the one result why? Thanks, Yariv - Olaf Stein DBA Battelle Center for Mathematical Medicine Nationwide Children's Hospital, The Research Institute 700 Children's Drive 43205 Columbus, OH phone: 1-614-355-5685 cell: 1-614-843-0432 email: olaf.st...@nationwidechildrens.org ³I consider that the golden rule requires that if I like a program I must share it with other people who like it.² Richard M. Stallman - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Average Rating, like Netflix
Pretend I'm Netflix and I want to return a list of found movies, including the average of related ratings for each movie. Something like this: select movies.*, average(ratings.rating) from movies, ratings where movies.movie_id=ratings.movie_id I'm sure that's wrong in about 10 different ways but hopefully you get what I'm trying to do. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Average Rating, like Netflix
If I did the left join to include movies with no ratings, how would I tell if it had no ratings? If I used mysql_fetch_array in PHP, would $result['rating'] == 0, or '', or NULL, or what? On Dec 22, 2008, at 9:29 AM, Brent Baisley wrote: The biggest problem is your join condition (and no group by). It's fine for MySQLv4, but things have changed in v5. You should start getting in the habit of moving the join filters from the WHERE clause to a specific JOIN condition. Use the WHERE clause to perform filters after the join occurs. For example: SELECT movies.* average(ratings.rating) FROM movies INNER JOIN ratings ON movies.movie_id=ratings.movie_id GROUP BY movies.movie_id Change the INNER JOIN to a LEFT JOIN if you want all movies, even those with no ratings. Brent Baisley On Mon, Dec 22, 2008 at 11:13 AM, Brian Dunning br...@briandunning.com wrote: Pretend I'm Netflix and I want to return a list of found movies, including the average of related ratings for each movie. Something like this: select movies.*, average(ratings.rating) from movies, ratings where movies.movie_id=ratings.movie_id I'm sure that's wrong in about 10 different ways but hopefully you get what I'm trying to do. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=brentt...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Average Rating, like Netflix
The ratings field would be NULL. You could also add a count in your query to tell how many ratings there were. If count is 0, you know there are no ratings. SELECT count(ratings.rating_id) AS rate_count, ... Brent Baisley On Mon, Dec 22, 2008 at 12:39 PM, Brian Dunning br...@briandunning.com wrote: If I did the left join to include movies with no ratings, how would I tell if it had no ratings? If I used mysql_fetch_array in PHP, would $result['rating'] == 0, or '', or NULL, or what? On Dec 22, 2008, at 9:29 AM, Brent Baisley wrote: The biggest problem is your join condition (and no group by). It's fine for MySQLv4, but things have changed in v5. You should start getting in the habit of moving the join filters from the WHERE clause to a specific JOIN condition. Use the WHERE clause to perform filters after the join occurs. For example: SELECT movies.* average(ratings.rating) FROM movies INNER JOIN ratings ON movies.movie_id=ratings.movie_id GROUP BY movies.movie_id Change the INNER JOIN to a LEFT JOIN if you want all movies, even those with no ratings. Brent Baisley On Mon, Dec 22, 2008 at 11:13 AM, Brian Dunning br...@briandunning.com wrote: Pretend I'm Netflix and I want to return a list of found movies, including the average of related ratings for each movie. Something like this: select movies.*, average(ratings.rating) from movies, ratings where movies.movie_id=ratings.movie_id I'm sure that's wrong in about 10 different ways but hopefully you get what I'm trying to do. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Average Rating, like Netflix
Or you could wrap your entire SELECT in another query, and do an IFNULL around the rating field to convert it to 0 (or some other value important to you) as follows: SELECT movie_id, ... any other fields from movies table you want ..., IFNULL(ratings, 0) AS rating FROM ( SELECT movies.*, average(ratings.rating) AS rating FROM movies LEFT JOIN ratings ON movies.movie_id=ratings.movie_id GROUP BY movies.movie_id ) result Andy Brent Baisley wrote: The ratings field would be NULL. You could also add a count in your query to tell how many ratings there were. If count is 0, you know there are no ratings. SELECT count(ratings.rating_id) AS rate_count, ... Brent Baisley On Mon, Dec 22, 2008 at 12:39 PM, Brian Dunning br...@briandunning.com wrote: If I did the left join to include movies with no ratings, how would I tell if it had no ratings? If I used mysql_fetch_array in PHP, would $result['rating'] == 0, or '', or NULL, or what? On Dec 22, 2008, at 9:29 AM, Brent Baisley wrote: The biggest problem is your join condition (and no group by). It's fine for MySQLv4, but things have changed in v5. You should start getting in the habit of moving the join filters from the WHERE clause to a specific JOIN condition. Use the WHERE clause to perform filters after the join occurs. For example: SELECT movies.* average(ratings.rating) FROM movies INNER JOIN ratings ON movies.movie_id=ratings.movie_id GROUP BY movies.movie_id Change the INNER JOIN to a LEFT JOIN if you want all movies, even those with no ratings. Brent Baisley On Mon, Dec 22, 2008 at 11:13 AM, Brian Dunning br...@briandunning.com wrote: Pretend I'm Netflix and I want to return a list of found movies, including the average of related ratings for each movie. Something like this: select movies.*, average(ratings.rating) from movies, ratings where movies.movie_id=ratings.movie_id I'm sure that's wrong in about 10 different ways but hopefully you get what I'm trying to do. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
different results from '=' vs. 'LIKE'
Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results from '=' vs. 'LIKE'
Do you seriously have a column named 0020,0032 ?!!? And don't even get me started on the actual name of these images (column data). Wow. That makes my head hurt. I think mySQL is just punishing you for both of those offenses. *hee hee* ;-p But if I were to venture a guess, and RTFM... http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html I'd say it might be related to casting. If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons. So perhaps in the first case you're dealing with strings, but in the second case you're dealing with numbers? Or possibly you're not escaping your \ enough? To search for “\”, specify it as “”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. I don't know, but that should point you in the right direction and perhaps convince you to rename your column and use a more sane data naming convention... good luck! D.Vin http://daevid.com On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael
Re: different results from '=' vs. 'LIKE'
Also, I realize you're trying to 'encode' some sort of X\Y\Z coordinates in that column, so perhaps a different delimiter such as the pipe | character or , would be more appropriate than a \ which has special meanings? Or possibly just split them out into separate X, Y, Z columns rather than cramming them together like that. This would allow you to do various trig and math functions on them easier (assuming you are storing coordinates for a reason). There are basic SQL 'update' statements you could write to fix your existing data and/or convert it to the new delimiter. This may save you headaches going forward. d. On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael
Re: different results from '=' vs. 'LIKE'
On Dec 9, 2008, at 2:03 PM, Daevid Vincent wrote: Do you seriously have a column named 0020,0032 ?!!? And don't even get me started on the actual name of these images (column data). Jepp, and there are a hell of a lot of more weird number like that. That's an attribute tag from DICOM images. The names might alter, the tag value won't. Wow. That makes my head hurt. I think mySQL is just punishing you for both of those offenses. *hee hee* ;-p But if I were to venture a guess, and RTFM... http://dev.mysql.com/doc/refman/5.1/en/string-comparison- functions.html I'd say it might be related to casting. If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons. So perhaps in the first case you're dealing with strings, but in the second case you're dealing with numbers? Or possibly you're not escaping your \ enough? Yeah, I actually figured that out after writing the first mail. Then I was talking to myselfe for quite a while like: freakin' 4 backslashes just to get one out of in the end, tsss. To search for “\”, specify it as “”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. I don't know, but that should point you in the right direction and perhaps convince you to rename your column and use a more sane data naming convention... good luck! Thx for the feedback and cooperation! :) Cheers, Michael D.Vin http://daevid.com On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results from '=' vs. 'LIKE'
On Dec 9, 2008, at 2:27 PM, Daevid Vincent wrote: Also, I realize you're trying to 'encode' some sort of X\Y\Z coordinates in that column, so perhaps a different delimiter such as the pipe | character or , would be more appropriate than a \ which has special meanings? That's actually how the values are in original. At this stage, I don't want to mess around with it further. But exporting it later to another coordinate-table, if that'll be required, is being thought of. Perhaps the field might also get dropped. (Ups, now the wholly god of db schema design will put rage upon me for bad designing in the first place ... not to mention the NU** values ... :-$) Thx for the hint! :) Cheers, Michael Or possibly just split them out into separate X, Y, Z columns rather than cramming them together like that. This would allow you to do various trig and math functions on them easier (assuming you are storing coordinates for a reason). There are basic SQL 'update' statements you could write to fix your existing data and/or convert it to the new delimiter. This may save you headaches going forward. d. On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REGEXP vs LIKE/OR
Hi, I want to retrieve all records where the field value contains either foo, bar or baz. Like so: SELECT id FROM table WHERE value LIKE '%foo%' OR value LIKE '%bar%' OR value LIKE '%baz%'; But then I stumbled upon REGEXP, and can do the same this way: SELECT id FROM table WHERE value REGEXP 'foo|bar|baz' != 0; Any opinions on what's the better approach and why? Thanks Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: REGEXP vs LIKE/OR
It looks like LIKE is only slightly faster(on my XP), hardly worth mentioning. Go with what is easier for you to read or for portability if you need it. IMHO set @a='gfdueruie baz hdhrh';select BENCHMARK(500, (select 1 from dual WHERE @a LIKE '%foo%' OR @a LIKE '%bar%' OR @a LIKE '%baz%')) as elapse_time; # average 750ms set @a='gfdueruie baz hdhrh';select BENCHMARK(500, (select 1 from dual WHERE @a REGEXP 'foo|bar|baz' != 0)) as elapse_time; # average 770ms Ed -Original Message- From: Morten Primdahl [mailto:[EMAIL PROTECTED] Sent: Friday, August 15, 2008 5:12 AM To: mysql Subject: REGEXP vs LIKE/OR Hi, I want to retrieve all records where the field value contains either foo, bar or baz. Like so: SELECT id FROM table WHERE value LIKE '%foo%' OR value LIKE '%bar%' OR value LIKE '%baz%'; But then I stumbled upon REGEXP, and can do the same this way: SELECT id FROM table WHERE value REGEXP 'foo|bar|baz' != 0; Any opinions on what's the better approach and why? Thanks Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: doubt: mysqldump in linux like windows
Hello
the process of the restore is painful.
i see
even in windows, i dont know why made the backup in that way,
(i dindt change any option to make the backup in the mysql administrator)
if you say that the restore would be painful
thank for your time
Moon's Father wrote:
If you skip the extend insert during mysqldump ,the process of the restore
is painful.
On Fri, Mar 21, 2008 at 5:05 AM, dr_pompeii [EMAIL PROTECTED] wrote:
Hi Rolando
thanks for the reply
it works, thanks,
new command used
mysqldump --opt --skip-extended-insert --password=XXX --user=root somedb
/home/Someuser/somepath/A.sql
but i see one difference
from windows
/*!4 ALTER TABLE `articulo` DISABLE KEYS */;
INSERT INTO `articulo`
(`idArticulo`,`descripcion`,`stockactual`,`precioUnitario`,`precioUnitarioVenta`,`totalValorizado`,`xtraTextUnoArticulo`,`xtraNumDosArticulo`,`idLineaCategoria`,`idMedida`)
VALUES
('1-15W40','ACEITE EXTRAVIDA X GLN
15W40','0.00','0.00','0.00','0.00','','0.00','300','11'),
('1-P0001','CASCOS DE MOTOS
HONDA','0.00','0.00','0.00','0.00','','0.00','300','10'),
('1-P0003','LLANTAS DUNLOP LT
265/75R216','0.00','0.00','0.00','0.00','','0.00','300','10'),
('1-P0014','POLOS
HONDA','0.00','0.00','0.00','0.00','','0.00','300','10'),
now with the new command already shown
i have this way
LOCK TABLES `articulo` WRITE;
/*!4 ALTER TABLE `articulo` DISABLE KEYS */;
INSERT INTO `articulo` VALUES ('1-15W40','ACEITE EXTRAVIDA X GLN
15W40','0.00','0.00','0.00','0.00','','0.00','300','11');
INSERT INTO `articulo` VALUES ('1-CHA01','KIT CHACARERO AZUL
(GDFGO,PORTAF,LLANT-DEL/POST)','0.00','0.00','0.00','0.00','','0.00
','300','14');
INSERT INTO `articulo` VALUES ('1-P0001','CASCOS DE MOTOS
HONDA','0.00','0.00','0.00','0.00','','0.00','300','10');
i need like the windows way, thats mean,
for the first line for insertion before to insert all rows
i need
INSERT INTO `articulo`
(`idArticulo`,`descripcion`,`stockactual`,`precioUnitario`,`precioUnitarioVenta`,`totalValorizado`,`xtraTextUnoArticulo`,`xtraNumDosArticulo`,`idLineaCategoria`,`idMedida`)
VALUES
i tried adding --disable-keys but wierd and undesired results
regards
Rolando Edwards-3 wrote:
Use --skip-extended-insert as another mysqldump option
-Original Message-
From: dr_pompeii [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 20, 2008 2:43 PM
To: mysql@lists.mysql.com
Subject: doubt: mysqldump in linux like windows
Hello guys
i have this situation
in widnows with the mysql administrador i make backup
i saw in the x.sql these lines for example
/*!4 ALTER TABLE `articulo` DISABLE KEYS */;
INSERT INTO `articulo`
(`idArticulo`,`descripcion`,`stockactual`,`precioUnitario`,`precioUnitarioVenta`,`totalValorizado`,`xtraTextUnoArticulo`,`xtraNumDosArticulo`,`idLineaCategoria`,`idMedida`)
VALUES
('1-15W40','ACEITE EXTRAVIDA X GLN
15W40','0.00','0.00','0.00','0.00','','0.00','300','11'),
('1-CHA01','KIT CHACARERO AZUL
(GDFGO,PORTAF,LLANT-DEL/POST)','0.00','0.00','0.00','0.00','','0.00
','300','14'),
('1-P0001','CASCOS DE MOTOS
HONDA','0.00','0.00','0.00','0.00','','0.00','300','10'),
('1-P0003','LLANTAS DUNLOP LT
265/75R216','0.00','0.00','0.00','0.00','','0.00','300','10'),
('1-P0014','POLOS
HONDA','0.00','0.00','0.00','0.00','','0.00','300','10'),
see pls that each row is written in a unique of line of text
now in linux with command in a terminal i do in this way my backups
mysqldump --opt --password=XXX --user=root somedb
/home/Someuser/somepath/A.sql
the backup is done but in this way
/*!4 ALTER TABLE `articulo` DISABLE KEYS */;
INSERT INTO `articulo` VALUES ('1-15W40','ACEITE EXTRAVIDA X GLN
15W40','0.00','0.00','0.00','0.00','','0.00
','300','11'),('1-CHA01','KIT
CHACARERO AZUL
(GDFGO,PORTAF,LLANT-DEL/POST)','0.00','0.00','0.00','0.00','','0.00
','300','14'),('1-P0001','CASCOS
DE
how you can see, all the rows appear in one line,
dangeous, i dont want this behaviour when i open this file in windows
tell
me if i try to save this file i will missing some values or rows
and in linux the gedit dies :(
after to read this
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html mysqldump
i tried in this way
mysqldump --opt --extended-insert--password=XXX --user=root somedb
/home/Someuser/somepath/A.sql
with the same undesired results
how i can resolve this??
thanks in advanced
--
View this message in context:
http://www.nabble.com/doubt%3A-mysqldump-in-linux-like-windows-tp16185833p16185833.html
Sent from the MySQL - General mailing list archive at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http
doubt: mysqldump in linux like windows
Hello guys
i have this situation
in widnows with the mysql administrador i make backup
i saw in the x.sql these lines for example
/*!4 ALTER TABLE `articulo` DISABLE KEYS */;
INSERT INTO `articulo`
(`idArticulo`,`descripcion`,`stockactual`,`precioUnitario`,`precioUnitarioVenta`,`totalValorizado`,`xtraTextUnoArticulo`,`xtraNumDosArticulo`,`idLineaCategoria`,`idMedida`)
VALUES
('1-15W40','ACEITE EXTRAVIDA X GLN
15W40','0.00','0.00','0.00','0.00','','0.00','300','11'),
('1-CHA01','KIT CHACARERO AZUL
(GDFGO,PORTAF,LLANT-DEL/POST)','0.00','0.00','0.00','0.00','','0.00','300','14'),
('1-P0001','CASCOS DE MOTOS
HONDA','0.00','0.00','0.00','0.00','','0.00','300','10'),
('1-P0003','LLANTAS DUNLOP LT
265/75R216','0.00','0.00','0.00','0.00','','0.00','300','10'),
('1-P0014','POLOS
HONDA','0.00','0.00','0.00','0.00','','0.00','300','10'),
see pls that each row is written in a unique of line of text
now in linux with command in a terminal i do in this way my backups
mysqldump --opt --password=XXX --user=root somedb
/home/Someuser/somepath/A.sql
the backup is done but in this way
/*!4 ALTER TABLE `articulo` DISABLE KEYS */;
INSERT INTO `articulo` VALUES ('1-15W40','ACEITE EXTRAVIDA X GLN
15W40','0.00','0.00','0.00','0.00','','0.00','300','11'),('1-CHA01','KIT
CHACARERO AZUL
(GDFGO,PORTAF,LLANT-DEL/POST)','0.00','0.00','0.00','0.00','','0.00','300','14'),('1-P0001','CASCOS
DE
how you can see, all the rows appear in one line,
dangeous, i dont want this behaviour when i open this file in windows tell
me if i try to save this file i will missing some values or rows
and in linux the gedit dies :(
after to read this
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html mysqldump
i tried in this way
mysqldump --opt --extended-insert--password=XXX --user=root somedb
/home/Someuser/somepath/A.sql
with the same undesired results
how i can resolve this??
thanks in advanced
--
View this message in context:
http://www.nabble.com/doubt%3A-mysqldump-in-linux-like-windows-tp16185833p16185833.html
Sent from the MySQL - General mailing list archive at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
