Re: SELECT query question
select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = anything or T2.Source2_Name = anything or T3.Source3_Name = anything Not tested. -- João Cândido de Souza Neto SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS Fone: (0XX41) 3033-3636 - JS www.siens.com.br Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com... Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna -- 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 query question
Should be more efficient to do something like: SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name' UNION SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name' UNION SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name' -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, July 27, 2009 1:09 PM To: mysql@lists.mysql.com Subject: Re: SELECT query question select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = anything or T2.Source2_Name = anything or T3.Source3_Name = anything Not tested. -- João Cândido de Souza Neto SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS Fone: (0XX41) 3033-3636 - JS www.siens.com.br Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com... Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- 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 query question
There are many ways to get the same result. hehehehe Gavin Towey gto...@ffn.com escreveu na mensagem news:30b3df511cec5c4dae4d0d290504753413956dc...@aaa.pmgi.local... Should be more efficient to do something like: SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name' UNION SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name' UNION SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name' -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, July 27, 2009 1:09 PM To: mysql@lists.mysql.com Subject: Re: SELECT query question select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = anything or T2.Source2_Name = anything or T3.Source3_Name = anything Not tested. Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com... Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- 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 query locks tables in Innodb
2009/3/12 Carl c...@etrak-plus.com: I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. What's the relationship you're implying between memory and locking? Multi-version concurrency doesn't necessarily mean the older versions that are being read from have to be entirely in memory. InnoDB will lock on a query that doesn't use an index. It shouldn't lock on a SELECT query, regardless of the indexes involved. - Perrin -- 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 query locks tables in Innodb
Brent, After a delay while I was busy killing alligators, I did as you suggested (added a composite index of date and organization_serial on journal_entry_master... in the spirit of your suggestion, anyway.) The results were interesting: 1. In my test environment, I could not force a locked file even though I opened the dates up to cover 2+ years and changed to an organization that had more records. The 'Explain' is attached as temp1.txt. You will note that it starts with 100,000+ records while the eventual set of records for the report is 60,000 because the 100,000+ number includes some journmal entries for refund/void/etc. transactions which we have no interest in. 2. I tried various combinations of indexes but couldn't seem to get any better than the composite one on the journal_entry_master. I did not check whether the other options would produce locked files. I am now going to put this into production and see if it will actually fly. I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. Thanks for all your help and Baron's suggestions also. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Thursday, March 05, 2009 1:12 PM Subject: Re: Select query locks tables in Innodb Ok, so you have 687 unique organization serial numbers. That's not very unique, on average it will only narrow down the table to 1/687 of it's full size. This is probably the source of your locking problem and where you want to focus. InnoDB will lock on a query that doesn't use an index. It would have to lock every record anyway, so why not lock the table? 36,000 records still may be too large of a result set to do record versioning. But, optimizing your query is the only way to go. Your date_effective is a lot more granular, so you may want to focus on that. If you do a lot of these types of searches, you can try creating a compound index on organization_serial+date_effective. CREATE INDEX (org_date) ON journal_entry_master(organization_serial,date_effective) MySQL would/should then use that query, which will narrow things down quicker and better. It shouldn't have to try to do versioning on 56,000 records while it tries to get the subset of that (36,000). Brent On Thu, Mar 5, 2009 at 6:02 AM, Carl c...@etrak-plus.com wrote: Brent, The query returns about 36,000 rows. The 56,000 rows from the journal_entry_master table is all the entries for organization 16 (they span more than the dates I have asked for.) SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary index (auto-increment), 687 for the organization_serial (the one I am using), 18 for the organization_shift_start (I tried this before, i.e., starting with the organization_shift, but it quickly got mired down) and 777,000+ for the date_effective. If I understand correctly, you have suggested using the date index. The difficulty is the data contains many organizations and so the date range query returns 163,000+ rows. Also, I would expect scaling a query where I had to programatically cut it up would 1) be difficult and 2) wouldn't really solve the problem but would rather just shorten the time of the locks. I am not suggesting that I might not end up there, only hoping for a better solution. Thanks for all your insight and feel free to suggest away. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Wednesday, March 04, 2009 4:23 PM Subject: Re: Select query locks tables in Innodb Is the result of the query returning 56,000+ rows? How many rows are you expecting to be returned once the query is finished running? Your date range is over a year. You may actually get much better performance (and avoid locking) by running more queries with a narrower date range and linking them through a UNION. It's using the organization index rather than the date index. I don't know your dataset, but typically you want your query to use the date index since that narrows down the data set better. You can run SHOW INDEX FROM journal_entry_master to see the distribution of your data in the index. The cardinality column will indicate the uniqueness of your data. The higher the number, the more unique values. Brent 2009/3/4 Carl c...@etrak-plus.com: Under stress (having transaction entered), the query shows that it is still locking the tables. I rewrote the query and tested it step by step but could not tell whether tyhe partially complete query was locking tables because it ran so fast. However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. Does anyone
Re: Select query locks tables in Innodb
The nice thing about InnnoDB is that it won't have to access the data portion of the file if it doesn't have to. So if all the information you are retrieving is contained in an index, it only accesses the index to get the information it needs. The data portion is never access, and thus never locked. Something like this is probably going on. All the information it needs for the 100,000 records is contained in the index, the the data portion is never accessed until it needs to retrieve the 60,000 records. That's a simplistic overview of what could be going on. But it sounds like your issue has been resolved. Interesting, your temp1 attached file shows mysql switched from using the org_date index to the organization index. Brent Baisley 2009/3/12 Carl c...@etrak-plus.com: Brent, After a delay while I was busy killing alligators, I did as you suggested (added a composite index of date and organization_serial on journal_entry_master... in the spirit of your suggestion, anyway.) The results were interesting: 1. In my test environment, I could not force a locked file even though I opened the dates up to cover 2+ years and changed to an organization that had more records. The 'Explain' is attached as temp1.txt. You will note that it starts with 100,000+ records while the eventual set of records for the report is 60,000 because the 100,000+ number includes some journmal entries for refund/void/etc. transactions which we have no interest in. 2. I tried various combinations of indexes but couldn't seem to get any better than the composite one on the journal_entry_master. I did not check whether the other options would produce locked files. I am now going to put this into production and see if it will actually fly. I am still a little puzzled about how we could have a relatively large set of records (100,000+) and yet not cause any table to be locked as the server has only 8GB of memory. Thanks for all your help and Baron's suggestions also. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Thursday, March 05, 2009 1:12 PM Subject: Re: Select query locks tables in Innodb Ok, so you have 687 unique organization serial numbers. That's not very unique, on average it will only narrow down the table to 1/687 of it's full size. This is probably the source of your locking problem and where you want to focus. InnoDB will lock on a query that doesn't use an index. It would have to lock every record anyway, so why not lock the table? 36,000 records still may be too large of a result set to do record versioning. But, optimizing your query is the only way to go. Your date_effective is a lot more granular, so you may want to focus on that. If you do a lot of these types of searches, you can try creating a compound index on organization_serial+date_effective. CREATE INDEX (org_date) ON journal_entry_master(organization_serial,date_effective) MySQL would/should then use that query, which will narrow things down quicker and better. It shouldn't have to try to do versioning on 56,000 records while it tries to get the subset of that (36,000). Brent On Thu, Mar 5, 2009 at 6:02 AM, Carl c...@etrak-plus.com wrote: Brent, The query returns about 36,000 rows. The 56,000 rows from the journal_entry_master table is all the entries for organization 16 (they span more than the dates I have asked for.) SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary index (auto-increment), 687 for the organization_serial (the one I am using), 18 for the organization_shift_start (I tried this before, i.e., starting with the organization_shift, but it quickly got mired down) and 777,000+ for the date_effective. If I understand correctly, you have suggested using the date index. The difficulty is the data contains many organizations and so the date range query returns 163,000+ rows. Also, I would expect scaling a query where I had to programatically cut it up would 1) be difficult and 2) wouldn't really solve the problem but would rather just shorten the time of the locks. I am not suggesting that I might not end up there, only hoping for a better solution. Thanks for all your insight and feel free to suggest away. Carl - Original Message - From: Brent Baisley brentt...@gmail.com To: Carl c...@etrak-plus.com Sent: Wednesday, March 04, 2009 4:23 PM Subject: Re: Select query locks tables in Innodb Is the result of the query returning 56,000+ rows? How many rows are you expecting to be returned once the query is finished running? Your date range is over a year. You may actually get much better performance (and avoid locking) by running more queries with a narrower date range and linking them through a UNION. It's using the organization index rather than the date index. I don't know your dataset, but typically you want your query to use the date index since
Re: Select query locks tables in Innodb
I really appreciate the time you have taken to help me with this problem.
I will be out of the office until around 1:00PM and will try your
suggestions.
I did attach a copy of the query but it may have been stripped somewhere
along the line so I have placed it in line below.
select *
from payment_to_fee_link_budget_account_detail_link, journal_entry_master,
journal_entry_type,
payment_to_fee_link_event, payment_to_fee_link, fees, fees_event,
fees_budget_account_detail_link, person, transactions
left join regs on regs.transactions_serial =
transactions.transactions_serial,
transaction_event, receipt_master, budget_account_detail,
budget_account_detail as ptfl_budget_account_detail, budget_account_master
where journal_entry_master.organization_serial = 16
and journal_entry_master.date_effective = '2008-01-01'
and journal_entry_master.date_effective '2009-03-31'
and journal_entry_type.journal_entry_type_serial =
journal_entry_master.journal_entry_type_serial
and
payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial =
journal_entry_master.journal_entry_master_serial
and payment_to_fee_link_budget_account_detail_link.date_effective =
'2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective
'2009-03-31'
and payment_to_fee_link_event.payment_to_fee_link_event_serial =
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
and payment_to_fee_link.payment_to_fee_link_serial =
payment_to_fee_link_event.payment_to_fee_link_serial
and transaction_event.transaction_event_serial =
payment_to_fee_link_event.transaction_event_serial
and fees.fees_serial = payment_to_fee_link.fees_serial
and transactions.transactions_serial = fees.transactions_serial
and person.person_serial = transactions.person_serial
and receipt_master.receipt_serial = transaction_event.receipt_serial
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial =
fees_event.fees_event_serial
and budget_account_detail.budget_account_detail_serial =
fees_budget_account_detail_link.budget_account_detail_serial
and ptfl_budget_account_detail.budget_account_detail_serial =
payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial
and budget_account_master.budget_account_serial =
budget_account_detail.budget_account_serial
and budget_account_master.budget_account_type_serial = 5001
TIA,
Carl
- Original Message -
From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 8:11 PM
Subject: Re: Select query locks tables in Innodb
I don't think it locks the tables. The behavior may be similar, but I
seriously doubt that's what's happening. Take a snapshot of SHOW
INNODB STATUS while this is going on. And use mysqladmin debug and
check the error log. Then put those in some pastebin and send us the
link. And realize that you've only given us bits and snippets of
information about this -- you still haven't given us SHOW CREATE TABLE
or even shown us the query that's running. There's not a lot I can do
to really help you with this other than assume that you are wrong :)
Your version is definitely affected by that bug, which I can't find --
I am using the wrong search terms and can't find the right ones to
find the bug.
5.0.37 is a very buggy version and I would upgrade regardless if I
were you, to the latest 5.0 release. You might be surprised at how
much that changes things.
Baron
On Wed, Mar 4, 2009 at 3:33 PM, Carl c...@etrak-plus.com wrote:
Baron,
I am using 5.0.37.
While it may be true that there is a bug that shows tables as being locked
when they really aren't, I do not think that applies here. I do know that
when a table shows a status of 'Locked' in the Navicat Server Monitor that
the transaction which created and is processing the query comes to a
complete stop until the report query (the one I am trying to straighten
out
or understand) is finished. For example, the report query is reading from
several files, e.g., receipt_master, if a user tries to check out (which
requires an insert into the receipt_master table), they are stopped until
the report query finishes and query on that table shows in Navicat as
waiting for lock ('Locked'.)
Since the report query is only reading data, I am puzzled why it locks the
tables. Any ideas?
TIA,
Carl
- Original Message - From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you
Re: Select query locks tables in Innodb
Thanks to all of you.
The key was the 107488 rows. I restructured the query so that it started
with something smaller and it 1) runs faster (I'm guessing the reduced use
of temp space) and 2) did not seem to cause any locking problems (I will
test this under load today.)
I have attached a copy of the query which has been simplified in a couple of
ways (I don't really want every field from every row selected from every
table.) Also, the constants like organization_serial (16) and dates are
variables in the real version.
The explain now shows:
idtable typepossible_keys
keylenref rows
1organization_shiftrefPRIMARY, organizationorganization
4const5
1organization_shift_start ref PRIMARY, organization_shift
organization_shift4 organization_shift_serial295
1journal_entry_masterrefPRIMARY, organization_shift_start
organization_shift_start 5 organization_shift_start_serial 52
Note that it now starts with 5 row, expands to 295 rows, etc. not the
100,000+ from before.
Again, thanks for all your help.
Carl
- Original Message -
From: Baron Schwartz ba...@xaprb.com
To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com
wrote:
A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows part of the
query. That's too many rows for InnoDB to keep a version history on so
it's likely just locking the table.
InnoDB does not do lock escalation a la SQL Server etc. I'd look at
Perrin's suggestions, I think they are likely to be the problem.
More importantly, what is the query? :-)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com
# sales from collections
(select *
from organization_shift, organization_shift_start, transaction_event,payment_to_fee_link_event,payment_to_fee_link, payment_to_fee_link_budget_account_detail_link,fees_budget_account_detail_link, fees_event, budget_account_detail,
payments, budget_account_detail as ptfl_budget_account_detail, budget_account_master, journal_entry_master, journal_entry_type, receipt_master, person, transactions
left join regs on regs.transactions_serial =
transactions.transactions_serial
where organization_shift.organization_serial = 16
and organization_shift_start.organization_shift_serial =
organization_shift.organization_shift_serial
and organization_shift_start.date_effective = '2008-01-01'
and organization_shift_start.date_effective '2009-03-31'
#$P!{organizationShiftStartQuery}
and journal_entry_master.organization_shift_start_serial =
organization_shift_start.organization_shift_start_serial
and receipt_master.receipt_serial =
transaction_event.receipt_serial
and transactions.transactions_serial =
transaction_event.transactions_serial
and transactions.organization_serial = organization_shift.organization_serial
#$P!{itemSerials}
and person.person_serial = transactions.person_serial
and payment_to_fee_link_event.transaction_event_serial =
transaction_event.transaction_event_serial
and
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
= payment_to_fee_link_event.payment_to_fee_link_event_serial
and
payment_to_fee_link_budget_account_detail_link.cash_basis_reporting_flag = 'Y'
and payment_to_fee_link.payment_to_fee_link_serial =
payment_to_fee_link_event.payment_to_fee_link_serial
and payments.payments_serial =
payment_to_fee_link.payments_serial
and payment_to_fee_link_budget_account_detail_link.date_effective
= '2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective
'2009-03-31'
and (payments.payment_type_code_serial in (
1,2,3,4,5,8,24,6,7,12,13,23,25 )# 1,2,3,4,5,8,24,6,7,12,13,23,25
or
payment_to_fee_link_budget_account_detail_link.description='Apply available
credit to customer accounts receivable')
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial =
fees_event.fees_event_serial
and
fees_budget_account_detail_link.budget_account_detail_serial
Re: Select query locks tables in Innodb
Under stress (having transaction entered), the query shows that it is still locking the tables. I rewrote the query and tested it step by step but could not tell whether tyhe partially complete query was locking tables because it ran so fast. However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. Does anyone have any ideas? TIA, Carl - Original Message - From: Baron Schwartz ba...@xaprb.com To: Brent Baisley brentt...@gmail.com Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com wrote: A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. InnoDB does not do lock escalation a la SQL Server etc. I'd look at Perrin's suggestions, I think they are likely to be the problem. More importantly, what is the query? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com select * from payment_to_fee_link_budget_account_detail_link, journal_entry_master, journal_entry_type, payment_to_fee_link_event, payment_to_fee_link, fees, fees_event, fees_budget_account_detail_link, person, transactions left join regs on regs.transactions_serial = transactions.transactions_serial, transaction_event, receipt_master, budget_account_detail, budget_account_detail as ptfl_budget_account_detail, budget_account_master where journal_entry_master.organization_serial = 16 and journal_entry_master.date_effective = '2008-01-01' and journal_entry_master.date_effective '2009-03-31' and journal_entry_type.journal_entry_type_serial = journal_entry_master.journal_entry_type_serial and payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial = journal_entry_master.journal_entry_master_serial and payment_to_fee_link_budget_account_detail_link.date_effective = '2008-01-01' and payment_to_fee_link_budget_account_detail_link.date_effective '2009-03-31' and payment_to_fee_link_event.payment_to_fee_link_event_serial = payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial and payment_to_fee_link.payment_to_fee_link_serial = payment_to_fee_link_event.payment_to_fee_link_serial and transaction_event.transaction_event_serial = payment_to_fee_link_event.transaction_event_serial and fees.fees_serial = payment_to_fee_link.fees_serial and transactions.transactions_serial = fees.transactions_serial and person.person_serial = transactions.person_serial and receipt_master.receipt_serial = transaction_event.receipt_serial and fees_event.fees_serial = payment_to_fee_link.fees_serial and ( fees_event.transaction_event_description_serial = 13 or fees_event.transaction_event_description_serial = 2 ) and fees_budget_account_detail_link.fees_event_serial = fees_event.fees_event_serial and budget_account_detail.budget_account_detail_serial = fees_budget_account_detail_link.budget_account_detail_serial and ptfl_budget_account_detail.budget_account_detail_serial = payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial and budget_account_master.budget_account_serial = budget_account_detail.budget_account_serial and budget_account_master.budget_account_type_serial = 5001 temp.XLS Description: MS-Excel spreadsheet 1 SIMPLE journal_entry_masterref PRIMARY,organization,journal_entry_type_serial,date_effective organization 4 const 56926 Using where 1 SIMPLE journal_entry_type eq_ref PRIMARY PRIMARY 4 PRODUCTION.journal_entry_master.journal_entry_type_serial 1 1 SIMPLE payment_to_fee_link_budget_account_detail_link ref journal_entry,budget_account_detail_serial,event,date_effective journal_entry 4 PRODUCTION.journal_entry_master.journal_entry_master_serial 1 Using where 1 SIMPLE ptfl_budget_account_detail eq_ref PRIMARY PRIMARY 4 PRODUCTION.payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial 1 1 SIMPLE
Re: Select query locks tables in Innodb
2009/3/4 Carl c...@etrak-plus.com: However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. I don't know what that is, but I think you'd better look at something closer to the bone, like SHOW INNODB STATUS. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. EXPLAIN isn't really relevant to table locking. InnoDB tables should never let readers block writers for a simple SELECT. Does anyone have any ideas? Did you check that your tables are InnoDB? Are you running some kind of crazy isolation level? - Perrin -- 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 query locks tables in Innodb
One more note. Perrin asked if I was using any select... for update. The answer is no, neither in the select query that seems to be locking the tables nor in the queries that are processing transactions. Surprisingly, one of the tables that reports being locked is never accessed in the report query. It is a foreign key on one of the files that is used. TIA, Carl - Original Message - From: Baron Schwartz ba...@xaprb.com To: Brent Baisley brentt...@gmail.com Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com wrote: A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. InnoDB does not do lock escalation a la SQL Server etc. I'd look at Perrin's suggestions, I think they are likely to be the problem. More importantly, what is the query? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=c...@etrak-plus.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 query locks tables in Innodb
I did check that all tables are Innodb. I was using the Navicat Server Monitor because I know that when I see the monitor reporting a status of locked during an attempted query, that user comes to a complete halt until the lock is cleared (usually by the bad query finishing.) I will check the isolation level but I believe it is whatever was set out of the box (five years ago.) Thanks, Carl - Original Message - From: Perrin Harkins per...@elem.com To: Carl c...@etrak-plus.com Cc: mysql@lists.mysql.com Sent: Wednesday, March 04, 2009 1:49 PM Subject: Re: Select query locks tables in Innodb 2009/3/4 Carl c...@etrak-plus.com: However, when I had all the pieces in the query (copy attached), I could easily see it was locking tables using the Server Monitor in Navicat. I don't know what that is, but I think you'd better look at something closer to the bone, like SHOW INNODB STATUS. Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows. EXPLAIN isn't really relevant to table locking. InnoDB tables should never let readers block writers for a simple SELECT. Does anyone have any ideas? Did you check that your tables are InnoDB? Are you running some kind of crazy isolation level? - Perrin -- 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 query locks tables in Innodb
Carl, Locked status in SHOW PROCESSLIST and a table being locked are different. There is a bug in MySQL that shows Locked status for queries accessing InnoDB tables in some cases. What version of MySQL are you using? The table is not really locked, you're just seeing that as a side effect of whatever's really happening. Baron On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote: I did check that all tables are Innodb. I was using the Navicat Server Monitor because I know that when I see the monitor reporting a status of locked during an attempted query, that user comes to a complete halt until the lock is cleared (usually by the bad query finishing.) -- 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 query locks tables in Innodb
Carl, Locked status in SHOW PROCESSLIST and a table being locked are different. There is a bug in MySQL that shows Locked status for queries accessing InnoDB tables in some cases. What version of MySQL are you using? The table is not really locked, you're just seeing that as a side effect of whatever's really happening. Baron On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote: I did check that all tables are Innodb. I was using the Navicat Server Monitor because I know that when I see the monitor reporting a status of locked during an attempted query, that user comes to a complete halt until the lock is cleared (usually by the bad query finishing.) -- 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 query locks tables in Innodb
Baron,
I am using 5.0.37.
While it may be true that there is a bug that shows tables as being locked
when they really aren't, I do not think that applies here. I do know that
when a table shows a status of 'Locked' in the Navicat Server Monitor that
the transaction which created and is processing the query comes to a
complete stop until the report query (the one I am trying to straighten out
or understand) is finished. For example, the report query is reading from
several files, e.g., receipt_master, if a user tries to check out (which
requires an insert into the receipt_master table), they are stopped until
the report query finishes and query on that table shows in Navicat as
waiting for lock ('Locked'.)
Since the report query is only reading data, I am puzzled why it locks the
tables. Any ideas?
TIA,
Carl
- Original Message -
From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of whatever's really happening.
Baron
On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote:
I did check that all tables are Innodb.
I was using the Navicat Server Monitor because I know that when I see the
monitor reporting a status of locked during an attempted query, that user
comes to a complete halt until the lock is cleared (usually by the bad
query
finishing.)
--
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 query locks tables in Innodb
I don't think it locks the tables. The behavior may be similar, but I
seriously doubt that's what's happening. Take a snapshot of SHOW
INNODB STATUS while this is going on. And use mysqladmin debug and
check the error log. Then put those in some pastebin and send us the
link. And realize that you've only given us bits and snippets of
information about this -- you still haven't given us SHOW CREATE TABLE
or even shown us the query that's running. There's not a lot I can do
to really help you with this other than assume that you are wrong :)
Your version is definitely affected by that bug, which I can't find --
I am using the wrong search terms and can't find the right ones to
find the bug.
5.0.37 is a very buggy version and I would upgrade regardless if I
were you, to the latest 5.0 release. You might be surprised at how
much that changes things.
Baron
On Wed, Mar 4, 2009 at 3:33 PM, Carl c...@etrak-plus.com wrote:
Baron,
I am using 5.0.37.
While it may be true that there is a bug that shows tables as being locked
when they really aren't, I do not think that applies here. I do know that
when a table shows a status of 'Locked' in the Navicat Server Monitor that
the transaction which created and is processing the query comes to a
complete stop until the report query (the one I am trying to straighten out
or understand) is finished. For example, the report query is reading from
several files, e.g., receipt_master, if a user tries to check out (which
requires an insert into the receipt_master table), they are stopped until
the report query finishes and query on that table shows in Navicat as
waiting for lock ('Locked'.)
Since the report query is only reading data, I am puzzled why it locks the
tables. Any ideas?
TIA,
Carl
- Original Message - From: Baron Schwartz ba...@xaprb.com
To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, March 04, 2009 2:29 PM
Subject: Re: Select query locks tables in Innodb
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect of whatever's really happening.
Baron
On Wed, Mar 4, 2009 at 2:01 PM, Carl c...@etrak-plus.com wrote:
I did check that all tables are Innodb.
I was using the Navicat Server Monitor because I know that when I see the
monitor reporting a status of locked during an attempted query, that user
comes to a complete halt until the lock is cleared (usually by the bad
query
finishing.)
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
--
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 query locks tables in Innodb
A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. Is that how many records you want to return? That seems like a lot. Maybe reworking your query may help. Heck, post the sizeable query. You've been spending weeks on it. Brent Baisley On Tue, Mar 3, 2009 at 10:53 AM, Carl c...@etrak-plus.com wrote: I have been wrestling with this problem for a couple of weeks and have been unable to find a solution. The MySQL version is 5.0.37 and it is running on a Slackware Linux 11 box. The problem: A query that is selecting data for a report locks the files that it accesses forcing users who are attempting to enter transactions to wait until the select query is finished. The query is sizable so I have not included it here (I can if that would be helpful.) Explain shows (abbreviated): id select_type table type possible keys key_len ref rows Extra 1 SIMPLE transactions ref PRIMARY,person,organization 4 const 107448 * 1 SIMPLE person eq_ref PRIMARY 4 person_serial 1 1 SIMPLE regs ref transaction 4 transactions_serial 1 1 SIMPLE transaction_event ref PRIMARY, transaction, receipt 4 transactions_serial 1 1 SIMPLE receipt_master ref PRIMARY 4 receipt_serial 1 The 107448 rows are the transactions for the organization I am reporting. The person is linked directly to the transaction. During the select query, the person table is locked thereby stopping updates to any person in the table. I have always thought a select is only a read and would, therefore, not lock any tables. Anyone have any ideas? TIA, Carl -- 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 query locks tables in Innodb
On Tue, Mar 3, 2009 at 10:53 AM, Carl c...@etrak-plus.com wrote: A query that is selecting data for a report locks the files that it accesses forcing users who are attempting to enter transactions to wait until the select query is finished. Is it an INSERT INTO...SELECT FROM? Those lock. Also, have you verified that each table you think is InnoDB really is? Do a SHOW CREATE TABLE on them. - Perrin -- 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 query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com wrote: A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table. InnoDB does not do lock escalation a la SQL Server etc. I'd look at Perrin's suggestions, I think they are likely to be the problem. More importantly, what is the query? :-) -- 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 Query
On Fri, May 23, 2008 at 11:20 PM, Velen [EMAIL PROTECTED] wrote: Hi, I wanted to know when doing a select query how is it executed : If there is 1000 records with price10, 3000 records with flag='Y' and the table contains 200,000 records. Select code, description, price, flag from products where flag='Y' and price10 Select code, description, price, flag from products where price10 and flag='Y' Which one of the query will be faster? In query 1, will mysql sort the list for flag='Y' then from the list find price'10'? Regards, Velen There should be no difference in quey execution. If there is an index on either column with good cardinality, then that index will probably be used to eliminate records first. If you are on mysql 5.0+ then multiple index may be used (merge index). After this happens each individual row will need to be examined, which will be expensive depending on the number or rows left after using the index. EXPLAIN and EXPLAIN EXTENDED are your friends for questions like this. At some point I need to dig into the mysql source to gain a better understanding of what is going on... -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select query problem
Barry wrote: Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? IF!!! you need IF!! :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query taking time
On Monday 24 July 2006 09:05, Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using mytop. In the 11th sec both the queries ended. Running it thrice simultaneously, it took 15 secs for all the three queries to complete. Consider this: With the query cache enabled, running a query for the first time will take 5 seconds. Running it again immediately should be instantaneous, as the result set is in memory (and if it isn't, it should be in the OS disk cache [assuming a small result set]). Two queries executed simultaneously will cause disk contention, because the query isn't cached by mysql, and the OS cache probably hasn't had time to commit the data coming from the disks either. In the case of a single disk serving up the data, two simultaneous queries for the same data will cause the disk to go back and forth trying to satisfy each query. Even with a mirrored pair of disks, you're going to have problems unless you have a very intelligent disk controller that can split the requests across the two disks. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query taking time
On Monday 24 July 2006 09:06, Duncan Hill wrote: On Monday 24 July 2006 09:05, Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using In the case of a single disk serving up the data, two simultaneous queries for the same data will cause the disk to go back and forth trying to satisfy each query. Even with a mirrored pair of disks, you're going to have problems unless you have a very intelligent disk controller that can split the requests across the two disks. Forgot to add - do the queries require table locks? If so, the first one is going to lock the table, run in 5 seconds, unlock. Then the second one, and then the third. Assuming no query cache. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query taking time
Ratheesh K J wrote: Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using mytop. In the 11th sec both the queries ended. Running it thrice simultaneously, it took 15 secs for all the three queries to complete. In such a case should this query be considered as slow? We are actually checking for queries which take longer than 12 secs and regarding such queries as slow. The moment we find such a query, a mail is sent to the DBA saying that the query is slow. So in a day there are more than 400 such slow query notifications flowing into the mail box. My questions are, Should the simultaneous queries take so long? Should'nt both queries have finished by 6 secs rather than 10 secs? Without query caching enabled, yes it is perfectly normal that the time spent is rising in a linear fashion, eg 4 simulatious would be 20 seconds, 5 25 secs and so on - this just means that your query run by itself is able to utilize all available resources such as cpu time. Look at it this way: 1 query will use 100% of the available cpu and it takes 5 seconds. When you run two at the same time they each have 50% cpu to use, and thus take 10 seconds (5 seconds * 100 / 50). With 3 they each have 33,1/3% and take 15 seconds ( 5 seconds * 100 / 33,1/3) and so on. Is this a right strategy to track slow queries? Yes and no. It is always wise to test your queries to see how the do speed wise, but if you only measure time you aren't really getting the full picture. You have to also look at what else the system is doing - if a query is bottlenecked only by available cpu, it will run at very different speeds depending on how busy the system is with other things - try to bzip2 a 500MB file while running the query and see how much time it takes then for instance ;) And as always remember to use explain to see how mysql optimizes your query so you can modify it if needed, especially complicated joins can sometimes be alot faster if you tweak them a bit. Any suggestions would help. Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select query problem
Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Query GROUP BY
The schema of your contract should be like this: Contract (id, level, ...) where column 'id' is the primary key, isn't it? If so, you can try this: SELECT COUNT(id) FROM contract GROUP BY level - Original Message - From: Jay [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 11, 2006 5:41 PM Subject: SELECT Query GROUP BY Hello MySQL Users I have a contract table. Each contract has a certain level, which can be in a range from 1-5. This information is stored as a number. There is no additional table for the levels. I would like to get a list with the amount of contracts of each level - including 0 for the levels with no contracts. Until now I just used : SELECT COUNT(*), level FROM contract GROUP BY level but this is just showing level with contracts. I tried a right join with a table which contains just integer values. Seems like a workaround, but I'm interested in a easier aolution - I bet there is one. Thank you! Jay PS: I'm using Version 4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Query GROUP BY
Thank you, Peng Yi-fan but incase there is no contract with the level 5, it will not be shown. I would like to see: level amount 1 34 2 0 3 18 4 986 5 0 I could add it in the application, but I try to do it within the Query. btw. the right join I mentioned, doesn't work. Has someone another idea? Thank you! Jay The schema of your contract should be like this: Contract (id, level, ...) where column 'id' is the primary key, isn't it? If so, you can try this: SELECT COUNT(id) FROM contract GROUP BY level - Original Message - From: Jay [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 11, 2006 5:41 PM Subject: SELECT Query GROUP BY Hello MySQL Users I have a contract table. Each contract has a certain level, which can be in a range from 1-5. This information is stored as a number. There is no additional table for the levels. I would like to get a list with the amount of contracts of each level - including 0 for the levels with no contracts. Until now I just used : SELECT COUNT(*), level FROM contract GROUP BY level but this is just showing level with contracts. I tried a right join with a table which contains just integer values. Seems like a workaround, but I'm interested in a easier aolution - I bet there is one. Thank you! Jay PS: I'm using Version 4.1 -- 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: SELECT Query GROUP BY
The easiest thing to do would be to create an additional table containing all the possible valid values for contract level, then join on that table to show counts. Otherwise it's just not possible to show what's not there - in your case, think of this: how would MySQL know to show 5 when there are no 5's, but not also show the count for every other integer that's not there? (6, 7, 8, .. 1048576, 1048577, etc.) CREATE TABLE contractlevel ( level int ); then INSERT 1, 2, 3, etc. then you need a LEFT JOIN like so: select cl.level, count(c.level) as count from contractlevel cl left join contract c using (level) group by cl.level; Hope this helps! Dan Jay wrote: Thank you, Peng Yi-fan but incase there is no contract with the level 5, it will not be shown. I would like to see: level amount 1 34 2 0 3 18 4 986 5 0 I could add it in the application, but I try to do it within the Query. btw. the right join I mentioned, doesn't work. Has someone another idea? Thank you! Jay The schema of your contract should be like this: Contract (id, level, ...) where column 'id' is the primary key, isn't it? If so, you can try this: SELECT COUNT(id) FROM contract GROUP BY level - Original Message - From: Jay [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, May 11, 2006 5:41 PM Subject: SELECT Query GROUP BY Hello MySQL Users I have a contract table. Each contract has a certain level, which can be in a range from 1-5. This information is stored as a number. There is no additional table for the levels. I would like to get a list with the amount of contracts of each level - including 0 for the levels with no contracts. Until now I just used : SELECT COUNT(*), level FROM contract GROUP BY level but this is just showing level with contracts. I tried a right join with a table which contains just integer values. Seems like a workaround, but I'm interested in a easier aolution - I bet there is one. Thank you! Jay PS: I'm using Version 4.1 -- 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: SELECT Query GROUP BY
Thank you Dan, [...] Otherwise it's just not possible to show what's not there - in your case, think of this: how would MySQL know to show 5 when there are no 5's, but not also show the count for every other integer that's not there? (6, 7, 8, .. 1048576, 1048577, etc.) [...] Sure, easy to understand. I was thinking in a (1,2,3,4,5) list instead of a table Thank you very much! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT Query GROUP BY
No problem, glad to help. I noticed your comment in an earlier message about it seeming like a workaround - I don't think it seems like a workaround at all. Having a table with the possible values makes for a normal database structure, and an approach that should keep you from having to modify your application's SQL queries when someone decides to add contract levels 6 through 10 and then later 11 and 12, for example. I also wouldn't be concerned about performance using such a join - SQL database servers are optimized for JOIN operations. They do them very well. In your case I'd add a UNIQUE index on the contractlevel table, more to guard against duplicate values than for performance, though it certainly won't hurt performance. Dan Jay wrote: Thank you Dan, [...] Otherwise it's just not possible to show what's not there - in your case, think of this: how would MySQL know to show 5 when there are no 5's, but not also show the count for every other integer that's not there? (6, 7, 8, .. 1048576, 1048577, etc.) [...] Sure, easy to understand. I was thinking in a (1,2,3,4,5) list instead of a table Thank you very much! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query taking too long
On Wed, 2005-10-19 at 17:39 -0400, Anoop kumar V wrote: I have 2 tables used for reporting and there are no primary keys or indexes for either. I am trying to run a select query to identify some rows that need to be removed. But for around 100,000 rows the query is taking too long. Can somebody please help me in tuning this query? You have answered your own question! The problem is that there are no indexes on your tables. Indexes are designed to speed SELECT queries up, so not having indexes will cause your SELECT queries to slow down. Define indexes on the columns you are querying against; I'd start with accountstatus, eid, loginid, applicationname, profilecode... From the names I'd suggest some of those might be UNIQUE indexes or PRIMARY KEYs. -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query taking too long
Unfortunately, I cannot create indexes for these tables. These are on production and I cannot modify the tables in anyway. Also, none of the columns are unique in nature - they just serve as a reporting store. Is there anyway that I can tune the select query itself and hope some performance enhancement?? (Maybe I am scanning the tables once too many.. or) Need help please. Thanks, Anoop On 10/19/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: On Wed, 2005-10-19 at 17:39 -0400, Anoop kumar V wrote: I have 2 tables used for reporting and there are no primary keys or indexes for either. I am trying to run a select query to identify some rows that need to be removed. But for around 100,000 rows the query is taking too long. Can somebody please help me in tuning this query? You have answered your own question! The problem is that there are no indexes on your tables. Indexes are designed to speed SELECT queries up, so not having indexes will cause your SELECT queries to slow down. Define indexes on the columns you are querying against; I'd start with accountstatus, eid, loginid, applicationname, profilecode... From the names I'd suggest some of those might be UNIQUE indexes or PRIMARY KEYs. -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks and best regards, Anoop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query taking too long
Im a little confused by the query you posted.. it looks like it would work, although with many redundant subqueries to get there. From your requirement, I don't understand why you needs to wrap it in a self- referencing subquery.. Why does this not give you the same logical value? select count(r2.eid) from rptaccess r2, record_of_access roa where roa.system = 'Remove All' and roa.accountstatus = 'D' and r2.eid = roa.eid and r2.loginid = roa.loginid and upper(r2.applicationname) = upper(roa.applicationname) Having said that: if your original query takes 5 seconds in your 30 record QA environment, adding a few indexes as recommended will take it down to the order of maybe a few hundred milliseconds. Adding indexes to production, while not to be taken lightly, is not something to be shy away from. for records on the order of a few hundred K, it would be a matter of a minute or so and the odds of the action breaking anything are very nearly nil. On Wed, 2005-19-10 at 19:39 -0400, Anoop kumar V wrote: Unfortunately, I cannot create indexes for these tables. These are on production and I cannot modify the tables in anyway. Also, none of the columns are unique in nature - they just serve as a reporting store. Is there anyway that I can tune the select query itself and hope some performance enhancement?? (Maybe I am scanning the tables once too many.. or) Need help please. Thanks, Anoop On 10/19/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: On Wed, 2005-10-19 at 17:39 -0400, Anoop kumar V wrote: I have 2 tables used for reporting and there are no primary keys or indexes for either. I am trying to run a select query to identify some rows that need to be removed. But for around 100,000 rows the query is taking too long. Can somebody please help me in tuning this query? You have answered your own question! The problem is that there are no indexes on your tables. Indexes are designed to speed SELECT queries up, so not having indexes will cause your SELECT queries to slow down. Define indexes on the columns you are querying against; I'd start with accountstatus, eid, loginid, applicationname, profilecode... From the names I'd suggest some of those might be UNIQUE indexes or PRIMARY KEYs. -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks and best regards, Anoop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select/query from two tables
I think that this will work:
$query = SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH
(`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 12:06:17 PM:
A popular question, how to select/query from two tables. I googled it
but am having trouble, wondered if anyone would answer this newbie
question. Here's my existing (PHP) query:
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
I want to SELECT from a new second table using page_id just like I'm
doing now...they all are related by that page_id field. So i'm already
selecting from a table called page and I want to select from a new
table called url the same i do for page at the same time. How do I
modifiy my statement?
Thank you sincerely,
Lee G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select/query from two tables
On Fri, 8 Oct 2004 12:22:37 -0400, [EMAIL PROTECTED] said:
I think that this will work:
$query = SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH
(`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
Sorry to bother I may be showing my lack, but the url table is a
different table from the page table so wouldn't it be folowing your
example:
$query = SELECT page.* FROM `page`, url.* FROM `url` LEFT JOIN
`keywords` USING??
Thanks again
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 12:06:17 PM:
A popular question, how to select/query from two tables. I googled it
but am having trouble, wondered if anyone would answer this newbie
question. Here's my existing (PHP) query:
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
I want to SELECT from a new second table using page_id just like I'm
doing now...they all are related by that page_id field. So i'm already
selecting from a table called page and I want to select from a new
table called url the same i do for page at the same time. How do I
modifiy my statement?
Thank you sincerely,
Lee G.
--
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: select/query from two tables
No. You only get one FROM clause, so it's SELECT columns FROM tables
See the manual for complete details of SELECT syntax
http://dev.mysql.com/doc/mysql/en/SELECT.html.
Michael
leegold wrote:
On Fri, 8 Oct 2004 12:22:37 -0400, [EMAIL PROTECTED] said:
I think that this will work:
$query = SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH
(`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
Sorry to bother I may be showing my lack, but the url table is a
different table from the page table so wouldn't it be folowing your
example:
$query = SELECT page.* FROM `page`, url.* FROM `url` LEFT JOIN
`keywords` USING??
Thanks again
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 12:06:17 PM:
A popular question, how to select/query from two tables. I googled it
but am having trouble, wondered if anyone would answer this newbie
question. Here's my existing (PHP) query:
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
I want to SELECT from a new second table using page_id just like I'm
doing now...they all are related by that page_id field. So i'm already
selecting from a table called page and I want to select from a new
table called url the same i do for page at the same time. How do I
modifiy my statement?
Thank you sincerely,
Lee G.
--
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: select/query from two tables
You didn't try it, did you 8-).
In a nutshell a basic SELECT statement looks like:
SELECT /columns list/
FROM /tables list/
WHERE /conditions list/
The /columns list/ is where you specify all of the values you want from
the database, including constant and computed values
The /tables list/ is where you specify where the data comes from. If it
requires more than one table to provide your data, this is also where your
table JOINs occur.
The /where list/ defines the conditions each resulting row must meet in
order to be able to contribute it's values to those requested in the
/columns list/
Please refer to this URL for more details:
http://dev.mysql.com/doc/mysql/en/SELECT.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 01:12:17 PM:
On Fri, 8 Oct 2004 12:22:37 -0400, [EMAIL PROTECTED] said:
I think that this will work:
$query = SELECT page.*, url.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) LEFT JOIN URL USING (`page_id`) WHERE MATCH
(`keywords`.`keyword_txt`) AGAINST ('$keyword'
IN BOOLEAN MODE);
Sorry to bother I may be showing my lack, but the url table is a
different table from the page table so wouldn't it be folowing your
example:
$query = SELECT page.* FROM `page`, url.* FROM `url` LEFT JOIN
`keywords` USING??
Thanks again
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
leegold [EMAIL PROTECTED] wrote on 10/08/2004 12:06:17 PM:
A popular question, how to select/query from two tables. I googled
it
but am having trouble, wondered if anyone would answer this newbie
question. Here's my existing (PHP) query:
$query = SELECT page.* FROM `page` LEFT JOIN `keywords` USING
(`page_id`) WHERE MATCH (`keywords`.`keyword_txt`) AGAINST
('$keyword'
IN BOOLEAN MODE);
I want to SELECT from a new second table using page_id just like
I'm
doing now...they all are related by that page_id field. So i'm
already
selecting from a table called page and I want to select from a new
table called url the same i do for page at the same time. How do
I
modifiy my statement?
Thank you sincerely,
Lee G.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query that uses a temporary table
Lorderon [EMAIL PROTECTED] wrote: Mabye, is there a way to tell MySQL to limit the temporary table up to 500 rows? so, when a row is matching into the top 500 rows, the last row will be dropped out (in case the table is on limit), and the new matched row will be inserted into the right place in the temporary table... To find out top 500 of 10,000 rows ordered by some criteria you anyway need to sort these 10,000 rows. :) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query help needed
Daniel, I have a database with the following (simplified) structure: [Products] ProductID ProductName [PurchaseRecords] ProductID CustomerID I need to find all of the rows in the table Products which do not have at least one corresponding row in PurchaseRecords. How do I translate this into a working MySQL select statement? SELECT * FROM products LEFT JOIN purchaserecords USING (productID) WHERE child.product_id IS NULL; PB
Re: select query
Ratna Rajesh Thangudu said: my table looks like this : code size 1n3j 14 1n3j 32 1n3j 37 1n9j 14 1n9j 32 1n9j 14 1nm4 14 1nm4 37 1nm4 32 1nmi14 1nmi14 1oo314 1oo314 1oo414 1oo414 I want to select those rows with 'size' 14, 32 and 37. This is easy..but I also want only those uniq codes which have all the 'size' (14,32 and 37) associated with it. This was discussed yesterday: http://lists.mysql.com/mysql/157911 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT Query assistance please
Got it, thanks SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference', EVENT.ID, EVENT.ID_PROJECTCODE FROM PROJECTCODE LEFT JOIN EVENT ON PROJECTCODE.ID = EVENT.ID_PROJECTCODE WHERE EVENT.ID IS NULL ORDER BY PROJECTCODE.Name -Original Message- From: Luc Foisy Sent: Thursday, November 13, 2003 2:01 PM To: MYSQL-List (E-mail) Subject: SELECT Query assistance please I have two tables EVENT and PROJECTCODE EVENT.ID EVENT.ID_PROJECTCODE PROJECTCODE.ID PROJECTCODE.Name EVENT PROJECTCODE ID = 1 ID_PROJECTCODE = 0 ID = 1 ID = 2 ID_PROJECTCODE = 0 ID = 2 ID = 3 ID_PROJECTCODE = 1 ID = 3 ID = 4 ID_PROJECTCODE = 4 ID = 4 SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference' FROM PROJECTCODE Not quite sure on the join or where claus here, I tried 3 or 4 different ways and can't seem to get what I want. What I want out of the results is PROJECTCODE.ID = 2 and 3, that being all records in PROJECTCODE that do not appear in EVENT Luc -- 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: select query syntax help [ANSWER]
This is a common question. The syntax looks like this: SELECT a.* FROM tbl_a AS a LEFT JOIN tbl_b AS b ON a.id = b.id WHERE b.id.id IS NULL; The idea is you're retrieving a recordset of the two tables where the rows are joined on the id. For tbl_b, the id field has no value (its null) so you can identify those rows by asking for nulls in the `tbl_b` `id` column. In your case, I would try: SELECT tbl.* FROM ResourceTable AS tbl LEFT JOIN ResourceLinkTable AS lnk ON tbl.ResourceID= lnk.ResourceID WHERE lnk.ResourceID.id IS NULL ORDER BY ResourceName ASC; Regards, Adam -Original Message- From: Dan Lamb [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:39 AM To: [EMAIL PROTECTED] Subject: select query syntax help Hello All, I have two table the look like this (greatly simplified): ResourceTable - int ResourceID var ResourceName ResourceLinkTable - int ResourceLinkID int ResourceID var Text I need to find all rows in ResourceTable for which there is NO entry in ResourceLinkTable. I know I could do this with sub-selects like this: Select * from ResourceTable where ResourceID not in (select distinct ResourceID from ResourceLinkTable) How can I do this in MySQL 4.0 without using sub-selects? Thanks, Dan Lamb -- 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: select query syntax help
Dan, SELECT ResourceTable.* FROM ResourceTable LEFT JOIN ResourceLinkTable ON ResourceTable.ResourceID = ResourceLinkTable.ResourceID WHERE ResourceLinkTable.ResourceID IS NULL; Regards, Thomas On Thu, 30 Oct 2003, Dan Lamb wrote: Hello All, I have two table the look like this (greatly simplified): ResourceTable - int ResourceID var ResourceName ResourceLinkTable - int ResourceLinkID int ResourceID var Text I need to find all rows in ResourceTable for which there is NO entry in ResourceLinkTable. I know I could do this with sub-selects like this: Select * from ResourceTable where ResourceID not in (select distinct ResourceID from ResourceLinkTable) How can I do this in MySQL 4.0 without using sub-selects? Thanks, Dan Lamb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query-Display current month and last 11 months...
Maybe like: SELECT something FROM tablename WHERE date_column DATE_SUB(CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-','01'), INTERVAL @n MONTH) @n is the number of months you want. If you want data from the current month, @n would be 0. -- Diana Soares On Mon, 2003-10-06 at 07:23, [EMAIL PROTECTED] wrote: Hi all, Having a slight problem with mysql select query right here. I've learnt that if I were to select a particular data within the last 30 days, this is what my select query should be like: SELECT something FROM tablename WHERE TO_DAYS(NOW(()_TO_DAYS(date_column)=30; (This query selects all records with a 'date_column' value within the last 30 days.) Now my question is: What if I would like to display data for the CURRENT MONTH and the last 11 months???(May also said to be the LAST MONTHS) HOw should my select query be like?? Hope to receive some help soon. Any help given is greatly appreciated. Regards, Irin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select query question
Luis Lebron wrote: I have a test results table that looks like this student_id test_id score 1 1 90 1 1 100 1 1 80 2 1 95 2 1 85 2 1 75 I need to create a query that would give me the average of the top 2 scores per student per test. Following this example, student #1 would have an average of 95 (100 + 90)/2 for test #1 and student #2 would have an average of 90 (95 + 85)/2 Tricky, but doable. SELECT a.student_id, a.test_id, avg(b.score), a.score AS second_highest, max(b.score) AS highest FROM test_results a INNER JOIN test_results b ON a.student_id = b.student_id WHERE a.score = b.score GROUP BY a.student_id, a.test_id, a.score HAVING count(b.score) = 2; I think this ought to work. To see how, try executing it by hand against the sample data. Basically, the WHERE restricts the join to look at combinations where the student has scores at least the value found in a.score, which is needed to rank the scores. The group by allows us to count how many scores are at least as high as the one from 'a'. And, the HAVING clause allows us to isolate scores in 'a' which are second-highest using that information; we then compute the average score that's at least as high as the second-highest value. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT query with OUTER JOIN - problem
On Mon, Jun 02, 2003 at 12:48:38PM +0200, Dejan Milenkovic wrote: I have two tables, one is containing data about courses and the second one is containing data about course start date. Is it possible to list all courses with one query which should also return earliest scheduled start dates for courses (which are in the second table). I tried something like this: SELECT c_d.*, MIN(c_s.start) as start FROM course_data c_d RIGHT OUTER JOIN course_start c_s ON c_s.courseid=c_d.courseid WHERE c_s.startNOW() GROUP BY c_s.courseid But this return only courses that have start date if I replace ON condition with 1=1 I get list of all courses but with the same date, if I remove ON condition MySQL return error, I also tried replacing ON c_s.courseid=c_d.courseid with USING (courseid) but that didn't help. Any help is appriciated. Here are the table definitions and test data. CREATE TABLE course_data ( courseid mediumint(9) NOT NULL auto_increment, data varchar(255) NOT NULL default '', PRIMARY KEY (courseid) ) TYPE=MyISAM; INSERT INTO course_data VALUES (1, 'Test data'); INSERT INTO course_data VALUES (2, 'Also test data'); CREATE TABLE course_start ( courseid mediumint(9) NOT NULL, start date NOT NULL default '-00-00' ) TYPE=MyISAM; INSERT INTO course_start VALUES (1, '2004-12-12'); So I need quey that would return both courses where the start column for course 1 would be '2004-12-1 and NULL for course 2. You don't want a RIGHT OUTER JOIN -- you want a LEFT [OUTER] JOIN, because you are trying to get a result for each row from the left-most table. But you also need to explicitly select those results where the start is NULL. So your query would be: SELECT c_d.*, MIN(c_s.start) AS start FROM course_data c_d LEFT JOIN course_start c_s USING (courseid) WHERE c_s.start NOW() OR c_s.start IS NULL GROUP BY c_s.courseid; This returns: +--+++ | courseid | data | start | +--+++ |2 | Also test data | NULL | |1 | Test data | 2004-12-12 | +--+++ Here's an article from SQL-Guru.com that explains the basic join types: http://www.sql-guru.com/sql101/basicjoins.html I hope that helps. Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query to give sum and distinct values
I didn't test it but you may try something like: SELECT ddi, sum(tot_dur) FROM table GROUP BY ddi ORDER BY ddi On Mon, 2003-01-20 at 12:59, Steve Mansfield wrote: Using mysql 3.23.51 I have a mysql table that holds records for telephone traffic. The table fields are as follows: idstartstop clidditot_dur day_dureve_durwkd_dur 1 08/12/2002--23:50:0009/12/2002--02:23:551507608105 08451340206923508635600 2 09/12/2002--00:14:1509/12/2002--00:15:081634324824 0845134012053.1 053.1 0 3 09/12/2002--00:20:1709/12/2002--00:20:591634324824 0845134012041.9 041.9 0 4 09/12/2002--00:28:0009/12/2002--00:28:461634324824 08451340120460460 5 09/12/2002--00:30:2509/12/2002--00:31:041634324824 0845134012038.9 038.9 0 6 09/12/2002--03:22:3009/12/2002--05:08:431507608105 084513402066372.9 06372.9 0 7 09/12/2002--05:25:2509/12/2002--05:35:561622859384 08451340214631.6 0631.6 0 What i'm trying to do is run a query that will give me the sum tot_dur for each distinct ddi ie: distinct ddi will give me the list of all the ddi numbers that were called but i need the sum of tot_dur for each distinct ddi. so it should produce an output like: dditot_dur 08451340120179.9 0845134020615607.9 08451340214631.6 Anyone have any ideas as i just cannot get the query correct. Steve Mansfield [EMAIL PROTECTED] http://www.getreal.co.uk Real Data Services Ltd 117-119 Marlborough Road Romford Essex RM7 8AP [Office] 0870 757 7900 [Fax] 0870 757 8900 http://www.be-an-isp.comhttp://www.isdn4free.co.uk http://signup.getreal.co.uk For our email disclaimer please see the url below. http://www.getreal.co.uk/disclaimer.htm -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT query
RE: MySQL SELECT and COUNT or SUM [EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Hi, all! I think this SELECT command does almost what I need. SELECT CW03survey.Q6, CW03survey.Q7, CW03survey.Q8 FROM CW03survey WHERE CW03survey.Q3 = '1' = But how can I get it to also run a count of the total of each column? Q6, Q7...? Thanks, Gloria McMillan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT query
At 14:38 -0700 12/28/02, Gloria L. McMillan wrote: RE: MySQL SELECT and COUNT or SUM Hi, all! I think this SELECT command does almost what I need. SELECT CW03survey.Q6, CW03survey.Q7, CW03survey.Q8 FROM CW03survey WHERE CW03survey.Q3 = '1' = But how can I get it to also run a count of the total of each column? Q6, Q7...? Thanks, Gloria McMillan If you mean that you want a list of items and also a count of the number of items in the list, you cannot do it with a single query. Lists of items and summaries of lists of items are two different things. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT query
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 28, 2002 6:44 PM To: Gloria L. McMillan; [EMAIL PROTECTED] Subject: Re: SELECT query At 14:38 -0700 12/28/02, Gloria L. McMillan wrote: RE: MySQL SELECT and COUNT or SUM Hi, all! I think this SELECT command does almost what I need. SELECT CW03survey.Q6, CW03survey.Q7, CW03survey.Q8 FROM CW03survey WHERE CW03survey.Q3 = '1' = But how can I get it to also run a count of the total of each column? Q6, Q7...? Try: SELECT CW03survey.Q6, CW03survey.Q7, CW03survey.Q8 FROM CW03survey WHERE CW03survey.Q3 = '1' UNION SELECT SUM(CW03survey.Q6), SUM(CW03survey.Q7), SUM(CW03survey.Q8) FROM CW03survey WHERE CW03survey.Q3 = '1' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Query
if you have an ms. windows machine for a front end, i recommend that you download corereader from http://CoreReader.com/ . ( it's free. ) it does pointclick queries, so you can quickly experiment with them until you get what you want. it installs at the novice level, so set it to the proficient skill level. in the where clause frame, you'll find drop-down lists for the selects. i believe that the one that you want is the is in which will produce the ansi92 in select for you. when it's returning what you want, open the sql frame, which will show you the sql statement that it built for you. (fair warning: not even corereader can make the data connection simple. ) Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 ID = 25 - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Query
I seem to be having problems trying to get to http://www.corereader.com ... Anyone else? +--+ | ___ ___ | | //_/_/_/_/_/ //_/_/_/ | |//_/ //_/ _/ | | //_/ //_/ _/| | //_/ //_/_/_/ | |--| | Tony Devlin - [EMAIL PROTECTED] | | Airewaves Broadband, Systems Admin | | Website - www.airewaves.com | | Atlanta, Ga - 678.522.3923 | | Beaufort, SC - 843.379.AIRE(2473)| +--+ -Original Message- From: John Ragan [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 03, 2002 10:47 AM To: [EMAIL PROTECTED]; Chris Kay Subject: Re: Select Query if you have an ms. windows machine for a front end, i recommend that you download corereader from http://CoreReader.com/ . ( it's free. ) it does pointclick queries, so you can quickly experiment with them until you get what you want. it installs at the novice level, so set it to the proficient skill level. in the where clause frame, you'll find drop-down lists for the selects. i believe that the one that you want is the is in which will produce the ansi92 in select for you. when it's returning what you want, open the sql frame, which will show you the sql statement that it built for you. (fair warning: not even corereader can make the data connection simple. ) Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 ID = 25 - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Query
i just tried, and it came right up. the server is located in the southern u.s., so perhaps the hurricane is effecting it. I seem to be having problems trying to get to http://www.corereader.com ... Anyone else? +--+ | ___ ___ | | //_/_/_/_/_/ //_/_/_/ | |//_/ //_/ _/ | | //_/ //_/ _/| | //_/ //_/_/_/ | |--| | Tony Devlin - [EMAIL PROTECTED] | | Airewaves Broadband, Systems Admin | | Website - www.airewaves.com | | Atlanta, Ga - 678.522.3923 | | Beaufort, SC - 843.379.AIRE(2473)| +--+ -Original Message- From: John Ragan [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 03, 2002 10:47 AM To: [EMAIL PROTECTED]; Chris Kay Subject: Re: Select Query if you have an ms. windows machine for a front end, i recommend that you download corereader from http://CoreReader.com/ . ( it's free. ) it does pointclick queries, so you can quickly experiment with them until you get what you want. it installs at the novice level, so set it to the proficient skill level. in the where clause frame, you'll find drop-down lists for the selects. i believe that the one that you want is the is in which will produce the ansi92 in select for you. when it's returning what you want, open the sql frame, which will show you the sql statement that it built for you. (fair warning: not even corereader can make the data connection simple. ) Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 ID = 25 - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- John Ragan [EMAIL PROTECTED] 501-228-0317 http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Query
At 12:00 +1000 10/3/02, Chris Kay wrote: Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 ID = 25 ... WHERE ID IN (15,25) - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select Query
Thx those who answered, what I was looking for is the IN statement Thanks again - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Thursday, 3 October 2002 12:24 PM To: Chris Kay; [EMAIL PROTECTED] Subject: Re: Select Query At 12:00 +1000 10/3/02, Chris Kay wrote: Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 Or do I have to do WHERE ID = 15 ID = 25 ... WHERE ID IN (15,25) - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Query
Chris Kay wrote: Query ( that gets past the anti spam ) Question is.. I have a select where I want to get ID 15 id 25 Can I do something like WHERE ID = 15,25 I don't think this will work, In SQL you can do WHERE id IN (15, 25) This IN() function doesn't seems working in MYSQL though, Any one encounter the same problem?? Can refer to http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html Or do I have to do WHERE ID = 15 ID = 25 - Chris Kay Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query
MySQL doesn't support sub-selects...You must use joins and temporary tables. Check: http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html it gives some info about it. On Fri, 2002-07-12 at 16:37, Narcis GRATIANU wrote: When I try this: SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) LIMIT 0, 100 I got this error message: You have an error in your SQL syntax near 'SELECT MAX(s2.price) FROM shop s2 WHERE s1.article' at line 3 My shop table contains this data: +-++---+ | article | dealer | price | +-++---+ |0001 | A | 3.45 | |0001 | B | 3.99 | |0002 | A | 10.99 | |0003 | B | 1.45 | |0003 | C | 1.69 | |0003 | D | 1.25 | |0004 | D | 19.95 | +-++---+ and the table was created with this commnad: CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); Thank You ! -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query
Hi, Sorry but MySQL does not support subqueries yet. Bye and Good Luck! --- Narcis GRATIANU [EMAIL PROTECTED] wrote: When I try this: SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) LIMIT 0, 100 I got this error message: You have an error in your SQL syntax near 'SELECT MAX(s2.price) FROM shop s2 WHERE s1.article' at line 3 My shop table contains this data: +-++---+ | article | dealer | price | +-++---+ |0001 | A | 3.45 | |0001 | B | 3.99 | |0002 | A | 10.99 | |0003 | B | 1.45 | |0003 | C | 1.69 | |0003 | D | 1.25 | |0004 | D | 19.95 | +-++---+ and the table was created with this commnad: CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); Thank You ! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select query optimization
i made the change, but it looks like it didn't speed the query up at all. here are the results from the first explain: mysql explain select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; +-+--+---++-+---+--++ | table | type | possible_keys | key| key_len | ref | rows | Extra | +-+--+---++-+---+--++ | RADPOOL | ref | RADPOOL_I2,RADPOOL_I3 | RADPOOL_I2 | 20 | const | 6893 | where used; Using filesort | +-+--+---++-+---+--++ 1 row in set (0.00 sec) i made the changes to acoomodate this create statement: CREATE TABLE `RADPOOL1` ( `id` int(11) NOT NULL auto_increment, `STATE` tinyint(4) default NULL, `TIME_STAMP` int(11) NOT NULL default '0', `EXPIRY` int(11) default NULL, `USERNAME` char(35) default NULL, `POOL` char(20) NOT NULL default '', `YIADDR` char(15) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `RADPOOL_I` (`YIADDR`), INDEX ipoolstate( POOL, STATE ) ); here are the new explain results: mysql explain select TIME_STAMP, YIADDR from RADPOOL1 where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; +--+--+---++-+-+--++ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+--+---++-+-+--++ | RADPOOL1 | ref | ipoolstate| ipoolstate | 22 | const,const | 6011 | where used; Using filesort | +--+--+---++-+-+--++ 1 row in set (0.00 sec) katen At 08:35 AM 4/10/2002 -0700, Lopez David E-r9374c wrote: Katen Try using a compound index with STATE and POOL INDEX ipoolstate( POOL, STATE ) Use EXPLAIN SELECT to see what mysql thinks. David PS anybody know if KEY is the same as INDEX? -Original Message- From: Steve Katen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 8:07 AM To: [EMAIL PROTECTED] Subject: select query optimization i have been working on getting this query as fast as possible and figured it was time to come to the mailing list. the below table currently holds about 43 thousand records with potential to grow to around 1,000,000 records. CREATE TABLE `RADPOOL` ( `id` int(11) NOT NULL auto_increment, `STATE` tinyint(4) default NULL, `TIME_STAMP` int(11) NOT NULL default '0', `EXPIRY` int(11) default NULL, `USERNAME` char(35) default NULL, `POOL` char(20) NOT NULL default '', `YIADDR` char(15) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `RADPOOL_I` (`YIADDR`), KEY `RADPOOL_I2` (`POOL`), KEY `RADPOOL_I3` (`STATE`), KEY `RADPOOL_I4` (`TIME_STAMP`) ); the query below becomes slow when I added the ORDER BY clause to it. select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; the output received from the mysql client is 1 row in set (0.09 sec) is there a way to speed this query up when using the ORDER BY? thanks in advance, katen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select query optimization
Katen The manual has a section on optimization. If you have done deletes on this table, the table may not be optimized. Try: OPTIMIZE TABLE RADPOOL1 It does lock the table while doing it. Other than that, I don't know what else to do. David -Original Message- From: Steve Katen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 8:47 AM To: Lopez David E-r9374c; [EMAIL PROTECTED] Subject: RE: select query optimization i made the change, but it looks like it didn't speed the query up at all. here are the results from the first explain: mysql explain select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; +-+--+---++-+---+--++ | table | type | possible_keys | key| key_len | ref | rows | Extra | +-+--+---++-+---+--++ | RADPOOL | ref | RADPOOL_I2,RADPOOL_I3 | RADPOOL_I2 | 20 | const | 6893 | where used; Using filesort | +-+--+---++-+---+--++ 1 row in set (0.00 sec) i made the changes to acoomodate this create statement: CREATE TABLE `RADPOOL1` ( `id` int(11) NOT NULL auto_increment, `STATE` tinyint(4) default NULL, `TIME_STAMP` int(11) NOT NULL default '0', `EXPIRY` int(11) default NULL, `USERNAME` char(35) default NULL, `POOL` char(20) NOT NULL default '', `YIADDR` char(15) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `RADPOOL_I` (`YIADDR`), INDEX ipoolstate( POOL, STATE ) ); here are the new explain results: mysql explain select TIME_STAMP, YIADDR from RADPOOL1 where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; +--+--+---++-+-+--++ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+--+---++-+-+--++ | RADPOOL1 | ref | ipoolstate| ipoolstate | 22 | const,const | 6011 | where used; Using filesort | +--+--+---++-+-+--++ 1 row in set (0.00 sec) katen At 08:35 AM 4/10/2002 -0700, Lopez David E-r9374c wrote: Katen Try using a compound index with STATE and POOL INDEX ipoolstate( POOL, STATE ) Use EXPLAIN SELECT to see what mysql thinks. David PS anybody know if KEY is the same as INDEX? -Original Message- From: Steve Katen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 8:07 AM To: [EMAIL PROTECTED] Subject: select query optimization i have been working on getting this query as fast as possible and figured it was time to come to the mailing list. the below table currently holds about 43 thousand records with potential to grow to around 1,000,000 records. CREATE TABLE `RADPOOL` ( `id` int(11) NOT NULL auto_increment, `STATE` tinyint(4) default NULL, `TIME_STAMP` int(11) NOT NULL default '0', `EXPIRY` int(11) default NULL, `USERNAME` char(35) default NULL, `POOL` char(20) NOT NULL default '', `YIADDR` char(15) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `RADPOOL_I` (`YIADDR`), KEY `RADPOOL_I2` (`POOL`), KEY `RADPOOL_I3` (`STATE`), KEY `RADPOOL_I4` (`TIME_STAMP`) ); the query below becomes slow when I added the ORDER BY clause to it. select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; the output received from the mysql client is 1 row in set (0.09 sec) is there a way to speed this query up when using the ORDER BY? thanks in advance, katen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query optimization
Hi. (I am replying to the wrong mail, because I already delete the one from Steve...) The slow part is probably the using filesort. I am not sure, if it will work, but try a key over all used column, i.e. INDEX ( POOL, STATE, TIMESTAMP ) If it works (i.e. if MySQL correctly sees that it can skip the filesort), EXPLAIN should show something like using index instead of using filesort and the query run a lot faster again. Bye, Benjamin. PS: And to answer the other question, yes INDEX and KEY are synonyms in MySQL (see http://www.mysql.com/doc/C/R/CREATE_TABLE.html). [...] -Original Message- From: Steve Katen [mailto:[EMAIL PROTECTED]] [...] i made the change, but it looks like it didn't speed the query up at all. [...] here are the new explain results: mysql explain select TIME_STAMP, YIADDR from RADPOOL1 where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; +--+--+---++-+-+--++ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+--+---++-+-+--++ | RADPOOL1 | ref | ipoolstate| ipoolstate | 22 | const,const | 6011 | where used; Using filesort | +--+--+---++-+-+--++ 1 row in set (0.00 sec) At 08:35 AM 4/10/2002 -0700, Lopez David E-r9374c wrote: Katen Try using a compound index with STATE and POOL INDEX ipoolstate( POOL, STATE ) Use EXPLAIN SELECT to see what mysql thinks. David PS anybody know if KEY is the same as INDEX? [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query optimization
ben, I did that about three minutes after I got Davids email. Things seem to be working a lot faster now, and the using filesort is gone. i am assuming there isn't much more i can do to make this thing faster, but anything else i can do would be awesome! heres is the explain: mysql explain select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and POOL='GLOBAL-POOL-SMF' order by TIME_STAMP limit 1; +-+--+---++-+-+--++ | table | type | possible_keys | key| key_len | ref | rows | Extra | +-+--+---++-+-+--++ | RADPOOL | ref | ipoolstate| ipoolstate | 22 | const,const | 9416 | where used | +-+--+---++-+-+--++ 1 row in set (0.00 sec) thanks for your response! =) katen At 08:48 PM 4/10/2002 +0200, Benjamin Pflugmann wrote: Hi. (I am replying to the wrong mail, because I already delete the one from Steve...) The slow part is probably the using filesort. I am not sure, if it will work, but try a key over all used column, i.e. INDEX ( POOL, STATE, TIMESTAMP ) If it works (i.e. if MySQL correctly sees that it can skip the filesort), EXPLAIN should show something like using index instead of using filesort and the query run a lot faster again. Bye, Benjamin. PS: And to answer the other question, yes INDEX and KEY are synonyms in MySQL (see http://www.mysql.com/doc/C/R/CREATE_TABLE.html). [...] -Original Message- From: Steve Katen [mailto:[EMAIL PROTECTED]] [...] i made the change, but it looks like it didn't speed the query up at all. [...] here are the new explain results: mysql explain select TIME_STAMP, YIADDR from RADPOOL1 where STATE=0 and POOL='GLOBAL-POOL-SJC' ORDER BY TIME_STAMP limit 1; +--+--+---++-+-+--++ | table| type | possible_keys | key| key_len | ref | rows | Extra | +--+--+---++-+-+--++ | RADPOOL1 | ref | ipoolstate| ipoolstate | 22 | const,const | 6011 | where used; Using filesort | +--+--+---++-+-+--++ 1 row in set (0.00 sec) At 08:35 AM 4/10/2002 -0700, Lopez David E-r9374c wrote: Katen Try using a compound index with STATE and POOL INDEX ipoolstate( POOL, STATE ) Use EXPLAIN SELECT to see what mysql thinks. David PS anybody know if KEY is the same as INDEX? [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query optimization
Hi. As I said, or at least, tried to say, a key over all used columns should help. Adding the column from ORDER BY avoids the using filesort. Adding YIADDR (which I simply overlooked last time) should add using index, as I mentioned. This means, that the data file is not touched at all, but only the index file. I.e. INDEX ( POOL, STATE, TIME_STAMP, YIADDR ) Of course you have to pay off a bit with disk usage and insert speed. But if select speed is that important, that's usually a good trade off. Bye, Benjamin. On Wed, Apr 10, 2002 at 11:52:03AM -0700, [EMAIL PROTECTED] wrote: ben, I did that about three minutes after I got Davids email. Things seem to be working a lot faster now, and the using filesort is gone. i am assuming there isn't much more i can do to make this thing faster, but anything else i can do would be awesome! heres is the explain: mysql explain select TIME_STAMP, YIADDR from RADPOOL where STATE=0 and POOL='GLOBAL-POOL-SMF' order by TIME_STAMP limit 1; +-+--+---++-+-+--++ | table | type | possible_keys | key| key_len | ref | rows | Extra | +-+--+---++-+-+--++ | RADPOOL | ref | ipoolstate| ipoolstate | 22 | const,const | 9416 | where used | +-+--+---++-+-+--++ 1 row in set (0.00 sec) thanks for your response! =) katen At 08:48 PM 4/10/2002 +0200, Benjamin Pflugmann wrote: [...] The slow part is probably the using filesort. I am not sure, if it will work, but try a key over all used column, i.e. INDEX ( POOL, STATE, TIMESTAMP ) If it works (i.e. if MySQL correctly sees that it can skip the filesort), EXPLAIN should show something like using index instead of using filesort and the query run a lot faster again. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select query optimization
Steve Have you tried using compound index: INDEX( POOL, STATE ) Just a thought. David -Original Message- From: Steve Katen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 03, 2002 10:13 AM To: [EMAIL PROTECTED] Subject: select query optimization i am running a basic install of mysql version 3.23.49-log and i have a pretty simple query that is not fast enough for my needs. any thoughts on optimization would help. the table description is below: mysql desc RADPOOL; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | STATE | int(11) | | MUL | 0 || | TIME_STAMP | int(11) | YES | | NULL|| | EXPIRY | int(11) | YES | | NULL|| | USERNAME | char(50) | YES | | NULL|| | POOL | char(20) | YES | MUL | NULL|| | YIADDR | char(50) | | UNI | || ++--+--+-+-++ 7 rows in set (0.00 sec) the query: select TIME_STAMP, YIADDR from RADPOOL where POOL='GLOBAL-POOL-SJC' and STATE=0 order by TIME_STAMP limit 1 the mysql client shows that it takes .09 seconds mysql select TIME_STAMP, YIADDR from RADPOOL where POOL='GLOBAL-POOL-SJC' and STATE=0 order by TIME_STAMP limit 1; ++-+ | TIME_STAMP | YIADDR | ++-+ | 1016494596 | 66.81.70.26 | ++-+ 1 row in set (0.09 sec) how can i optimize the table, the database, or anything else that will speed the query up? katen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT query with TRIM and LIKE
Thanks Nathan, I think I will go with SELECT * FROM table_name WHERE Name LIKE '%$name%'; NOBBY - Original Message - From: Nathan Bank [EMAIL PROTECTED] To: Tshering Norbu [EMAIL PROTECTED] Sent: Friday, February 08, 2002 12:25 PM Subject: Re: SELECT query with TRIM and LIKE What is the point of the trim? Why can't you simply SELECT * FROM table_name WHERE Name LIKE '%$name%'; Then, if the user types Rob, they'll get Robert, Robert Downing, etc. If they type in Robert D, they'll get Robert Downing. If you trimmed Robert D into RobertD with the trim() function, you would not match Robert Downing any longer. Is that different from what you want? By the way, if your 's are meant to join that info together, as it appears, you need to change them to .'s. A dot joins things. is a bit-wise comparison... Also, $query is a variable, not a function. Either lose the parens (), or add a function to the beginning, like so: $query = mysql_query(SELECT * FROM table); Hope that helps, # Nathan - Original Message - From: Tshering Norbu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 05, 2002 10:29 PM Subject: SELECT query with TRIM and LIKE Dear list, I have a telephone database which has Name as one of the fields/columns. This field contains single name e.g Robert, double names e.g Robert Downing and more than double names. I want to make a SELECT query using a form whose input name is Name i.e '$Name' against the Name field in the database. One can type a part/full of one part of the name, or part/full of two parts of a name and goes on likewise for more than double names in the input form. I believe this SELECT query is accomplished using TRIM and LIKE, something like: $query = (SELECT * from table_name where Name like '% Trim($Name) %' ); This $query is not working. Could sombody please provide me a query statement that I could use for my above requirement. Thank you so much. NOBBY - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT QUERY Problem
Hi, Table 1: names Id | name 15 | George 16 | Suzy Table 2 : scores_1 Id | score 15 | 85 15 | 60 15 | 70 15 | 95 Table 3 : scores_2 Id | score 15 | 50 15 | 55 15 | 60 15 | 45 What I want to end up with is a selection that would pick up George and his highest score on score_1 and score_2 (i.e. George 95 60) mysql select n.name, max(s1.score), max(s2.score) - from names n, scores_1 s1, scores_2 s2 - where n.id = s1.id and n.id = s2.id and - n.id = 15 - group by n.name; ++---+---+ | name | max(s1.score) | max(s2.score) | ++---+---+ | George |95 |60 | ++---+---+ 1 row in set (0.00 sec) Pozdrav, Sasa - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT Query in PHP
Heh, yeah that sounds rookie ;) Have you ever done work in Perl? PHP is a lot like Perl in many aspects (and nothing like it in many others). Heh. Check out http://www.zend.com/ for some other tutorials, if you can find 'em. http://www.phpbuilder.com/ has some gooduns, too. Mike Todd Williamsen wrote: Yes, I think HTML, mySQL and PHP is a marriage made in heaven but... Online docs is like reading stereo instructions in a foreign language most of the time. I tried the webmonkey.com tutorial and it was ok.. Funny thing is that the script they used, I couldn't get to work... Go figure... Just my luck! Granted I am so new to PHP I think I just eclipsed the 48 hour mark... So am I still a rookie? Lol!! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 -Original Message- From: Mike [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 9:33 PM To: Woolsey, Fred Cc: '[EMAIL PROTECTED] ' Subject: Re: SELECT Query in PHP I think the worst part about the books is that with things like PHP, Apache and MySQL, by the time you find (i.e. from browsing at the local library) and sit down to learn, it's talking about MySQL 3.22, Apache 1.2, and PHP3. Nothing beats the online documentation.. amen to that. :) You might try some sites like webmonkey.com for php tutorials. I think that's where I went when I decided to learn php about.. oh about a year ago. Mike Woolsey, Fred wrote: Funny... I find the PHP, HTML and MySQL combo to be a marriage made in heaven. Also, I have not yet seen a book (and I've bought a few of them) that beats the PHP and MySQL documentation available for free on the web. Follow the URLs in the other e-mails and you will find the truth... OK, maybe I exaggerate, but at least you'll find the info you need. Cheers (and remember, wer immer streibend sich bemueht, den koennen wir erloesen.- apologies to Goethe) Fred Woolsey -Original Message- From: Todd Williamsen To: [EMAIL PROTECTED] Sent: 10/26/2001 8:25 PM Subject: SELECT Query in PHP I am a newbie at this and I cannot find the damn answer to it! I want to display the table in an html format with PHP but I cannot get it to work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a pain in the butt! I cannot find any documentation on this and I order 3 books on this stuff, but they won't be here till at least Monday. HELP! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT Query in PHP
Congrats on getting to 48 hours - let us know which platform you prefer when you reach the number of hours that it took to get the 'Must Consult Some Else' tag! :-) QB -Original Message- From: Todd Williamsen [mailto:[EMAIL PROTECTED]] Sent: Saturday, 27 October 2001 5:48 p.m. To: 'Mike'; 'Woolsey, Fred' Cc: [EMAIL PROTECTED] Subject: RE: SELECT Query in PHP Yes, I think HTML, mySQL and PHP is a marriage made in heaven but... Online docs is like reading stereo instructions in a foreign language most of the time. I tried the webmonkey.com tutorial and it was ok.. Funny thing is that the script they used, I couldn't get to work... Go figure... Just my luck! Granted I am so new to PHP I think I just eclipsed the 48 hour mark... So am I still a rookie? Lol!! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 -Original Message- From: Mike [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 9:33 PM To: Woolsey, Fred Cc: '[EMAIL PROTECTED] ' Subject: Re: SELECT Query in PHP I think the worst part about the books is that with things like PHP, Apache and MySQL, by the time you find (i.e. from browsing at the local library) and sit down to learn, it's talking about MySQL 3.22, Apache 1.2, and PHP3. Nothing beats the online documentation.. amen to that. :) You might try some sites like webmonkey.com for php tutorials. I think that's where I went when I decided to learn php about.. oh about a year ago. Mike Woolsey, Fred wrote: Funny... I find the PHP, HTML and MySQL combo to be a marriage made in heaven. Also, I have not yet seen a book (and I've bought a few of them) that beats the PHP and MySQL documentation available for free on the web. Follow the URLs in the other e-mails and you will find the truth... OK, maybe I exaggerate, but at least you'll find the info you need. Cheers (and remember, wer immer streibend sich bemueht, den koennen wir erloesen.- apologies to Goethe) Fred Woolsey -Original Message- From: Todd Williamsen To: [EMAIL PROTECTED] Sent: 10/26/2001 8:25 PM Subject: SELECT Query in PHP I am a newbie at this and I cannot find the damn answer to it! I want to display the table in an html format with PHP but I cannot get it to work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a pain in the butt! I cannot find any documentation on this and I order 3 books on this stuff, but they won't be here till at least Monday. HELP! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT Query in PHP
I am a newbie at this and I cannot find the damn answer to it! I want
to display the table in an html format with PHP but I cannot get it to
work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a
pain in the butt! I cannot find any documentation on this and I order 3
books on this stuff, but they won't be here till at least Monday.
?
$dblink = mysql_pconnect(localhost, username, password);
mysql_select_db(database);
echo(table\n);
$res = mysql_query(SELECT * FROM table);
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
echo(tr\n);
foreach ($row as $col) {
echo(\ttd$col/td\n);
}
echo(/tr\n);
}
echo(/table\n);
?
http://www.php.net/manual/en/html/ref.mysql.html
Doesn't seem so hard to me...
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT Query in PHP
Any books that you buy will probably be derived from: http://www.mysql.com/documentation/mysql/bychapter/ http://www.php.net/manual/en/ Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - Original Message - From: Todd Williamsen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 26, 2001 8:25 PM Subject: SELECT Query in PHP I am a newbie at this and I cannot find the damn answer to it! I want to display the table in an html format with PHP but I cannot get it to work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a pain in the butt! I cannot find any documentation on this and I order 3 books on this stuff, but they won't be here till at least Monday. HELP! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT Query in PHP
Funny... I find the PHP, HTML and MySQL combo to be a marriage made in heaven. Also, I have not yet seen a book (and I've bought a few of them) that beats the PHP and MySQL documentation available for free on the web. Follow the URLs in the other e-mails and you will find the truth... OK, maybe I exaggerate, but at least you'll find the info you need. Cheers (and remember, wer immer streibend sich bemueht, den koennen wir erloesen.- apologies to Goethe) Fred Woolsey -Original Message- From: Todd Williamsen To: [EMAIL PROTECTED] Sent: 10/26/2001 8:25 PM Subject: SELECT Query in PHP I am a newbie at this and I cannot find the damn answer to it! I want to display the table in an html format with PHP but I cannot get it to work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a pain in the butt! I cannot find any documentation on this and I order 3 books on this stuff, but they won't be here till at least Monday. HELP! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT Query in PHP
I think the worst part about the books is that with things like PHP, Apache and MySQL, by the time you find (i.e. from browsing at the local library) and sit down to learn, it's talking about MySQL 3.22, Apache 1.2, and PHP3. Nothing beats the online documentation.. amen to that. :) You might try some sites like webmonkey.com for php tutorials. I think that's where I went when I decided to learn php about.. oh about a year ago. Mike Woolsey, Fred wrote: Funny... I find the PHP, HTML and MySQL combo to be a marriage made in heaven. Also, I have not yet seen a book (and I've bought a few of them) that beats the PHP and MySQL documentation available for free on the web. Follow the URLs in the other e-mails and you will find the truth... OK, maybe I exaggerate, but at least you'll find the info you need. Cheers (and remember, wer immer streibend sich bemueht, den koennen wir erloesen.- apologies to Goethe) Fred Woolsey -Original Message- From: Todd Williamsen To: [EMAIL PROTECTED] Sent: 10/26/2001 8:25 PM Subject: SELECT Query in PHP I am a newbie at this and I cannot find the damn answer to it! I want to display the table in an html format with PHP but I cannot get it to work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a pain in the butt! I cannot find any documentation on this and I order 3 books on this stuff, but they won't be here till at least Monday. HELP! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT Query in PHP
Yes, I think HTML, mySQL and PHP is a marriage made in heaven but... Online docs is like reading stereo instructions in a foreign language most of the time. I tried the webmonkey.com tutorial and it was ok.. Funny thing is that the script they used, I couldn't get to work... Go figure... Just my luck! Granted I am so new to PHP I think I just eclipsed the 48 hour mark... So am I still a rookie? Lol!! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 -Original Message- From: Mike [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 9:33 PM To: Woolsey, Fred Cc: '[EMAIL PROTECTED] ' Subject: Re: SELECT Query in PHP I think the worst part about the books is that with things like PHP, Apache and MySQL, by the time you find (i.e. from browsing at the local library) and sit down to learn, it's talking about MySQL 3.22, Apache 1.2, and PHP3. Nothing beats the online documentation.. amen to that. :) You might try some sites like webmonkey.com for php tutorials. I think that's where I went when I decided to learn php about.. oh about a year ago. Mike Woolsey, Fred wrote: Funny... I find the PHP, HTML and MySQL combo to be a marriage made in heaven. Also, I have not yet seen a book (and I've bought a few of them) that beats the PHP and MySQL documentation available for free on the web. Follow the URLs in the other e-mails and you will find the truth... OK, maybe I exaggerate, but at least you'll find the info you need. Cheers (and remember, wer immer streibend sich bemueht, den koennen wir erloesen.- apologies to Goethe) Fred Woolsey -Original Message- From: Todd Williamsen To: [EMAIL PROTECTED] Sent: 10/26/2001 8:25 PM Subject: SELECT Query in PHP I am a newbie at this and I cannot find the damn answer to it! I want to display the table in an html format with PHP but I cannot get it to work! With MS SQL Server it was sooo easy! With PHP and mySQL it's a pain in the butt! I cannot find any documentation on this and I order 3 books on this stuff, but they won't be here till at least Monday. HELP! Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT query
- Original Message - From: Craig Meyers [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 04, 2001 3:20 Subject: SELECT query List, Our site is running mysql server version 3.22.32 I'm a power user - not the DBA, so forgive my ignorance. Is it possible to corrupt data with a SELECT query? Very unlikely. Have a look at the logs and see if something else was being performed at around the same time. The connection is made via a shell script: mysql -h servername -u userid -ppassword database QUERY_INPUT SELECT c.id, s.id FROM customers c, simulations s WHERE c.name = 'Customer Name Here' AND s.cust_id = c.id AND s.name = 'Simulation Name Here' QUERY_INPUT During the period I was testing this script, our DBA indicated we lost some data. Is this just a coincidence? I can't see how a benign query could cause such a problem. Thanks. Craig Meyers Senior Engineer Invensys Dynamic Simulation - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
That makes sense, sound like I'm redoing in PHP what the UNIQUE column
already does.
So all I need to do is modify my PHP trap using mysql_affected_rows() =
0, retun a error messagu to the used indicating a rcord already exists.
Will try it, THX!
Pete
Paul DuBois wrote:
At 5:46 PM -0500 6/27/01, Pete Kuczynski wrote:
Thanks Paul!
ps works great!
I modified the database with a UNIQUE column like you recommended
earlier, this will now help in the PHP script to trap the duplicate
entry and advise the user of the dup.
But if hostname now has a UNIQUE index on it, it won't even be possible
for your table to have duplicate hostname values, other than NULL.
Probably all you need to do is just go ahead and try to insert the record,
and if mysql_affected_rows() is 0, it was a duplicate and was rejected.
?
// check for duplicate row
$query = SELECT hostname, COUNT(*) AS count FROM asset
GROUP BY hostname HAVING count 1
$result = mysql_query($query);
// check if row is returned, if yes error, if no insert
if (mysql_num_rows($result) != 0) {
echo error;
} else {
// DO INSERT
}
?
Pete
--
Paul DuBois, [EMAIL PROTECTED]
--
___
Pete Kuczynski
Principal Field Engineer
DHL Airways Inc.
Infrastructure Technology Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
Peter, That comes pretty close to the distinct -thread in this mailinglist. You might want to try: SELECT device, count(hostname) FROM your_table GROUP BY device HAVING count(hostname)1; This would display all devices with at least 2 (or more) hostnames. If you always have the same IP address, and the same comments allocated to a certain device, then and only then you can add IP and comments to the SELECT and GROUP BY statement. Vary this as needed or pleased Hope that helps And the manual is great too: http://www.mysql.com/doc/G/r/Group_by_functions.html http://www.mysql.com/doc/S/E/SELECT.html Hannes On 6/27/01 1:13 PM, Pete Kuczynski [EMAIL PROTECTED] wrote: Hi, How would a word a select statment, to search a database for duplicate entries in one field. For example, the fields: device, hostname, IP, comments I want to find all instances where there my be two devices with the same hostname. Thanks! Pete - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
Hi, How would a word a select statment, to search a database for duplicate entries in one field. For example, the fields: device, hostname, IP, comments I want to find all instances where there my be two devices with the same hostname. Thanks! Pete Sir, try the following. SELECT DISTINCT device, hostname FROM table_name GROUP BY hostname HAVING Count(*) 1; Only one of the two or more devices will be listed. If you want to include cases where the same device has been listed twice with the same hostname, leave out the DISTINCT. WOMM (Works On My Machine) Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
Thanks Paul!
ps works great!
I modified the database with a UNIQUE column like you recommended
earlier, this will now help in the PHP script to trap the duplicate
entry and advise the user of the dup.
?
// check for duplicate row
$query = SELECT hostname, COUNT(*) AS count FROM asset
GROUP BY hostname HAVING count 1
$result = mysql_query($query);
// check if row is returned, if yes error, if no insert
if (mysql_num_rows($result) != 0) {
echo error;
} else {
// DO INSERT
}
?
Pete
Paul DuBois wrote:
At 3:13 PM -0500 6/27/01, Pete Kuczynski wrote:
Hi,
How would a word a select statment, to search a database for duplicate
entries in one field.
For example, the fields: device, hostname, IP, comments
I want to find all instances where there my be two devices with the same
hostname.
SELECT hostname, COUNT(*) AS count FROM tbl_name
GROUP BY hostname HAVING count 1
Thanks!
Pete
--
___
Pete Kuczynski
Principal Field Engineer
DHL Airways Inc.
Infrastructure Technology Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767
--
Paul DuBois, [EMAIL PROTECTED]
--
___
Pete Kuczynski
Principal Field Engineer
DHL Airways Inc.
Infrastructure Technology Services
(773)-462-9758
24/7 Helpdesk 1-800-434-5767
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query for duplicate records
At 5:46 PM -0500 6/27/01, Pete Kuczynski wrote:
Thanks Paul!
ps works great!
I modified the database with a UNIQUE column like you recommended
earlier, this will now help in the PHP script to trap the duplicate
entry and advise the user of the dup.
But if hostname now has a UNIQUE index on it, it won't even be possible
for your table to have duplicate hostname values, other than NULL.
Probably all you need to do is just go ahead and try to insert the record,
and if mysql_affected_rows() is 0, it was a duplicate and was rejected.
?
// check for duplicate row
$query = SELECT hostname, COUNT(*) AS count FROM asset
GROUP BY hostname HAVING count 1
$result = mysql_query($query);
// check if row is returned, if yes error, if no insert
if (mysql_num_rows($result) != 0) {
echo error;
} else {
// DO INSERT
}
?
Pete
--
Paul DuBois, [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select query
On 6/7/01 5:33 PM, Gary Huntress [EMAIL PROTECTED] wrote: I'll bet it's a roundoff problemtry select * from sequence_protein where mol_wt 53211.62 and mol_wt 53211.63 Regards, Gary SuperID Huntress === FreeSQL.org offering free database hosting to developers Visit http://www.freesql.org - Original Message - From: Hannes Niedner [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 5:48 PM Subject: Select query What do I do wrong? The query result is not supposed to be an empty set (please cc your response to [EMAIL PROTECTED]) mysql select sequence_id, mol_wt from sequence_protein limit 1; +-+--+ | sequence_id | mol_wt | +-+--+ | 100368 | 53211.62 | +-+--+ 1 row in set (0.02 sec) mysql select distinct sequence_id from sequence_protein - where mol_wt=53211.62; Empty set (0.12 sec) mysql describe sequence_protein; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | sequence_id | int(10) unsigned | | PRI | 0 | | | length | int(10) unsigned | | | 0 | | | mol_wt | float(10,2) | YES | | 0.00| | +-+--+--+-+-+---+ Thank you Hannes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php You guys are great and solved my case mysql select sequence_id from sequence_protein - where truncate(mol_wt,2) =53211.62; +-+ | sequence_id | +-+ | 100368 | +-+ 1 row in set (0.18 sec) I do apologize, I was following the wrong thread in the manual. Thanx Hannes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select query
Switch the column type to double. From the manual: If you are comparing FLOAT or DOUBLE columns with numbers that have decimals, you can't use =! This problem is common in most computer languages because floating-point values are not exact values: mysql SELECT * FROM table_name WHERE float_column=3.5; - mysql SELECT * FROM table_name WHERE float_column between 3.45 and 3.55; In most cases, changing the FLOAT to a DOUBLE will fix this! http://www.mysql.com/doc/N/o/No_matching_rows.html At 05:19 PM 6/7/01 -0700, you wrote: On 6/7/01 4:50 PM, Eric Fitzgerald [EMAIL PROTECTED] wrote: When comparing float values, you have to use '' around the value. Your query should look like this: select distinct sequence_id from sequence_protein where mol_wt = '53211.62'; Ok, I should have mentioned that I tried the quotes. I am running out of ideas. Hannes mysql select sequence_id, mol_wt from sequence_protein limit 1; +-+--+ | sequence_id | mol_wt | +-+--+ | 100368 | 53211.62 | +-+--+ 1 row in set (0.02 sec) mysql select sequence_id from sequence_protein - where mol_wt = '53211.62'; Empty set (0.15 sec) mysql select sequence_id from sequence_protein - where mol_wt = 53211.62; Empty set (0.16 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select query
When comparing float values, you have to use '' around the value. Your query should look like this: select distinct sequence_id from sequence_protein where mol_wt = '53211.62'; - Original Message - From: Hannes Niedner [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 07, 2001 2:48 PM Subject: Select query What do I do wrong? The query result is not supposed to be an empty set (please cc your response to [EMAIL PROTECTED]) mysql select sequence_id, mol_wt from sequence_protein limit 1; +-+--+ | sequence_id | mol_wt | +-+--+ | 100368 | 53211.62 | +-+--+ 1 row in set (0.02 sec) mysql select distinct sequence_id from sequence_protein - where mol_wt=53211.62; Empty set (0.12 sec) mysql describe sequence_protein; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | sequence_id | int(10) unsigned | | PRI | 0 | | | length | int(10) unsigned | | | 0 | | | mol_wt | float(10,2) | YES | | 0.00| | +-+--+--+-+-+---+ Thank you Hannes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select query
On 6/7/01 4:50 PM, Eric Fitzgerald [EMAIL PROTECTED] wrote: When comparing float values, you have to use '' around the value. Your query should look like this: select distinct sequence_id from sequence_protein where mol_wt = '53211.62'; Ok, I should have mentioned that I tried the quotes. I am running out of ideas. Hannes mysql select sequence_id, mol_wt from sequence_protein limit 1; +-+--+ | sequence_id | mol_wt | +-+--+ | 100368 | 53211.62 | +-+--+ 1 row in set (0.02 sec) mysql select sequence_id from sequence_protein - where mol_wt = '53211.62'; Empty set (0.15 sec) mysql select sequence_id from sequence_protein - where mol_wt = 53211.62; Empty set (0.16 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: select query?
On Fri, 04 May 2001, Roger Karnouk wrote: Something like this should work: select ip, mac, count(mac) from ipmac group by ip,mac having count(mac) 1 hope this helps Yes! Thanks Roger - this is just the thing. group by ip, mac having count(mac) 1 returns nothing, but if we use group by mac having count(mac) 1 Then we get the number of different mac's using an ip. Can then query the mac for different IP's used. It would be nice to roll it all into one query and I'll work on that. Thanks again. -- Best regards, Tony Hunter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select query syntax
supposed you numbered all rows in the table incrementally in field nid: select mydata from mytable where mod(nid, N)=offset; fill in N and offset (0..n-1) On Sat, 28 Apr 2001 14:26:46 +0400 Igor V Yermakov [EMAIL PROTECTED] wrote: i have 400 rows in my mysql database table and i wont get each N row from table (say each sixth row)??? please help me??? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
