MySQLTalk.org

Mark Unwin · Guest

OK guys, here goes....

My vbscript audits Windows PC(s), and inserts/updates data about the PC
into the MySQL database. One of the tables in the database is for
software. Recorded are the details of the installed software, along with
an id, a uuid for the PC, the date first discovered, the date last
discovered. The fields software_timestamp & software_first_timestamp are
simple integers that I get from vbscript - calculated by
dt = Now()
timestamp = Year(dt) & Right("0" & Month(dt),2) & Right("0" & Day(dt),2)
& Right("0" & Hour(dt),2) & Right("0" & Minute(dt),2) & Right("0" &
Second(dt),2)

The software_timestamp field should reflect the timestamp for the latest
audit (all audits are in another table with id, PC and audit_timestamp).
The software_first_timestamp is recorded only upon the initial insert.
So it reflects the first time a software package was detected.

This them forms an audit trail - if an entry exists with a
software_timestamp != the latest audit, then the software was removed -
it was detected on the software_first_timstamp date, but then not
detected on the software_timestamp date.

The table looks like this

mysql> describe software;
+---------------------------+---------------------+------+-----+--------
-+----------------+
| Field | Type | Null | Key | Default
| Extra |
+---------------------------+---------------------+------+-----+--------
-+----------------+
| software_id | int(10) unsigned | | PRI | NULL
| auto_increment |
| software_uuid | varchar(100) | | |
| |
| software_name | varchar(100) | | |
| |
| software_version | varchar(50) | | |
| |
| software_location | varchar(200) | | |
| |
| software_uninstall | varchar(200) | | |
| |
| software_install_date | varchar(20) | | |
| |
| software_publisher | varchar(100) | | |
| |
| software_install_source | varchar(200) | | |
| |
| software_system_component | char(2) | | |
| |
| software_url | varchar(100) | | |
| |
| software_comment | varchar(200) | | |
| |
| software_timestamp | bigint(20) unsigned | | | 0
| |
| software_first_timestamp | bigint(20) unsigned | | | 0
| |
+---------------------------+---------------------+------+-----+--------
-+----------------+
14 rows in set (0.00 sec)

The data is submitted to MySQL thru a .php form.
I know all the values for the current software package, along with the
value of software_timestamp (this is the timestamp from the last audit -
retrieved from the audits table). I also know the uuid of the PC, and
the timestamp for the current audit.
The current logic goes like this (in the .php script) -

$sql = "SELECT count(software_uuid) AS count FROM software WHERE
software_uuid = '$uuid' AND ";
$sql .= "software_name = '$software_name' AND software_timestamp '$software_timestamp'";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
if ($myrow['count'] == "0"){
// Insert a new entry into the DB
$sql = "INSERT INTO software (software_uuid, software_name,
software_version, ";
$sql .= "software_location, software_uninstall,
software_install_date, software_publisher, ";
$sql .= "software_install_source, software_system_component,
software_url, software_comment, ";
$sql .= "software_timestamp, software_first_timestamp ) VALUES (";
$sql .= "'$uuid','$software_name','$software_version', ";
$sql ."'$software_location','$software_uninstall','$software_install_date','$s
oftware_publisher',";
$sql ."'$software_install_source','$software_system_component','$software_url'
,'$software_comments','$timestamp','$timestamp')";
$result = mysql_query($sql);
} else {
// Already in DB - flag as detected
$sql = "UPDATE software SET software_timestamp = '$timestamp'
WHERE software_uuid = '$uuid' AND ";
$sql .= "software_name = '$software_name' AND software_timestamp '$software_timestamp'";
$result = mysql_query($sql);
}

This all works fine, but is quite slow (I have similar logic for about
40 tables...).
I would like to replace all the logic with ONE sql statement.
I'm thinking a REPLACE so that if the record exists, it is simply
replaced, with the same data, and an updated software_timestamp - to
reflect it was seen in the latest audit.
If the record doesn't exist, it is inserted.
Now the issue - how do I do a REPLACE, but keep the value from
software_first_timestamp ?
If the record does not exist, I need to use the value of the current
audit (software_timestamp) for software_first_timestamp.
If the record does exist, I need the value of software_first_audit to
stay the same.
So how can I do that in one sql statement ?

Maybe I can use software_first_timestamp = software_first_timestamp -
and if it is not already set (in the case of an insert), it would use
the default value. Then, after all the software for the current audit is
done, set any software_first_timestamp(s) with the default value, to the
value of the current audit (software_timestamp)?

Failing that, how can I improve the speed of this ?

Thanks,
Mark Unwin.

*************************************************************************************************
This message and any attachments, or any part
of it is intended solely for the named addressee.

Reading, printing, distribution, storing, commercialising
or acting on this transmission or any information it contains, by anyone other than the addressee, is prohibited. If you have received this message in error, please destroy all copies and notify
Qld Police Credit Union Ltd on +61 7 3008 4444 or by replying to the sender.

This message may contain legally privileged and
confidential information, and/or copyright material
of QPCU or third parties.

QPCU is not responsible for any changes made
to a document other than those made by QPCU,
or for the effect of the changes on the document's meaning.
You should only re-transmit, distribute or commercialise
the material if you are authorised to do so.

Any views expressed in this message are
those of the individual sender. You may not rely on this message as advice unless subsequently confirmed by fax or letter signed by an Officer or Director of QPCU, or
an Authorised Representative QPCU.

QPCU advises that this e-mail and any attached files should be scanned to detect viruses. QPCU accepts no liability for loss or damage (whether caused by negligence or not) resulting from the use of any attached files.

Information regarding Privacy can be found at the QPCU web site. ( www.qpcu..org.au )

