Simple MySql query question - I hope

[netfrugal]

After optimizing my database tables my queries are running smoother and a little bit faster.

I have thousands of rows of data to view and everyday I need to wait for a while for the results to show up on the browser.

My question is: Does paginating speed up the query? Or is there another way to speed up the query?

Currently all results are showing in a repeating table, so I have alot of scrolling to do. But I'm open to other ideas for possible faster results.

thanks!

[roopurt18]

Pagination will help a lot.  Basically you want to append LIMIT <offset>, <return> to your SQL statement.

<offset> is an integer telling MySQL how far through the return set to actually start returning.
<return> is the number of records to actually return.

Let's say you want to display 25 records per page.

Page 1: LIMIT 0, 25
Page 2: LIMIT 25, 25
Page 3: LIMIT 50, 25

Page N: LIMIT 25 * (N - 1), 25

If you want to display Z records per page, the general formula will be:
Page N: LIMIT Z * (N - 1), Z

Been a while since I did pagination, but I believe this all to be correct.

[Daniel0]

There is tutorials about pagination on PHPFreaks' main site.

[netfrugal]

Yes, I read the tutorial on PHPFreaks.  It is very helpful.  But it seems there is a limitation to query speeds even when using pagination.  I have over 11 million records to query.  With or without pagination the browser still takes about the same time to load.

I am guessing that the query is actually looking through the entire database to give the pagination results, regardless that only 15 results are to be shown on the page at a time.

To my limited experience in the pagination world, there's no other way to make the query execute faster.

Any ideas?

[alpine]

Could you show us the query you are using ?

[roopurt18]

Do your tables have indexes used by this particular query?

(EDIT)  Can we see the query and the table definition?

[netfrugal]

According to phpmyadmin I have a few indexes.  Here is the sql for creating that table:

