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?
This is fast:
SELECT * FROM contacts WHERE fullname LIKE "j%" ORDER BY fullname LIMIT 10;
But this is slow:
SELECT * FROM contacts WHERE fullname LIKE "j%" ORDER BY email LIMIT 10;
EXPLAIN tells me that the optimizer is using filesort for the second
but not the first (which makes sense.)
* 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
Any insight will be greatly appreciated.
Thanks,
Adam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