General Advice Warning

Any advice has been prepared without taking into account your particular objectives, financial situation or needs. For that reason, before acting on the advice you should consider the appropriateness of the advice having regard to your own objectives, financial situation and needs. Where the advice relates to the acquisition, or possible acquisition, of a particular financial product, you should obtain a Product Disclosure Statement relating to the product and consider the Product Disclosure Statement before making any decision about whether to acquire the product.
*************************************************************************************************

Guest · Posted: Wed Dec 07, 2005 9:47 am Post subject: Re: SQL problem

Perhaps you could try:

ALTER TABLE software ADD KEY(software_uuid, software_name);

Otherwise mysql has to run a full table scan for every select and every update.

regards,
Peter

--- Mark Unwin <munwin (AT) qpcu (DOT) org.au> wrote:

Mark Unwin · Guest

Problem is - what if Acrobat was installed, removed, then installed
again ?
You would have two keys the same.
I do have the following indexes on the table though.
Id
Id + UUID
Id + UUID + timestamp

Mark.

-----Original Message-----
From: toomuchphp-mysqlugbrisbane (AT) yahoo (DOT) com
[mailto:toomuchphp-mysqlugbrisbane (AT) yahoo (DOT) com]
Sent: Wednesday, 7 December 2005 4:10 PM
To: Mark Unwin; brisbane (AT) ug (DOT) mysql.org
Subject: Re: SQL problem

Perhaps you could try:

ALTER TABLE software ADD KEY(software_uuid, software_name);

Otherwise mysql has to run a full table scan for every select and every
update.

regards,
Peter

--- Mark Unwin <munwin (AT) qpcu (DOT) org.au> wrote:

Peter Hodge · Guest

The key doesn't need to cover the whole 'where' clause - just enough to reduce
the table scan to a handful of rows. Mysql will use the indexes to narrow the
search results as much as possible, and then scan the matching rows using a
full table scan,
so if the result set can be narrowed to 20 rows using an index, then only 20
rows need to be read during the full table scan, which is still much (much)
faster than a full table scan on even 5000 rows. The keys don't need to be
unique, as long as they can be used to narrow the search results.

However, in order for mysql to use an index, the 'where' clause must include
comparisons for the columns on the left of the index before it can be used for
columns on the right of the index - Mysql can't use the software_uuid and
software_name indexes unless you include the software_id (the column on the
left of the indexes) in the 'where' clause too. If you run
the select preceding it with the word 'DESCRIBE' (so "DESCRIBE SELECT coun...")
you can see how mysql will try and use indexes to find the results. In this
case the 'key' column will be NULL, indicating that mysql is unable to use any
indexes, and therefore the 'rows' column will contain the total number of rows
in the software table - this is the number of rows which will be read during
the full table scan. If you add an index on software_uuid and software_name
(together or separately) and run the DESCRIBE SELECT again, the result should
state that an index will be used, and the 'rows' column will now be far smaller
(as small as mysql can narrow the search by using an index first).

As a side note, the 2nd and 3rd of the 3 indexes you mentioned:
1. Id
->2. Id + uuid
->3. Id + uuid + name
will never offer a performance gain, because you need to include the
software_id in order to use any of these indexes, and once you include the
software_id, the 1st index will be used and the search results narrowed to 1
row.

I hope that's all clear enough ...

regards,
Peter

-- Mark Unwin <munwin (AT) qpcu (DOT) org.au> wrote:

Mark Unwin · Guest

WOW - Thank you Peter.
I shall redo indexes 2 & 3 to remove the id field.
Shall let you know how it goes.

I still would like to reduce the logic to one SQL statement though...

Mark.

-----Original Message-----
From: Peter Hodge [mailto:toomuchphp-mysqlugbrisbane (AT) yahoo (DOT) com]
Sent: Thursday, 8 December 2005 12:40 AM
To: brisbane (AT) ug (DOT) mysql.org
Subject: RE: SQL problem

The key doesn't need to cover the whole 'where' clause - just enough to
reduce the table scan to a handful of rows. Mysql will use the indexes
to narrow the search results as much as possible, and then scan the
matching rows using a full table scan, so if the result set can be
narrowed to 20 rows using an index, then only 20 rows need to be read
during the full table scan, which is still much (much) faster than a
full table scan on even 5000 rows. The keys don't need to be unique, as
long as they can be used to narrow the search results.

However, in order for mysql to use an index, the 'where' clause must
include comparisons for the columns on the left of the index before it
can be used for columns on the right of the index - Mysql can't use the
software_uuid and software_name indexes unless you include the
software_id (the column on the left of the indexes) in the 'where'
clause too. If you run
the select preceding it with the word 'DESCRIBE' (so "DESCRIBE SELECT
coun...") you can see how mysql will try and use indexes to find the
results. In this case the 'key' column will be NULL, indicating that
mysql is unable to use any indexes, and therefore the 'rows' column will
contain the total number of rows in the software table - this is the
number of rows which will be read during the full table scan. If you
add an index on software_uuid and software_name (together or separately)
and run the DESCRIBE SELECT again, the result should state that an index
will be used, and the 'rows' column will now be far smaller (as small as
mysql can narrow the search by using an index first).

As a side note, the 2nd and 3rd of the 3 indexes you mentioned:
1. Id
->2. Id + uuid
->3. Id + uuid + name
will never offer a performance gain, because you need to include the
software_id in order to use any of these indexes, and once you include
the software_id, the 1st index will be used and the search results
narrowed to 1 row.

I hope that's all clear enough ...

regards,
Peter

-- Mark Unwin <munwin (AT) qpcu (DOT) org.au> wrote: