Hi, 2006/4/20, Adam Wolff <[EMAIL PROTECTED]>: > 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 ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
