optimizing the queries

jonsof

I feel that my database speed is getting slower and slower then i must do something to come up with this.

Any method?

Any help would be appreciated

Frederick

Other then purging out the old data, setup your queries to be more specific about what you want to retrieve. I have had to do this for a customer in the past.

jonsof

my queries are already specific about what i want to retrive and i also want to keep a kind of archive. Is there any other method?

mattr

Sounds like the queries are not using indexes. Check your query plans.

Frederick

You can set a limit on the query results that will get displayed on the page and a link to an archieve page that limits results per page and a next/previous links.

For main results page:

$sql = mysql_query("SELECT * FROM tablename order by id desc LIMIT 30",$db)
or die(mysql_error());

For archeive page with [ Next ] [ Previous ] links:

<?php
if(!isset($start)) $start = 0;

$csql = mysql_query("SELECT count(*) as count FROM tablename",$db)
or die(mysql_error());
$c = mysql_fetch_array($csql);
$numrows = $c['count'];

if($start > 0)
echo "[ <a href=page.php?start=" .($start - 30).">Previous</a> ]
&nbsp&nbsp&nbsp";
if($numrows > ($start + 30))
echo "[ <a href=page.php?start=".($start + 30).">Next</a> ]";

echo "<br><br>";

$sql = mysql_query("SELECT * FROM tablename order by id LIMIT " . $start . ", 30",$db)
or die(mysql_error());
?>

jweissig

Hi,

This is a little big of an open ended questions. We need more information before anyone can make a answer.

what is your database design
what queries are you using to extract the information
how much information are we talking about
what is an expectable query time / how much traffic does you database see?

Thanks about it.
- justin

jonsof

here is my whole database

CREATE DATABASE announcements_system;

USE announcements_system;

CREATE TABLE users (
userid int not null auto_increment,
username varchar(30) not null,
password varchar(40) not null,
fname  varchar(30) not null,
lname varchar(30) not null,
email varchar(30) not null,
phone_number varchar(20) not null,
user_level tinyint(2) not null,
ip varchar(20) not null,
PRIMARY KEY (userid) );

CREATE TABLE categories (
categoryid int not null auto_increment,
category VARCHAR(70) not null,
description VARCHAR(220) not null,
primary key (categoryid)) ;

CREATE TABLE post (
postid int NOT NULL AUTO_InCREMENT,
userid int not null,
typeid tinyint not NULL,
languageid tinyint not NULL,
relevant_link VARCHAR(150) not null,
image VARCHAR(20) not null,
date_posted DATE not null,
expire_date DATE not null,
mid_date bigint not null,
last_update date not null,
primary key (postid) );

CREATE TABLE announcement (
postid int not NULL,
title VARCHAR(250) not null,
categoryid int not null,
post text not null,
primary key (postid) );

CREATE TABLE conference (
postid int not NULL,
title VARCHAR(250) not null,
date date not null,
organization text not null,
post text not null,
place VARCHAR(70) not null,
date_description VARCHAR(80) not null,
primary key (postid) );

CREATE TABLE exhibition (
postid int not NULL,
title VARCHAR(250) not null,
subtitle VARCHAR(250) not null,
date date not null,
time VARCHAR(30) not null,
text text not null,
place VARCHAR(70) not null,
entrance VARCHAR(50) not null,
contact VARCHAR(100) not null,
organization VARCHAR(100),
date_description VARCHAR(80) not null,
primary key (postid) );

CREATE TABLE seminar (
postid int not NULL,
department VARCHAR(50) not null,
domain VARCHAR(70) not null,
spokesman VARCHAR(70) not null,
title VARCHAR(250) not null,
subject VARCHAR(250) not null,
overview text not null,
date date not null,
time VARCHAR(30) not null,
room VARCHAR(70) not null,
organization text not null,
school VARCHAR(50) not null,
affiliation VARCHAR(100) not null,
primary key (postid) );

