Development How does PHPMyAdmin's "Total Rows" work?

I'm trying to work out how PHPMyAdmin works out the total number of records returned by a query, so when you type a query into the MySQL front end it limits the result set to 30 but displays the total number of records that the query returns.

I've tried to use SQL_CALC_FOUND_ROWS and FOUND_ROWS() But this doesn't work on a LIKE clause.

Any Ideas?

 

knowing PMA it probably runs two queries (one with a limit and one without). Or it runs without a limit but then only displays the rows you've asked for. It wouldn't be that hard to find out...

 

I found the solution now, it actually does work, but when running the queries through PMA it must run an extra query on the whole table. As it returns the entire table size.

But running it via the CLI or my own PHP script it runs fine.

Weird.

I originally thought it was two queries aswell, but when looking at the time taken it wouldn't be feasible.

 

Ah yeah, I guess. I never run particularly heavy queries so I'd never really be able to use time as a factor.

 

Heh, this stuff has to be done on tables > 1,000,000. With multiple joins and so on.

Been playing with views and it really speeds the whole process up, gotta love the "pseudo-anonomised" process I invented!

 

Actually I think i ran the longest (time wise) query i've ever done the other day - comparing one of the forum tables with itself - 25m rows in it. 1 minute something

 

I can beat that, I've managed to make the MySQL process jump to 299% CPU usage

(this is turning into a penis thread again isn't it)?

 

I win with a delete query that took 7 minutes.

 

That's cheating, you shouldn't delete that many records at once unless your silly

I've got a process that takes about 1 day to process 8million records, and seperate them across 12 tables. Total size: 100mil recs

 

Code:

show table status like 'foo';

This returns a 'Rows' column. With MyISAM it's 100% accurate, InnoDB just guesses. This is why MyISAM is faster than InnoDB when doing a 'SELECT COUNT(*)' query without a where clause.


A couple of our bandwidth maintenance scripts at work take 30-45 mins (depending on the time of day) to execute the initial 'get dataset to process' query.