Database programming, help.

[katten]

I was lastnight while working on my own open-source forum for fun, i looked at the mysql runtime varibles and i noticed a few things that concerned me...

here are a few things i would like to have tips on how to fix.

Innodb_buffer_pool_reads 25 The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.



Handler_read_rnd 488 The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.


i have got one single join in my code witch is

$posts = $db->query('SELECT `posts`.`id` AS `postid`,`message` AS `post`,`deleted` AS `deleted`,`users`.`id` AS `uid`,`users`.`signature` AS `signature`,`users`.`posts` AS `posts`,`users`.`access` AS `access`,`users`.`name` AS `name`,`users`.`website` AS `website`,`lAStvisit` AS `online` FROM `posts`,`users` WHERE (`posts`.`threadid` = \''. (int) $id .'\' AND `users`.`id` = `posts`.`uid` AND `posts`.`deleted` = \''.$deleted.'\') ORDER BY `posts`.`time` DESC LIMIT 10')->fetchAll();



Handler_read_rnd_next 48 k The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.


I tried to fix this myself last night but i don't know how to.

Created_tmp_disk_tables 311 The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.



Key_reads 17 The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.




Opened_tables 190 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.



Note, i'm using a pdo extension for the connection to MYSQL, and that i'm runing my stuff on my own Ubuntu-edgy server.

also i'm only 15 years old, and i have never took a single lesson in any programing language also here is link it you want to see it. http://drakaro.ath.cx/engine/

[katten]

<bumb>

[MrCoder]

So what needs fixing?

I read your post, but I think you forgot to tell us what is broke?

[katten]

I want to know how i can lower the figures in the parts i gave you. ?

[MrCoder]

I belive this is more of a database issue then PHP.

Do you have a database layout for us to look at, or are we ment to guess what the field types are?

[katten]

This is the database structure if thats what you mean.

-- phpMyAdmin SQL Dump
-- version 2.8.2-Debian-0.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 31, 2007 at 03:28 PM
-- Server version: 5.0.24
-- PHP Version: 5.1.6
--
-- Database: `engine`
--

-- --------------------------------------------------------

--
-- Table structure for table `boards`
--

CREATE TABLE `boards` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
`desc` varchar(70) NOT NULL,
`subforum` tinyint(1) NOT NULL,
`locked` enum('y','n') NOT NULL default 'n',
`access` tinyint(1) NOT NULL,
`lastpost_time` bigint(20) NOT NULL,
`lastpost_user` int(11) NOT NULL,
`lastpost_thread` int(11) NOT NULL,
`threads` int(11) NOT NULL,
`posts` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Table structure for table `news`
--

CREATE TABLE `news` (
`id` int(11) NOT NULL auto_increment,
`threadid` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`message` text NOT NULL,
`date` bigint(20) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

-- --------------------------------------------------------

--
-- Table structure for table `posts`
--

CREATE TABLE `posts` (
`id` bigint(20) NOT NULL auto_increment,
`threadid` int(11) NOT NULL,
`uid` int(11) NOT NULL,
`time` bigint(13) NOT NULL,
`message` text NOT NULL,
`deleted` enum('y','n') NOT NULL default 'n',
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

-- --------------------------------------------------------

--
-- Table structure for table `private_messages`
--

CREATE TABLE `private_messages` (
`id` int(11) NOT NULL auto_increment,
`from` int(11) NOT NULL,
`to` int(11) NOT NULL,
`message` text NOT NULL,
`date` bigint(20) NOT NULL,
`viewed` enum('y','n') NOT NULL default 'n',
`section` tinyint(1) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `sys_accesslist`
--

CREATE TABLE `sys_accesslist` (
`module` varchar(20) NOT NULL,
`command` varchar(20) NOT NULL,
`execute` tinyint(1) NOT NULL,
`logged` tinyint(1) NOT NULL,
`access` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `sys_errors`
--

CREATE TABLE `sys_errors` (
`id` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`message` varchar(255) NOT NULL,
`level` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `threads`
--

CREATE TABLE `threads` (
`id` int(11) NOT NULL auto_increment,
`boardid` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`user` int(11) NOT NULL,
`made` bigint(20) NOT NULL,
`lastpost` int(11) NOT NULL,
`lastpost_time` bigint(20) NOT NULL,
`sticky` enum('y','n') NOT NULL default 'n',
`poll` enum('y','n') NOT NULL default 'n',
`locked` enum('y','n') NOT NULL default 'n',
`deleted` enum('y','n') NOT NULL default 'n',
`posts` tinyint(5) NOT NULL,
`views` int(11) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(25) NOT NULL,
`password` varchar(50) NOT NULL,
`realname` varchar(50) NOT NULL,
`access` tinyint(4) NOT NULL,
`lastvisit` bigint(20) NOT NULL,
`lastactive` bigint(20) NOT NULL,
`born` bigint(20) NOT NULL,
`signature` varchar(255) NOT NULL,
`website` varchar(150) NOT NULL,
`banned` tinyint(4) NOT NULL,
`email` varchar(50) NOT NULL,
`posts` int(11) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;