CREATE TABLE types (
typeid tinyint not NULL AUTO_INCREMENT,
type VARCHAR(30),
primary key(typeid));

CREATE TABLE languages (
languageid tinyint not NULL AUTO_INCREMENT,
language VARCHAR(20),
primary key(languageid));

CREATE TABLE user_type (
userid int not null,
typeid int not NULL,
PRIMARY KEY (userid, typeid));


CREATE TABLE user_category (
userid int not null,
categoryid int not null,
PRIMARY KEY (userid, categoryid));

CREATE TABLE school (
schoolid smallint not null AUTO_INCREMENT,
school VARCHAR(50) not null,
department VARCHAR(50) not null,
PRIMARY KEY(schoolid));

The table post containts mostly the information

Obviously, i have set limits by WHERE condition. But i need an optimization!

jonsof

any ideas?

jweissig

hi,

what querys are you using... and how much information do you have... i.e. how many rows are you returning.

do you have indexs?

without this information there is notthing anyone can do.

jonsof

Look, all my queries returns 20-30 rows approximately. The 'heavy' table is the "post", "announce", "conference", "exhibition", "seminar".

post is like the stem and the other 4 tables are like branches, if you understand what i mean. However i would like to hear more about indexes. Have i to change my queries? There are a lot on each page and it would be painful but tell me!

ultraslacker

You are not using indexes besides what are generated by primary keys.

Post your slow queries along with the EXPLAIN output.

jonsof

the slowest queries are

$result=mysql_query("SELECT *, DATE_FORMAT(date_posted, '%e-%m-%Y')AS date_posted  FROM post WHERE typeid=1 AND now() BETWEEN date_posted AND expire_date ORDER BY postid DESC");
$result_type=mysql_query("SELECT a.* FROM announcement a, post p WHERE a.postid=p.postid AND now() BETWEEN date_posted AND expire_date ORDER BY postid DESC");
....
....
....
while(($row_type=mysql_fetch_assoc($result_type))&&($row=mysql_fetch_assoc($result))) {
.....
.....
.....}

Should i use index. What about them?

superwormy

http://www.mysql.com/information/presentations/presentation-oscon2000-20000719/

http://www.mysql.com/doc/en/MySQL_Optimisation.html

Also, I'd suggest you start benchmarking how long your scripts take to run ( ie get a microtime at the beginning, and end of the script execution and print out the difference ) and see how long your scripts are actually taking to run.

For anything with a light traffic load, up to a second is fine.

mattr

The clause:
"AND now() BETWEEN date_posted AND expire_date"

Cannot use an index and is not the right form.

If you want it to use an index you will need to re-write it as such:
AND date_posted > now()
AND expire_date < now()

jonsof

i would like to use indexes but how?

jonsof

Originally posted by mattr
The clause:
"AND now() BETWEEN date_posted AND expire_date"

Cannot use an index and is not the right form.

If you want it to use an index you will need to re-write it as such:
AND date_posted > now()
AND expire_date < now()

I used your script but nothing was pulled out from the database

date_posted and date_expire are date fields both.

mattr

BETWEEN is inclusive, so try:
AND date_posted >= now()
AND expire_date =< now()

jonsof

$result=mysql_query("SELECT *, DATE_FORMAT(date_posted, '%e-%m-%Y')AS date_posted  FROM post WHERE typeid=1 AND date_posted >= now() AND expire_date =< now() ORDER BY postid DESC");

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in c:\phpdev\www\output_program\announcement\output.php on line  28

🙁 🙁

mattr

DUH!

smacks forhead with palm of hand

If you logically change the now() BETWEEN date_posted AND expire_date you should get:
WHERE date_posted <= now() <= expire_date

So converting that to SQL it should be this:
WHERE typeid = 1
AND date_posted <= now()
AND expire_date >= now()

Yes?

jonsof

Does this syntax make it faster than mine one?