...

View Full Version : why is this query so slow?



student
03-26-2007, 04:31 PM
Hello,
I have been trying to optimze my mysql database and set up 'index' on two columns. But still I am finding many slow queries in the log.
for example,

# Mon Mar 26 03:26:59 2007
# Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 0
use my_data;
SELECT * FROM table WHERE (site = 'myourinbox') LIMIT 1

# Mon Mar 26 03:36:25 2007
# Query_time: 8 Lock_time: 0 Rows_sent: 10 Rows_examined: 1352
use my_data;
SELECT DISTINCT site FROM table WHERE category='business' ORDER BY popularity LIMIT 270, 10

category is index.
site is Unique key and also index.

1) how to optimize these queries?

2) can I have both Index and Unique on the same column?

Thank you

shyam
03-26-2007, 07:42 PM
without the table structure...i can only guess that ur table design is not normalized as u need distinct

student
03-27-2007, 06:46 AM
without the table structure...i can only guess that ur table design is not normalized as u need distinct

$query = "CREATE TABLE table (
id int(11) NOT NULL auto_increment,
site varchar(12) NOT NULL default '',
title varchar(200) default NULL,
description text,
keywords text,
category varchar(100) default NULL,
PRIMARY KEY (id),
KEY category (category)
)";

after this I have added index on 'site' using wizmysqladmin script.

what is wrong with this?
and please inform me how to optimize queries.
Thank you



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum