PDA

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