Neverside

PHP/MySQL Efficiency Issues

Well, currently my website features a load time that is about the same as Neversides, which is great. However, my website also doesn't yet feature any user-related functions, which take up a bulk of load times.

Recently, I attempted to impliment such functions, and to my dissopointment the load times were horrifying. At first, I thought this was just PHP being an arse like it does on occasion. Digging deeper, I found the problem to be MySQL related. With the user funtions I was forced to impliment 2 extra queries per page (session handeling). These TWO queries caused my MySQL load times to go from ~0.003 to ~0.07/0.1, which had a huge impact on my overal load times. My PHP load times were, surprisingly, not affected on a large scale.

The two queries added are as follows:

Code:

UPDATE sessions SET location = "'.$location.'", ip = "'.$ip.'", userid = "'.$userid.'",time = "'.time().'" WHERE sessid = "'.$session.'"

AND

DELETE FROM sessions WHERE time < "'.$time.'"
($time = time()-900 or 15 minutes)

I'm curious if anyone knows why the load times were so jurastically affected by such a small addition.

Running: MySQL 4.1 (InnoDB) / PHP5 / Windows XP

Thanks

___________________

I don't suffer from insanity; I enjoy every minute of it.
Unintended Theory | Cacrew v4

Last edited by Kickboy, March 12th, 2005 10:25 PM (Edited 1 times)

After looking into it, the problem is with the UPDATE query. The DELETE query has little to no impact on load times, but the UPDATE query has loads of impact (Even when I add LIMIT 1 to the statement).

Any suggestions?

___________________

I don't suffer from insanity; I enjoy every minute of it.
Unintended Theory | Cacrew v4

update queries are usually pretty slow in general. basically because it is like a delete and an insert if you think about it.

___________________

Neverside Development Director
PHP Snippets
BigToach.com - IT WORKS, TOACHY!

Ah, figured it out.
I changed the table type from InnoDB to MyISAM. The speed impacts are virtually nothing now.

Kind of annoying, but it works.

___________________

I don't suffer from insanity; I enjoy every minute of it.
Unintended Theory | Cacrew v4

You could do the session update stuff as a shutdown function so that it doesnt effect anything.

___________________

http://www.philbrodeur.com - Expert PHP Development and Tutorials

Originally posted by Kickboy12:

Ah, figured it out.
I changed the table type from InnoDB to MyISAM. The speed impacts are virtually nothing now.

Kind of annoying, but it works.

what's the difference? I always use MyISAM but I never even considered an alternative

___________________

The capacity of human beings to bore one another seems to be vastly greater than that of any other animal. - H. L. Mencken (1880 - 1956)

Children seldom misquote you. In fact, they usually repeat word for word what you shouldn't have said.

transacations support is one.

___________________

http://www.philbrodeur.com - Expert PHP Development and Tutorials

phil, shutdown functions are still executed before content is sent.

Quote:

what's the difference? I always use MyISAM but I never even considered an alternative

MyISAM is generally faster, but InnoDB supports more features such as foreign keys and transactions.

Quote:

Ah, figured it out.
I changed the table type from InnoDB to MyISAM. The speed impacts are virtually nothing now.

You can get even more speed by changing the table type from MyISAM to HEAP.

___________________

Jeremie - Used to be the Director of Community Development

Originally posted by Jeremie:

You can get even more speed by changing the table type from MyISAM to HEAP.

Are there any major downsides to HEAP?
If not, I might consider using it. Otherwise, MyISAM works fine.

___________________

I don't suffer from insanity; I enjoy every minute of it.
Unintended Theory | Cacrew v4

HEAP/Memory tables are stored in memory. They lose data whens erver is restarted.

MEMORY tables use a fixed record length format.
MEMORY doesn't support BLOB or TEXT columns.
MEMORY doesn't support AUTO_INCREMENT columns before MySQL 4.1.0.

If you know what you are doing and realize the implications of storing data in memory and possible server load, by all means use them.

My bad, aparently that was very outdated info on shutdown functions. The work before like php 4.1 as I said

___________________

http://www.philbrodeur.com - Expert PHP Development and Tutorials