Jump to content

Simple MySql query question - I hope


netfrugal

Recommended Posts

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!
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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. 


Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

[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.


Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.