Hi,
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 ?
