Need Help with Slow Query

12Bo

I am having an issue with a login log table that I was wondering if I can get some assistance on. I have a table with 1.8 million records and the query (below) runs really slow. Any advice on how to resolve the slowness.

QUERY
I am trying to get the most recent log (last log)

* for each computer in the database.

SELECT DISTINCT userlogin.machineName, userlogin.DateTime, userlogin.Uname FROM vvcomputers.vvuserlogin ORDER BY DateTime desc

** This Query takes 23.5644 seconds to run when I try getting 500,000 records (it times out when I query the entire table).

TABLE STRUCTURE

CREATE TABLE IF NOT EXISTS `userlogin` (
  `index` int(11) NOT NULL AUTO_INCREMENT,
  `MachineName` varchar(255) CHARACTER SET ascii NOT NULL,
  `DateTime` datetime NOT NULL,
  `Uname` text CHARACTER SET ascii NOT NULL,
  PRIMARY KEY (`index`),
  KEY `dateTimeIndex` (`DateTime`),
  KEY `machineNameIndex` (`MachineName`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1827287 ;

I recently added the dateTimeIndex and machineNameIndex keys but that didn't help much.

bradgrafelman

Welcome to PHPBuilder!

Can you do us one favor and re-run that same query but add the word 'EXPLAIN' in front of it and post the output?

12Bo

id: 1
select_type: SIMPLE
table: userlogin
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1824969
Extra: Using temporary; Using filesort

bradgrafelman

You might wait for a seasoned DBA guru to reply (as I would not use those words to describe myself), but here's my initial guesses/thoughts:

Adding a dateTimeIndex probably wouldn't help much.
Adding an index on MachineName should help a bit, but it might help even more if you store machine names in a separate table and link to that table using an integer ID value. When looking for DISTINCT rows, it's a lot quicker for MySQL to compare two integers rather than two VARCHAR's.
Same comment as above for Uname.

I'm guessing this is one situation where normalization might improve the efficiency (read: lower response time) of the query.

12Bo

Thanks brad. I will see what I can based on your reply. Unfortunately, the script that is creating these logs is somewhat out of my hands. I might have to go back and write something that someone has already created.

Thanks again I really appreciate your quick and thoughtful feedback.

bradgrafelman

12Bo wrote:
Unfortunately, the script that is creating these logs is somewhat out of my hands.

Ah, well that would make things a bit more difficult if you need to go re-structuring the schema.

If nothing else, you could try modifying the script to instead call a procedure with the necessary info so that the DB could take care of handling the details (e.g. if you restructured the table, all you'd do is update the procedure without any changes needed in the other script).

Res

Lets try the easy way. The main slow down is that you aren't using any of the indexes you have setup resulting in a full table scan. You only need one record for each machine, so there is no reason for combing over 1.8 million of them.

There are a few ways we can do this, I think the easiest to explain would be to break it up into multiple queries.

1) Get machine names
2) loop over machine names
3) get last log for each machine

This way you can actually use some of the indexes you have on the table.

To get the machine names I would use a GROUP BY instead of select distinct as the distinct will slow things down. Once you have those loop over them and get the log details where name equals the current machine. You will want to order by date desc and limit the records to one, so you are getting just the last log entry for that machine.

This should take you down to a few milliseconds for each machine. If not we can go from there.

12Bo

I received a response from r937 from DevShed:

ALTER TABLE userlogin
DROP INDEX machinenameindex;

ALTER TABLE userlogin
ADD INDEX machinename_datetime ( machinename,datetime )

SELECT machinename
, MAX(datetime) AS last_login
FROM userlogin
GROUP
BY machinename
ORDER
BY last_login -- least recently used will come first

12Bo

His suggestions worked out really well.

Res

Very nice, that was the other option I spoke of but would have needed to test before I posted with certainty. I was also hesitant to use a MAX(date), what were you able to cut the processing time down to?

Also don't forget to set the thread to resolved.

12Bo

The original query took several minutes but and his suggestions take about 5-10 seconds.