number of records = 1 extra query?

jasondavis

This is basicly the way every page of my site works, it gets the data it needs and also queries again to get total records to show a total count to user and to make proper paging

On some pages this take a really long time because it is doing the quiery 2 times, is there another way to do this?

I have see the one function that returns the number of records but since I limit to 20 per page it would just return the number 20

//get records
$sql ='select userid,submit_date from friend_friend where friendid='. $_SESSION['auto_id']. ' and status="0" order by autoid DESC limit ' .$start. ', ' .$pagesize;
$result= executequery($sql);

//gets record count for my paging functions
$sql_count = 'select count(userid) from friend_friend where friendid='. $_SESSION['auto_id']. ' and status="0"'
$reccnt = getsingleresult($sql_count);

Piranha

Do you want to know how many rows are returned? Then use count(*) that often is quicker than counting a specific column.

Also make sure that you have the indexes correctly set to minimize the time.

jasondavis

Hi, I have indexes it's just a large table around 300,000 records and growing daily by around 1,000+

I will change back to count(*) but this doesn't help much, any other ideas?

Piranha

That you have indexes doesn't mean that you have the indexes set correctly. You should not have indexes that you don't need. And make sure that the indexes that you do need is correctly set, otherwise it will take longer time to handle queries.

By the way, it is not because the table is to big. 300.000 records is still a pretty small database. It might be that you should use a dedicated server for the database, but it is no problem for any modern database to handle millions of rows.

Are you sure that it is the queries that take time, or could it be something else? Use microtime to make sure.

jasondavis

Ok I just checked it is actually

almost 500,000 rows in this table
It is on a dedicated dual xeon server just for the database

here is the table

CREATE TABLE `friend_friend` (
  `autoid` int(11) NOT NULL auto_increment,
  `userid` int(10) default NULL,
  `friendid` int(10) default NULL,
  `status` enum('1','0','3') NOT NULL default '0',
  `submit_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `alert_message` enum('yes','no') NOT NULL default 'yes',
  PRIMARY KEY  (`autoid`),
  KEY `userid` (`userid`),
  KEY `friendid` (`friendid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=637223 ;

It seems to be doing the query 2 times is the lag on this particular page

Microtime takes about
0.2529 to load with both queries

when I do away with of of them it is around
0.1435

Piranha

To see if it is the query use microtime just before the query and just after it, otherwise there is other things that might take time. It is often like that.

I see that you still haven't read up on indexes. Do that, I don't see any reason to continue this if you don't want to put in a little effort to make your code work.

jasondavis

Piranha wrote:
To see if it is the query use microtime just before the query and just after it, otherwise there is other things that might take time. It is often like that.

I see that you still haven't read up on indexes. Do that, I don't see any reason to continue this if you don't want to put in a little effort to make your code work.

Sorry i'm deffinately trying to improve it I thought my indexes were good from what I have read about them, do you see an issue with them?


PRIMARY KEY  (`autoid`),
KEY `userid` (`userid`),
KEY `friendid` (`friendid`

Piranha

Yes.

This is my last post in this thread.

jasondavis

If anyone is willing to shed some light here, heres is what I have found about Indexes, that you generally added to to columns which are most frequently in the WHERE section of your queries and have a higher cardinality

So here are my indexes on my table

As far as I know
PRIMARY KEY (autoid), is required because it is an auto_increment, i could be wrong?

KEY userid (userid),

and

KEY friendid (friendid

are the 2 most often searched for columns in the table so I have put an index on them, I just don't see what I have done wrong and reading about indexes results in basicly the same thing I have read about them everytime I read about them so I just don't understand it right

jasondavis

Here is my new code which eliminates the second query to get rowcount but in the php manual I read this post about how it is bad for high traffic site like mine? HERE
can anyone confirm this issue still exist?

$sql ='select SQL_CALC_FOUND_ROWS userid,submit_date from friend_friend where friendid='. $_SESSION['auto_id']. ' and status="0" order by autoid DESC limit ' .$start. ', ' .$pagesize;
$result= executequery($sql); 
$result = mysql_query("SELECT FOUND_ROWS()");
$total = mysql_fetch_row($result);

jasondavis

anyone? I cant find anything wrong with my indexes? When I use EXPLAIN for any query that uses this table it list them

troybtj

In addition to keys, tables can have indexes.

Look up "CREATE INDEX"

Sxooter

A "key" in a create table IS an index. It's just an alias for index.

The problem here is that even if you're hitting an index, your database is still having to hit the index and count the number of matching rows, and it's not free, just cheaper than hitting the whole table.

You need to either rethink your requirements and design or live with the cost of the count(*).

If you need the exact count then you'll have to use the count(*) syntax and live with it. You'll note that most web apps that report on the number of matching pages report "about xxx rows" because it's cheaper to hit a cached value that's close to the right number than it is to hit the exact one each time.

If you're only hitting 10 or 20 rows in the index out of 500,000 at a time, then an approximation is no great win. If you're hitting 100,000 at a time, then it is.

rulian

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

troybtj

rulian wrote:
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

VERY Handy! Couldn't find a measurable difference in query time when I added it to test (only a 20k row table tho, returned 513 results).

--ETA: At the top of the page linked above there is a BENCHMARK(n, func) function where you can run your query n times, restructure it and see which way gives the best performance.

troybtj

jasondavis wrote:
Here is my new code which eliminates the second query to get rowcount but in the php manual I read this post about how it is bad for high traffic site like mine? HERE
can anyone confirm this issue still exist?
$sql ='select SQL_CALC_FOUND_ROWS userid,submit_date from friend_friend where friendid='. $_SESSION['auto_id']. ' and status="0" order by autoid DESC limit ' .$start. ', ' .$pagesize;
$result= executequery($sql); 
$result = mysql_query("SELECT FOUND_ROWS()");
$total = mysql_fetch_row($result);

If you did your query as a transaction, it should show the correct count. Appending ";SELECT FOUND_ROWS();" to the initial query might reduce the chance of a race condition as well.

rulian

yeah, that feature saved my ass on a few occasions, luckly, i havent needed it in very complex situations yet so I'm not sure how it would hold up

ahundiak

jasondavis wrote:
anyone? I cant find anything wrong with my indexes? When I use EXPLAIN for any query that uses this table it list them

Since you are searching on both friendid and status you could try :
CREATE INDEX whatever ON friend_friend(friendid,status);

Kind of doubt it will make much difference unless you have a lot of records with multiple status codes for a given friendid.

How many records are typically returned?