MySQL Forums
Forum List  »  General

Re: Jet Profiler says I'm slow....
Posted by: Hans de Bruin
Date: August 31, 2012 11:01AM

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

Options: ReplyQuote


Subject
Written By
Posted
Re: Jet Profiler says I'm slow....
August 31, 2012 11:01AM
September 01, 2012 08:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.