List:General Discussion« Previous MessageNext Message »
From:Philippe Poelvoorde Date:April 20 2006 12:32pm
Subject:Re: newbie optimization question
View as plain text  

2006/4/20, Adam Wolff <awolff@stripped>:
> Hi. I'm new to database optimization and I have a couple of questions.
> I have a table like this:
> +--------+----------------+-------------+---------+
> | id     | fullname       | email       | user_id |
> +--------+----------------+-------------+---------+
> Where fullname and email are varchar(100) and user_id is a non
> nullable foreign key.
> I have indices on every column. InnoDB engine.
> * Question 1:
> How can I optimize the case where I filter on one key but sort on another?

try a compound index ? ADD INDEX(fullname, email)

> * Question 2:
> Why does introducing an extra WHERE clause make things slower?
> If I do this:
>     SELECT * FROM contacts WHERE fullname LIKE "j%" AND user_id=1
>     ORDER BY fullname LIMIT 10;
> The results come back several orders of magnitude slower. This is
> despite the facts that:
>     A) The results are the same for this query as the one without the
> test for user_id
>     and
>     B) About 95% of the records of in the table have user_id=1

what if you drop the index on user_id and run optimize table ?
newbie optimization questionAdam Wolff20 Apr
  • Re: newbie optimization questionPhilippe Poelvoorde20 Apr
  • Re: newbie optimization questionAlexey Polyakov20 Apr
    • Re: newbie optimization questionAdam Wolff20 Apr
  • Re: newbie optimization questionAdam Wolff21 Apr