loading page too slow

jansky

to all guru

i have a record with only 13MB and already loading the page "slow"

and my sql statament is not that difficult

but my tables is not indexes
if im going index my table is there any changes in sql statement (that is already implemented)

will indexes will increase performance ?

big_nerd

jansky,

That is a broad question, what is the SQL statement you are using? Are you executing it only once?

Feel free to dummy the table & row names, however posting here (or in the Database forum) you will find some brilliant minds with SQL, and I know they have helped me increase the performance of my transactions greatly.

bradgrafelman

If you're positive it's the SQL queries that are slowing down your page, post some of the longer queries for us as well as your current table structure.

jansky

The Sql statement executed if someone do searches, furthermore there are some rules for the sql. this involve (Search Near City, Area of Law, Number of year experience,etc.)
This is some of the sql statement

SELECT members.*,

			mo.years as exp_year1,
			mo.lastyear as thelastyear,
			mo.fiveyear as thefiveyear,
			mon.years as experience_by_year,
			mon.fiveyear as experience_five_year,
			mon.lastyear as experience_lastyear,
			areas_of_law.id as area_law,areas_of_law.english_title as area_law_name

		FROM members

			left join city on (members.concelho = city.id)

			LEFT JOIN member_area_of_law   as  mo ON ( members.id = mo.member_id )
			LEFT JOIN member_area_of_law as mon on (members.id=mon.member_id and mon.area_of_law='116' and mon.parent_area='116')
			left join areas_of_law on(mo.area_of_law=areas_of_law.id)


			 WHERE ((mo.area_of_law in (117,118,119,126,116))) AND concelho='153' AND active=1 group by members.id   


// this statement result to 53 records

my database schema
// MEMBERS table
CREATE TABLE members (
id int(11) NOT NULL auto_increment,
name varchar(255) NOT NULL default '',
address varchar(255) NOT NULL default '',
postal_code varchar(255) NOT NULL default '',
city varchar(255) NOT NULL default '',
concelho varchar(255) NOT NULL default '',
localidade varchar(255) NOT NULL default '',
telephone varchar(255) NOT NULL default '',
email varchar(255) NOT NULL default '',
firm_name varchar(255) default NULL,
firm_size varchar(255) default NULL,
firm_website varchar(255) default NULL,
lawyer_since varchar(5) NOT NULL default '',
photo varchar(255) default NULL,
cedula varchar(255) NOT NULL default '',
member_oa varchar(255) default NULL,
professional_association varchar(255) default NULL,
spoken_language varchar(255) default NULL,
price_indication_per_hour varchar(255) NOT NULL default '',
first_consult_for_free varchar(255) NOT NULL default '',
your_profile text NOT NULL,
your_services_and_practice_areas_of_law text NOT NULL,
password varchar(255) NOT NULL default '',
payment varchar(255) NOT NULL default '',
register_email varchar(255) NOT NULL default '',
payment_name varchar(255) NOT NULL default '',
fiscal_number varchar(255) NOT NULL default '',
payment_address varchar(255) NOT NULL default '',
payment_postal_code varchar(255) NOT NULL default '',
payment_city varchar(255) NOT NULL default '',
payment_phone varchar(255) NOT NULL default '',
payment_email varchar(255) NOT NULL default '',
forms_payment varchar(255) NOT NULL default '',
active smallint(6) default '0',
date_reg date default NULL,
date_approve date default NULL,
spok_eng smallint(6) default NULL,
spok_fren smallint(6) default NULL,
spok_ger smallint(6) default NULL,
spok_span smallint(6) default NULL,
spok_ital smallint(6) default NULL,
spok_other smallint(6) default NULL,
other_language varchar(255) default NULL,
expiration_date date default NULL,
PRIMARY KEY (id)
)

//ARea of law

CREATE TABLE member_area_of_law (
id int(11) NOT NULL auto_increment,
member_id int(11) NOT NULL default '0',
area_of_law int(11) NOT NULL default '0',
years int(11) default NULL,
cases int(11) default NULL,
lastyear int(11) default NULL,
fiveyear int(11) default NULL,
parent_area int(11) default NULL,
PRIMARY KEY (id)
)

//City
CREATE TABLE city (
id int(11) NOT NULL auto_increment,
district varchar(255) default NULL,
city varchar(255) default NULL,
parent_id int(11) default NULL,
near1 int(11) default NULL,
near2 int(11) default NULL,
near3 int(11) default NULL,
near4 int(11) default NULL,
near5 int(11) default NULL,
PRIMARY KEY (id)
)

This are the 3 most tables used

rulian

do an EXPLAIN and post that results as well,

it may only return 53 rows, but you are doing redundant joins that could add up to alot of possible matches very quicky and slwo your query.
Kind of like that sat problem, if 20 people are in the room and each person shakes hands with every other person, who many handshakes were there, same principle, it will run a join against every row and a join against every row of the 2nd join, so the more rows you have the more posibilities it needs to compute

rulian

and also what is "slow" how long is this taking?