Jump to content


Photo

Simple MySql query question - I hope


  • Please log in to reply
24 replies to this topic

#1 netfrugal

netfrugal
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 02 October 2006 - 05:31 PM

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!

#2 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 02 October 2006 - 05:40 PM

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.
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#3 Daniel0

Daniel0
  • Staff Alumni
  • Advanced Member
  • 11,956 posts

Posted 02 October 2006 - 05:53 PM

There is tutorials about pagination on PHPFreaks' main site.

#4 netfrugal

netfrugal
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 03 October 2006 - 04:17 PM

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?

#5 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 03 October 2006 - 05:11 PM

Could you show us the query you are using ?

#6 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 03 October 2006 - 05:30 PM

Do your tables have indexes used by this particular query?

(EDIT)  Can we see the query and the table definition?
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#7 netfrugal

netfrugal
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 03 October 2006 - 08:51 PM

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. 




#8 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 03 October 2006 - 09:27 PM

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

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#9 netfrugal

netfrugal
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 03 October 2006 - 09:59 PM

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

#10 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 03 October 2006 - 10:02 PM

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.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#11 netfrugal

netfrugal
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 03 October 2006 - 10:29 PM

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!

#12 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 03 October 2006 - 10:29 PM

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.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#13 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 03 October 2006 - 11:07 PM

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.

There is no practical advantage to typing:
<?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'";
?>
or typing:
<?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'";
?>

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.

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 algorithm for handling the data.



PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#14 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 04 October 2006 - 04:19 PM

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.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#15 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 04 October 2006 - 05:23 PM

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.
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#16 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 04 October 2006 - 05:24 PM

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.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#17 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 04 October 2006 - 05:30 PM

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.
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#18 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 04 October 2006 - 05:37 PM

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.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#19 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 04 October 2006 - 05:39 PM

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

http://phplens.com/l...bugging-php.php

http://www.dynamicwe...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.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#20 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 04 October 2006 - 05:55 PM

I would like to introduce you to my good friend Big O:
http://en.wikipedia....rs_of_functions

particularly the section:
http://en.wikipedia....rs_of_functions

Here is a little something I found through google:
http://www.cs.wisc.e...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
 
  • Get 1k records
  • Loop over records and do process 1
  • Loop over records and do process 2
  • Loop over records and do process 3

Algorithm 2
 
  • Get 1k records
  • Loop over records
         
  • For each record, do process 1, then do process 2, then do process 3
   
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 Introduction to Algorithms or something similar as their first computer science course.


PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users