Re: Jet Profiler says I'm slow....
Hi Rick (and Peter),
Yes, it is probably 'pagination'. In order for all the items to be findable by spiders I provided a link 'older articles' on every page that jumps back 20 articles. Indeed, no person would go back 32000 items, but Google spider does, and I am happy with that. I don't want to have older articles (say one year old) to disappear from the Google index. And since they are dynamic as far as 'related articles' are concerned I don't mind searchengines re-indexing those older pages.
However if it bogs down my database then I might have to change my mind. Don't know. This query is a very basic one I think, so I feel it should be possible to get it to work properly ?
Anyway, below some data as requested:
Server version: 5.5.19-cll MySQL Community Server (GPL) by Atomicorp
CREATE TABLE `items` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`BRON` varchar(15) NOT NULL DEFAULT 'onbekend',
`DATUM` datetime NOT NULL,
`CAT` varchar(45) NOT NULL,
`TITEL` varchar(100) NOT NULL,
`BODY` text,
`IMG` varchar(150) DEFAULT NULL,
`IMGX` smallint(6) DEFAULT NULL,
`IMGY` smallint(6) DEFAULT NULL,
`LINK` varchar(200) DEFAULT NULL,
`VIDEO` tinyint(1) NOT NULL DEFAULT '-1',
`CODE` varchar(50) DEFAULT NULL,
`VIEWS` int(11) NOT NULL DEFAULT '0',
`CLICKS` int(11) NOT NULL DEFAULT '0',
`CACHED` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `LINK` (`LINK`),
KEY `CODE` (`CODE`),
KEY `DATUM` (`DATUM`),
KEY `VIEWS` (`VIEWS`,`CLICKS`),
KEY `BRON` (`BRON`),
KEY `CAT` (`CAT`),
FULLTEXT KEY `BEIDE` (`TITEL`,`BODY`)
) ENGINE=MyISAM AUTO_INCREMENT=402872 DEFAULT CHARSET=utf8
explain extended SELECT * FROM items WHERE CAT='sport' ORDER BY DATUM DESC LIMIT 31900,20
-> ;
+----+-------------+--------------+------+---------------+------+---------+-------+-------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------+---------------+------+---------+-------+-------+----------+-----------------------------+
| 1 | SIMPLE | items | ref | CAT | CAT | 137 | const | 57375 | 100.00 | Using where; Using filesort |
+----+-------------+--------------+------+---------------+------+---------+-------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)
Show table status like 'items';
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| items | MyISAM | 10 | Dynamic | 400063 | 525 | 210158044 | 281474976710655 | 200520704 | 0 | 402872 | 2012-08-13 12:03:00 | 2012-08-31 18:45:06 | 2012-08-13 12:04:48 | utf8_general_ci | NULL | | |
+--------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
show variables like '%buff%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 402653184 |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 1048576 |
| read_rnd_buffer_size | 8388608 |
| sort_buffer_size | 2097152 |
| sql_buffer_result | OFF |
+------------------------------+-----------+
14 rows in set (0.02 sec)
Thanks a bunch guys !
Hans