RE: query on a very big table
I have tables that are over 7 million records and I originally had the same issue, however if you will create indexes in those tables, on the columns that you will be using for your queries this will GREATLY speed up your queries. I am sure that there is a more concise way to state how you should create indexes but you can look at the mysql online docs to figure out what is best for you. Chris Hood Investigator Verizon Global Security Operations Center -Original Message- From: Christos Andronis [mailto:[EMAIL PROTECTED] Sent: Monday, July 25, 2005 8:20 AM To: mysql@lists.mysql.com Subject: query on a very big table Hi all, we are trying to run the following query on a table that contains over 600 million rows: 'ALTER TABLE `typed_strengths` CHANGE `entity1_id` `entity1_id` int(10) UNSIGNED DEFAULT NULL FIRST' The query takes ages to run (has been running for over 10 hours now). Is this normal? As a side issue, is MySQL suited for such big tables? I've seen a couple of case studies with MySQL databases over 1.4 billion rows but it is not clear to me whether this size corresponds to the whole database or whether it is for a single table. The MySQL distribution we're using is 4.1.12. The database sits on a HP Proliant DL585 server with 2 dual-core Opterons and 12 GB of RAM, running Linux Fedora Core 3. Thanks in advance for any responses -Christos Andronis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: storing recurring dates
Bob, You should check out the Date::Manip module from CPAN, it will do what you need to do. Here is a snippet from the docs about recurrence: RECURRENCE A recurrence is simply a notation for defining when a recurring event occurs. For example, if an event occurs every other Friday or every 4 hours, this can be defined as a recurrence. With a recurrence and a starting and ending date, you can get a list of dates in that period when a recurring event occurs. This should get you going with all the options you need. Chris Hood -Original Message- From: Ramsey, Robert L [mailto:[EMAIL PROTECTED] Sent: Sunday, June 12, 2005 8:51 AM To: mysql@lists.mysql.com Subject: storing recurring dates Hi, I'm doing an event project and some of the events will be reccuring. For example: Monday, Wednesday, Friday from 10-11:30 am starting June 1 with no end date Every third Monday at 3-4 pm starting July 1 and ending January 1 (last event is third Monday in December) Every other Friday starting at 1pm with no set end time, starting June 3 Is there a good way to store those in a mysql database? So far the only thing I can think of is that on entry, have a script figure out all of the dates, which is pretty easy in php. Then for the events with no end date set an arbitrary end date of 5 years in the future knowing that the technology will probably change by then and the app will need to be re-written. Is there maybe some way or combination with the php strtotime function? I know it can take something like Third Thursday of October and turn it into a unix time stamp. But my brain is just not working today. ;) Thanks, Bob -- 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]
DBD::Oracle issue
ALL, Can someone help out with this error: DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: error possibly near * indicator at char 23 in 'select table_name from *all_tables') [for Statement select table_name from all_tables] at ./oracleTest.pl line 66. I have tried to set TWO_TASK environment variable to no avail. The oracle connection is a remote connection made. I have successfully looked up other information in the database with this script. The actual SQL command being run is select table_name from all_tables. If anyone needs the actual script that is being run please mail me. But I believe this error is specific to the select statement being run because other selects work just fine. ANY ideas / suggestions would be greatly appreciated, Chris Hood
RE: DBD::Oracle issue
Well I sent this message to both the mysql mailing list and this perl
list, because I am using perl to accomplish this, and mysql to talk to.
But anyway, thank you very much your number 3 EXACTLY fixed my issues, so
the person with the right answer contacted me.
Again, thank you very much for your assistance.
Chris Hood
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 01, 2005 8:02 AM
To: Christopher L. Hood
Cc: mysql@lists.mysql.com; beginners@perl.org
Subject: Re: DBD::Oracle issue
Hi,
I'll try even if this is not a neither oracle nor perl-dbd list.
1. verify that NLS_LANG is correct in your client env (and all the other
ORA
vars)
2. verify the query is simply quoted ( needs \_ for _)
3. Put all oracle variables in BEGIN {ORACLe_HOME=}; in the perl script
4. verify that select * from dual is OK
Mathias
Selon [EMAIL PROTECTED]:
ALL,
Can someone help out with this error:
DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication
protocol error (DBD ERROR: error possibly near * indicator at char 23
in
'select table_name from *all_tables') [for Statement select
table_name
from all_tables] at ./oracleTest.pl line 66.
I have tried to set TWO_TASK environment variable to no avail.
The oracle connection is a remote connection made.
I have successfully looked up other information in the database with
this
script.
The actual SQL command being run is select table_name from all_tables.
If anyone needs the actual script that is being run please mail me. But
I
believe this error is specific to the select statement being run because
other selects work just fine.
ANY ideas / suggestions would be greatly appreciated,
Chris Hood
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Resetting Auto-increment
Is there a better way to reset the auto_increment in a table, basically there are several million rows in the database and the field that is auto_increment is very large now and I don't want to exceed the limit of the field description so I want to renumber all the rows starting at 1 again. What I found online was this: For those that are looking to reset the auto_increment, say on a list that has had a few deletions and you want to renumber everything, you can do the following. DROP the field you are auto_incrementing. ALTER the table to ADD the field again with the same attributes. You will notice that all existing rows are renumbered and the next auto_increment number will be equal to the row count plus 1. So is there an easier / better way to do this? Also would the way this is done be different if the table is empty?? I have an empty table that when I add a record, starts at some number based on how many have been entered and deleted. Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message.
Changing DB name
Is there a way to change the name of a database? What has happened is that our test db has now become the production db and so I want to rename the database. Chris Hood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compressing after Deletion
I have looked in the documentation and either I am not looking for the right thing or have simply overlooked it. But my question is this, I have a database with 35 Million records, and I need to delete about 25 million of those. After deletion I would think that I would need to compress, shrink, or otherwise optimize the database. How is that done? do I need to do it? What commands should I be looking up in the docs? Any help is greatly appreciated. Chris Hood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Losing database information
Where would I find the tablename.InnoDB files ? As in the error below that I receive when check table is run against the table: mysql check table ACL; +-+---+--+---+ | Table | Op| Msg_type | Msg_text | +-+---+--+---+ | rt3.ACL | check | error| Can't open file: 'ACL.InnoDB'. (errno: 1) | +-+---+--+---+ 1 row in set (0.01 sec) Chris Hood Investigator Verizon Global Security Operations Center Email:[EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 8:05 AM To: Christopher L. Hood Cc: mysql@lists.mysql.com Subject: Re: MySQL Losing database information [EMAIL PROTECTED] wrote: ALL, I have an issue where MySQL has lost all of the table information for an existing database, I do not know of anything that has changed and there was no maintenance being done in MySQL. Below you will find the error message as I receive it from MySQL. ANY ideas or suggestions on how to recover this database intact will be GREATLY appreciated. The .FRM files are intact and all permissions have been checked against a database that I CAN still access, which tells me that the problem is the database somehow and not MySQL as a whole. Try check table and repair table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Losing database information
ALL, I have an issue where MySQL has lost all of the table information for an existing database, I do not know of anything that has changed and there was no maintenance being done in MySQL. Below you will find the error message as I receive it from MySQL. ANY ideas or suggestions on how to recover this database intact will be GREATLY appreciated. The .FRM files are intact and all permissions have been checked against a database that I CAN still access, which tells me that the problem is the database somehow and not MySQL as a whole. * Start Error ** mysql use rt3; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'ACL' Didn't find any fields in table 'Attachments' Didn't find any fields in table 'CachedGroupMembers' Didn't find any fields in table 'CustomFieldValues' Didn't find any fields in table 'CustomFields' Didn't find any fields in table 'GroupMembers' Didn't find any fields in table 'Groups' Didn't find any fields in table 'Links' Didn't find any fields in table 'Principals' Didn't find any fields in table 'Queues' Didn't find any fields in table 'ScripActions' Didn't find any fields in table 'ScripConditions' Didn't find any fields in table 'Scrips' Didn't find any fields in table 'Templates' Didn't find any fields in table 'TicketCustomFieldValues' Didn't find any fields in table 'Tickets' Didn't find any fields in table 'Transactions' Didn't find any fields in table 'Users' Database changed * End Error **
Ask for input during SQL script execution
All, Can someone point me in the right direction ? I am trying to right sql scripts for queries that will prompt for information. Here is the gist of it, I have a sql script that will query a database however I need the script to ask the user for IP address, and other information that will be different each time this script is run, is there a way to have the script take input from the user? Chris Hood
Best way to get Access DB structures into MySQL ??
What is the best way to take the structure of multiple tables in Access and get them re-created in MySQL without doing that all by hand? Is there a way to do the equivalent of a Show Create Table in Access, that I could then use in MySQL ?? Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary
RE: A new Machine
Well Actually the current Red Hat Release is Fedora Core 2 , which works very well. You can find it here http://www.redhat.com/fedora/ Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Monday, August 23, 2004 7:24 AM To: [EMAIL PROTECTED] Subject: Re: A new Machine Info [EMAIL PROTECTED] wrote: After 2 days in Microsoft HELL with my SQLsvr databases, I'm ready to = rob the piggy bank and build a new linux mysql server. This seem to be a good fruit of your repentance. :) I'm not rich, but what hardware and distro do you experts suggest? (My = current Win2K server is a dual p3-650, 1gb with the databases on 2 36gb = U160 10K drives. ) I've got no problem moving the drives out of that = system (especially since I just bought a new one...)-- (I'd put my = redhat 8 on it this afternoon, except it also runs my exchange server = and that's a different migration...)=20 Classical Red Hat distro is a dead end, because their Red Hat 9 distro is the last one and there are only commercial versions now. You can use any Linux distro you like because we suggest you to run binary versions downloaded from www.MySQL.com, and they are linked statically. The hardware is fine, disks are the bottleneck of any database, not the CPU. If the performance of the current system is fast enough for you - then newer system will not give you significant enhancement. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Brainstorming' time!
Ok unless I missed something here, wouldn't you just use the SELECT ... INTO OUTFILE Syntax found here http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html Just search for outfile and you can find it faster. You can run a query against a table and save the output into a file and in the case of creating excel type data, just save it into a file with the fields separated by commas, a CSV file in other words. Then use excel to open the file. Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, August 20, 2004 11:30 AM To: Scott Hamm; 'Mysql ' (E-mail) Subject: Re: Brainstorming' time! - Original Message - From: Scott Hamm [EMAIL PROTECTED] To: 'Mysql ' (E-mail) [EMAIL PROTECTED] Sent: Friday, August 20, 2004 12:02 PM Subject: Brainstorming' time! Ok. I'm looking into alternatives. I'm trying to figure out an alternative to mysql exporting into xls file. Is there any another way you can export into file and make it readable? What format do you use? I'm open to ideas from experienced database programmers :) I'm upgrading the whole database system from stupid Access database into SQL variant, whether it be MySQL or SQL server (MeowSoft) How do you export a MySQL table into an XLS file? ;-) You raised an interesting point - the whole issue of exporting data from MySQL - so I took a quick glance and couldn't see *anything* that looked like an export utility of the kind I've seen in many other programs and relational databases. Just about the only thing I saw was the mysqldump utility which basically generates the SQL needed to re-create and re-populate a table on another system. But I don't think that's what you're looking for and it clearly won't generate an XLS file. So I'm not sure how you'd even export to XLS. In the absence of an actual export utility - assuming I haven't missed one in the manual somewhere! - you are hosed. Unless of course you have programming skills; in that case you could write your own export utility to export data in any format you like. If you do that, you might consider sharing it with the rest of us when it is done, even if it isn't a full-function ultra-slick piece of code; it might still be useful to some of us if we ever need to export data. Or maybe there are some export utilities floating around for MySQL that are described somewhere other than the manual. I could well imagine users developing their own homegrown utilities and donating them to MySQL. I can also imagine professional developers writing a full-function export utility for MySQL; a Google search might turn those up. Rhino -- 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: Date Conversion Function
Well thanks to everyone that replied to this message, here are the main details. I was looking for this as help for a DBA here that is enslaved by MSSQL and was asking me what the equivalent would be, as I do not know what mm() does in mssql myself I cannot answer that question either, I sent her the link for the MySQL page that has all of the date/time functions listed and told her to figure it out. So anyway thanks a million, Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, July 29, 2004 12:27 PM To: Christopher L. Hood; '[EMAIL PROTECTED] ' Subject: RE: Date Conversion Function I do not know of an MM() date function in MS SQL, only mm used for the date part. What are you attempting to accomplish? -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 7/29/04 11:17 AM Subject: Date Conversion Function M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Conversion Function
M$ SQL server has a function MM that will do some date conversion, is there an equivalent in MySQL ?? Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexed Database still slow
Ok, I will be the first to say that I am learning about indexes, however it is my understanding that if I have a database with MANY rows and I wish my queries to be faster I should index my database. With that being said, I have 2 tables in my database that are being queried with a single query using a UNION these 2 tables combined are about 9 Million records (yes I said million). My query which is below takes about 1 minute to run, now some people would say that this isn't long, however when the 2 tables were sub 5 million it only took a matter of about 20 seconds to run, so I figure I need an index. So I have created an index called Main within both tables and added 6 columns to that index, most of the columns that are used in my query. Sorry for the long background, but here is the problem, my query DID NOT speed up at all. It still takes right at 1 minute per query, so indexing didn't buy me anything as far as I can tell. Can someone tell me how the indexes are supposed to be done ( to ensure that I did it correctly) and tell me if they think that it should have sped up or if there is a more efficient way to do my query. ###QUERY HERE ### Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND (PRTC_DIALUP.Date = 'one-date-here' OR PRTC_DIALUP.Date = 'one-day-earlier' OR PRTC_DIALUP.Date = 'one-day-later') UNION Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and (PRTC_DSL.Date = 'one-date-here' OR PRTC_DSL.Date = 'one-day-earlier' OR PRTC_DSL.Date = 'one-day-later') order by Full_Name, Time; ### END QUERY ### Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message.
RE: Indexed Database still slow
YAHOOO Shawn Green you were absolutely right, I basically just took your 6 union query from below, modified it to add back my specific data and fixed my index to only include the 2 fields that I needed and VOILA` it worked like a champ. My query times went from 1:05 to 0.32 seconds, thanks for all your help. Thanks to every one in the community that helped me out with this and other questions your knowledge in invaluable and cannot be gained strictly thru books, I will continue to use this mailing list and will help where I can. Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 8:41 AM To: Christopher L. Hood Cc: [EMAIL PROTECTED] Subject: Re: Indexed Database still slow I think the UNION is the right way to handle this, in fact, I would be tempted to break it into 6 UNIONS... more on that later. You really should concentrate mostly on coverage for fields used in your WHERE clauses, in this case: Framed_IP_Address and Date. Additional fields can be used to get data straight from the index but the docs say that they must be numeric (not character based). So, in the case of this query, those additional fields just make your index larger which takes longer to search. Try a two-field index and just this part of your subquery: Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-date-here' If I am right, that should return somewhere 2 seconds. This means that a 6-way union would return in somewhere near or below 12 seconds. In this case each query is doing an exact match on an index and the 6 queries unioned together should take less time than your 2 3-way queries. I call them 3 way as each half has to check for one of 3 dates. Also, if you need to ORDER BY the results of the UNION, you need to enclose each participating query in parentheses and put the ORDER BY clause after the last query. I went ahead and expanded your 2-query UNION into a 6-query UNION to illustrate: ( Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-date-here' ) UNION ( Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-day-earlier' ) UNION ( Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-day-later' ) UNION ( Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and PRTC_DSL.Date = 'one-date-here' ) UNION ( Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and PRTC_DSL.Date = 'one-day-earlier' ) UNION ( Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and PRTC_DSL.Date = 'one-day-later' ) ORDER BY Full_Name, Time; I agree that it will take some additional time to parse those 6 queries instead of just 2 but I believe that you won't be able to notice the difference. I would compare those 4 extra queries to the # of queries per second your system handles now to get a rough estimate of the additional overhead involved. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 07/28/2004 08:25:36 AM: Ok, I will be the first to say that I am
find out who was online at a given time
Ok, this may or may not be a tricky one I will try and be succinct in my statement. I have a database (mysql 4.0) with radius log entries for each day, we receive emails about Acceptable Use Abuses and must figure out exactly who was online with a certain IP address when the abuse occurred. As you will see below there are multiple starts and stops for any given IP address so here is the scenario: Problem: Spam Abuse IP of offender: 66.50.xxX.245 Date of offense: 2004-07-05 Time of offense: 16:15 Now if I query the database based on date and ip address, I get the following: Id Date Time Record TypeFull Name IP Address == = 349 2004-07-0511:21:08 Start [EMAIL PROTECTED] 66.50.xxX.245 345 2004-07-0511:21:09 Start [EMAIL PROTECTED] 66.50.xxX.245 413 2004-07-0511:22:32 Stop [EMAIL PROTECTED] 66.50.xxX.245 118984 2004-07-0517:22:26 Start [EMAIL PROTECTED] 66.50.xxX.245 149049 2004-07-0518:36:19 Stop [EMAIL PROTECTED] 66.50.xxX.245 90344 2004-07-0516:09:40 Start [EMAIL PROTECTED] 66.50.xxX.245 90380 2004-07-0516:09:40 Start [EMAIL PROTECTED] 66.50.xxX.245 97630 2004-07-0516:28:20 Stop [EMAIL PROTECTED] 66.50.xxX.245 97671 2004-07-0516:28:20 Stop [EMAIL PROTECTED] 66.50.xxX.245 97598 2004-07-0516:28:20 Stop [EMAIL PROTECTED] 66.50.xxX.245 now of course I changed the usernames and modified the IP for this mailing but that doesn't matter, now, the time field in the Database IS a time data type. What I need to be able to do is find the start before the offense time, and the stop after the offense time so I know that the person with the start and the stop is the one that committed the abuse. I haven't actually put code to bits yet, because I am not exactly sure how to go about creating this logic code. I don't think I can just say if on Date , if $timefield time of offense and $timefield time of offense; return some stuff. So any help on how to start with this would be greatly appreciated. Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900
Query from mulitple tables where data will only be in one table but that table unkown
Ok, So here is what I am trying to do, I have 2 tables one with DSL IP addresses and one with Dialup addresses. I need to be able to query both tables and extract the information by IP address but I will not know which table the data is in before I do the search. Basically I want to have a query that states look in both tables and if the ip is found in either then give me some fields from that table What I have tried already is things like this: Select DISTINCT DIALUP.Full_Name, DIALUP.Framed_IP_Address, DSL.Full_Name, DSL.Framed_IP_Address From DIALUP, DSL Where DSL.Framed_IP_Address = 65.xxx.196.175 or DIALUP.Framed_IP_Address = 65.xxx.196.175 LIMIT 500 However that doesn't work because it returns rows from the table that the IP address is NOT in as well, because of the self join that is being done. All help will be GREATLY appreciated. Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message.