CREATE TABLE `log` (
  `datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `ident` varchar(20) NOT NULL default '',
  `ip` varchar(40) default NULL,
  `url` blob NOT NULL,
  `what` blob NOT NULL,
  `how` varchar(40) NOT NULL default '',
  `size` varchar(9) NOT NULL default '',
  `ip_unresolved` varchar(15) NOT NULL default '',
  KEY `ident` (`ident`),
  KEY `datetime` (`datetime`,`ident`)
) TYPE=MyISAM;



And here is the Query:

SELECT datetime, ident, ip, what, url FROM log WHERE ident = '$username' and datetime between '$begindate 00:00:00' and '$enddate 23:59:59'


The tables are from DansGuardian: http://dansguardian.org/ for web content filtering. 

[Ninjakreborn]

SELECT datetime, ident, ip, what, url FROM log WHERE ident = '$username' and datetime between '$begindate 00:00:00' and '$enddate 23:59:59'

[netfrugal]

were you repeating what I put?  or is there something different?

[Ninjakreborn]

SELECT datetime, ident, ip, what, url FROM log WHERE ident = '$username' and datetime between '$begindate 00:00:00' and '$enddate 23:59:59'
Why do you have the dates there 2 times
It has the date, then a number
it's better to store all that in a variable and pass it, it's quicker.

What you are trying to do is called script optimization
Optimize your script, remove useless code, double code, code that redoes it self
If you have 150 lines of code, chances are if you try, it could be reduced to 100 lines of code
post all of your code, on the slow page.

Let me see if I can optimize it for you some.

[netfrugal]

Ah yes, I didn't explain the dates.  The form fields: username, begindate, enddate search for certain employees and the dates of their internet usage.  Unless it is not necessary I put the 00:00:00 into the code to search between time of day as well.

However, I am very open to anything better!

[Ninjakreborn]

I told you show the entire page, if I am still ona nd helping people when it's on, I will try and optimize it some for you.

[roopurt18]

[quote author=businessman332211 link=topic=110266.msg446371#msg446371 date=1159912937]
SELECT datetime, ident, ip, what, url FROM log WHERE ident = '$username' and datetime between '$begindate 00:00:00' and '$enddate 23:59:59'
Why do you have the dates there 2 times
It has the date, then a number
it's better to store all that in a variable and pass it, it's quicker.
[/quote]
There is no practical advantage to typing:
[code]
<?php
$sql = "SELECT datetime, ident, ip, what, url FROM log WHERE ident = '$username' AND datetime BETWEEN '$begindate 00:00:00' AND '$enddate 23:59:59'";
?>
[/code]
or typing:
[code]
<?php
$begindate = $begindate . " 00:00:00";
$enddate = $enddate . " 23:59:59";
$sql = "SELECT datetime, ident, ip, what, url FROM log WHERE ident = '$username' AND datetime BETWEEN '$begindate' AND '$enddate'";
?>
[/code]

[quote author=businessman332211 link=topic=110266.msg446371#msg446371 date=1159912937]
What you are trying to do is called script optimization
Optimize your script, remove useless code, double code, code that redoes it self
If you have 150 lines of code, chances are if you try, it could be reduced to 100 lines of code
post all of your code, on the slow page.

Let me see if I can optimize it for you some.
[/quote]
Reducing your code from 150 lines to 100 also isn't likely to provide any significant performance gains unless you are dramatically overhauling your current [i]algorithm[/i] for handling the data.

[Ninjakreborn]

That's not true.

I didn't say anything about rewriting that, I was not sure, as far as reducing code, it definitely increases performance.  Severely, when you are able to.  If you look at code that has for instance 800 lines of code.
If you really try to optimize it for performance, then you could find a way to reduce it down to around 500, there is normally useless code put in there, or some unnecessary code, or duplicate code written 2 different ways but actually doing the same things. Sometimes you can just take a few lines of code, and rewrite it in a different manner to take less room.

Yes it helps a lot, it also has to do with what pre-built functions are used, user functions(how tehre set up), number of database interactions, and a host of other things.  But code reduction is always one of the ways.

[roopurt18]

You are giving the wrong impression that shorter code is faster, which may or may not be true depending on the situation.

(EDIT) netfrugal, before you go hunting through your code trying to optimize it, try dumping the actual query being ran to the browser.  Copy and paste it into phpMyAdmin and see how long it takes for the query to run.  Then add the LIMIT x, y to the end of it and do the same thing.

[Ninjakreborn]

It is not the only thing that matters.  It also has to do with the "way" the code is written, what functions are used, database interaction, sessions, and a host of over things.  Including amount of xhtml, javascript, and css, and a few other factors.

[roopurt18]

The "way" the code is written, or the algorithm used for processing the data, is the determining factor in how quickly your page will display, not the length of the .php file.

[Ninjakreborn]

it has to do with everything.

Having a 6,000,000 page php will slow it down, then go back to school.

No differnet in 6,000,000 than having 500 if you can optimize the page, rewrite the code,
I already said it didn't entirely have to do with the length of the code, but also teh way it's written, as well as database interaction and other stuff.
If you have 600 lines of code, and you rewrite it to be more efficient, and less lines of code, it's going to run faster, if this algorithm doesn't work for you, then get over it, you can't change the way php works.

[Ninjakreborn]

alright sorry, shouldn't say things like that here
http://talks.php.net/show/perf_tunning/

http://phplens.com/lens/php-book/optimizing-debugging-php.php

http://www.dynamicwebpages.de/tutorial/50/

All of these deal with php performance.

How to rewrite your scripts to be better optimized, how to make the quicker, and more efficient, without loosing functionality.

[roopurt18]

I would like to introduce you to my good friend Big O:
http://en.wikipedia.org/wiki/Big_O_notation#Common_orders_of_functions

particularly the section:
http://en.wikipedia.org/wiki/Big_O_notation#Common_orders_of_functions

Here is a little something I found through google:
http://www.cs.wisc.edu/~hasti/cs367-common/notes/COMPLEXITY.html

Your example of a 6 million line .php script is a little absurd to say the least.  Further, I use the word algorithm because every script / program is the implementation of an algorithm.  Here are two algorithms:

Algorithm 1
 [list][*]Get 1k records
 [*]Loop over records and do process 1
 [*]Loop over records and do process 2
 [*]Loop over records and do process 3[/list]

Algorithm 2
 [list][*]Get 1k records
 [*]Loop over records
      [list][*]For each record, do process 1, then do process 2, then do process 3[/list]
   [/list]

Both algorithms work with 1k records.  The first loops 3 thousand times, the second loops 1 thousand times.  Which algorithm is faster?

FYI, most institutions offer [b]Introduction to Algorithms[/b] or something similar as their first computer science course.

[Ninjakreborn]

Ok it look's like I was a little off.  I apologize.  Also thank you for the clarification, I thought that it was the other way around, thanks.

[roopurt18]

From http://phplens.com/lens/php-book/optimizing-debugging-php.php
[quote]From the above figures, you can see that biggest speed improvements are derived not from tweaking the code, but by simple global optimizations such as ob_start(), or using radically different algorithms such as HTML caching.[/quote]

Also, no worries.  It's a fairly common misconception that shorter implies faster.  Sometimes it is true, sometimes it isn't.  Also, a determining, often variable factor, in this entire thing is the size of the data set.

An algorithm that runs at O(N * N) might actually outperform one that runs at O(log(N)) for small record sets.  But as the record set approaches infinity, or scales upward, the log(N) algorithm will win every time.  Garaunteed.

[Ninjakreborn]

I will keep this stuff in mind when writing my programs, thanks.

[roopurt18]

I wonder if the OP plans on returning to this thread...

[Ninjakreborn]

He probably got bored, after reading more than the 4th post.  Atleast I got helped in the process, thanks.