Tnx for the fast response, here are the requested things :)
EXPLAIN of the queries
#1
1 SIMPLE i fulltext simple_search simple_search 0 1 Using where; Using filesort
#2
1 SIMPLE i fulltext PRIMARY,simple_search simple_search 0 1 Using where; Using temporary; Using filesort
1 SIMPLE icpx ref FK_tbl_items_categories,item_id item_id 4 photoshot_v2.i.item_id 1 Using where; Distinct
#3
1 SIMPLE icpx ref FK_tbl_items_categories,item_id FK_tbl_items_categories 4 const 569026 Using where; Using temporary; Using filesort
1 SIMPLE i eq_ref PRIMARY PRIMARY 4 photoshot_v2.icpx.item_id 1 Using where
#4
1 SIMPLE i fulltext PRIMARY,simple_search simple_search 0 1 Using where; Using temporary; Using filesort
1 SIMPLE icpx ref FK_tbl_items_categories,item_id item_id 4 photoshot_v2.i.item_id 1 Using where; Distinct
#5
1 SIMPLE icpx range FK_tbl_items_categories,item_id FK_tbl_items_categories 4 \N 863070 Using where; Using temporary; Using filesort
1 SIMPLE i eq_ref PRIMARY PRIMARY 4 photoshot_v2.icpx.item_id 1 Using where
SHOW CREATE TABLE
*************************** 1. row ***************************
Table: tbl_items
Create Table: CREATE TABLE `tbl_items` (
`item_id` int(11) unsigned NOT NULL default '0',
`set_id` int(11) NOT NULL default '0',
`product_id` int(2) NOT NULL default '0',
`product_id2` int(2) default NULL,
`type_id` enum('1','2','3') NOT NULL default '1',
`file_name` varchar(255) NOT NULL default '',
`object_name` varchar(255) NOT NULL default '',
`thumbnail` varchar(255) NOT NULL default '',
`middlesize` varchar(255) NOT NULL default '',
`dimension_width` int(5) NOT NULL default '0',
`dimension_height` int(5) NOT NULL default '0',
`orientation` enum('v','h','s') default 'h',
`caption` text,
`site_caption` text,
`headline` varchar(255) default NULL,
`special_instructions` text,
`credit` varchar(255) default NULL,
`city` varchar(255) default NULL,
`primary_location` varchar(255) default NULL,
`urgency` varchar(255) default NULL,
`copyright_notice` varchar(255) default NULL,
`photography` enum('0','1') default '0',
`illustration` enum('0','1') default '0',
`color` enum('0','1') default '0',
`black_white` enum('0','1') default '0',
`model_release` enum('0','1') default '0',
`keywords` text,
`source` varchar(50) NOT NULL default '',
`by_line` varchar(255) default NULL,
`publication_date` date NOT NULL default '0000-00-00',
`date_created` date NOT NULL default '0000-00-00',
`location` varchar(255) default NULL,
`folder_path` varchar(255) NOT NULL default '',
`base_dir_id` int(2) NOT NULL default '1',
`original_supplier` varchar(255) default NULL,
`view_in_cd` enum('0','1') default '1',
`rights` enum('0','1') default '0',
PRIMARY KEY (`item_id`),
KEY `type_id` (`type_id`,`rights`),
KEY `source` (`source`),
KEY `date_created` (`date_created`),
KEY `set_id` (`imageset_id`,`date_created`),
FULLTEXT KEY `simple_search` (`caption`,`keywords`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
*************************** 1. row ***************************
Table: tbl_items_categories
Create Table: CREATE TABLE `tbl_items_categories` (
`id` int(11) unsigned NOT NULL auto_increment,
`item_id` int(11) unsigned NOT NULL default '0',
`category_id` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`id`,`item_id`,`category_id`),
KEY `FK_items_categories_idx` (`category_id`),
KEY `item_id` (`item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=8263919 DEFAULT CHARSET=utf8
SHOW TABLE STATUS LIKE
*************************** 1. row ***************************
Name: tbl_items
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 2594552
Avg_row_length: 1193
Data_length: 3096117312
Max_data_length: 4294967295
Index_length: 1105321984
Data_free: 0
Auto_increment: NULL
Create_time: 2009-04-10 10:25:09
Update_time: 2009-05-05 06:34:43
Check_time: 2009-04-10 11:15:43
Collation: latin1_swedish_ci
Checksum: 3490454518
Create_options: checksum=1 delay_key_write=1 row_format=DYNAMIC
Comment:
*************************** 1. row ***************************
Name: tbl_items_categories
Engine: MyISAM
Version: 9
Row_format: Fixed
Rows: 5729131
Avg_row_length: 13
Data_length: 74478703
Max_data_length: 55834574847
Index_length: 213514240
Data_free: 0
Auto_increment: 8263919
Create_time: 2009-04-10 10:25:09
Update_time: 2009-05-04 13:53:08
Check_time: 2009-04-10 12:59:53
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
The tables are MyIsam
I shall check what is HyperThreading about and trun it of!
Yes there are COUNT queries too before the select queries,
usually the total count is from 10 000 to 500 000, it may be 1 000 000 if the search is more global. This is the count, and the reslut sets are with limit 24(or 60 or 100)
[comment]
Stuff like this is killing performance:
AND icpx.category_id = 11
ORDER BY i.date_created DESC
LIMIT 0, 24;
That is, WHERE is selecting on one table while ORDER BY is hitting the other table. It cannot make good use of indexes in both tables, so it hauls around most of the data from one table before knowing what to do with it for the other table.
[/comment]
The select things are btom tbl_items i , the ordering is i.date_cated it's form the same table, there is only icpx.category_id that must filter the items.
Do you think that
adding the column date_created in tbl_items_categories and index(item_id, category_id, date_created) for PK will be good, and sort by tbl_items_categories.date_created instead tbl_items.date_created?
Also we are having big inserts 50 records in tbl_items and more in tbl_items_categories and of course another tables, usually 15000 - 40 000 inserts at day-night. This inserts a bulk and they are synch by the web application i.e. they are in queue and are not simultaneously.