Page 1 of 1

Optimize Query

Posted: Wed Apr 02, 2008 12:39 pm
by kerrycita2
Hello I'm trying to get my head around table indexes and optimizing my queries.

I have the following table:

Code: Select all

 
CREATE TABLE `tbl_company_lists` (
  `id` int(11) NOT NULL auto_increment,
  `comapny_id` int(11) default NULL,
  `dttm` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `comapny_id` (`comapny_id`),
  KEY `dttm` (`dttm`)
)
 
With data that looks like this:

Code: Select all

 
id  company_id  dttm
1   35  2008-03-29 19:24:16
2   123 2008-03-29 20:59:14
3   123 2008-03-29 20:59:16
4   123 2008-03-29 20:58:55
5   123 2008-03-29 20:58:41
6   67  2008-03-29 20:58:06
7   67  2008-03-29 20:58:36
8   123 2008-03-29 20:56:55
9   67  2008-03-29 20:56:19
10  35  2008-03-29 20:55:58
11  67  2008-03-29 20:52:49
12  67  2008-03-29 20:54:39
13  35  2008-03-29 20:52:19
14  163 2008-03-29 20:51:54
15  163 2008-03-29 20:51:55
16  35  2008-03-29 20:49:59
17  35  2008-03-29 20:50:20
18  67  2008-03-29 20:49:50
19  67  2008-03-29 20:48:40
 
I'm trying to get the last 10 records sorted by date (or id) descending.

[sql] EXPLAIN SELECT`id`,`comapny_id`,`dttm`FROM`tbl_company_lists` WHERE `comapny_id` = 2ORDER BY id DESC LIMIT 0,10  [/sql]

However, when I try and an explain I receive the following:

Code: Select all

 
id  select_type table   type    possible_keys   key key_len ref rows    extra
1   SIMPLE  tbl_company_lists   ref comapny_id  comapny_id  5   const   395 Using where; Using filesort
 
Notice how the rows is 395 and not 10 Is there anyway to optimize this better?

Thanks!

Re: Optimize Query

Posted: Wed Apr 02, 2008 4:56 pm
by flying_circus
Why do you use so many backticks (`)? According to your query, I would think it would return 0 rows because I dont see a company id of 2.

try:

Code: Select all

 
SELECT id, comapny_id, dttm 
FROM tbl_company_lists 
WHERE company_id = 2 
ORDER BY id DESC 
LIMIT 0,10 
 
You also have a typo in your WHERE clause