[RESOLVED] row count with slow queries

webnick

I have a table with 500K rows and 600 cols. I can run simple sort and search queries but when I count number of rows before the limit, then get the rows with limit, the script times out.

What's the best way to get a row count? Do you use mysql_free_result after each query change?

cgraz

Can you explain in more detail, and/or post your queries and table/structure?

From your post, I'm wondering if you are selecting all of the rows, then using mysql_num_rows() to count the rows? If so, you'd be better of doing a SELECT COUNT(fieldname) in mySQL.

Also, do you have your tables properly designed and indexed?

webnick

Indexes are probably part of it. What I'm trying to do first, is let someone run a search and show them the first, second, third etc part of the results. The search with a limit runs fine but trying to get a number of finds doesn't.

Right now only the first or second query run, not both.

$sql = SELECT * FROM t WHERE group= '2' AND SSN LIKE '%1212%'
$result = mysql_query($sql);
$numrows = mysql_num_rows($result);
mysql_free_result($result);

$sql .= " LIMIT $startrow, 30";
$result = mysql_query($sql);

So, maybe there are two solutions? Bump up the memory limits on the server? And add an index for the two allowed searches: group, SSN and group, LNAME?

Thanks for any advise

NogDog

For the above example, you definitely want indexes on the group and SSN columns.

To get the total row count of matches, use COUNT():

$sql = SELECT COUNT(*) FROM `t` WHERE `group`= '2' AND `SSN` LIKE '%1212%';

This will return just one result row with one column (the count) as opposed to returning every column of every matching row.

Finally, on your actual data query (with the LIMIT clause), unless you really need all 600(!) columns in each result row, instead of doing SELECT *, provide a list of only the columns you actually need. (Yes, it may be a lot of typing if you need 60 of them, but that would still mean transmitting only 60 columns of data per result row instead of 600, or roughly 10% as much (obviously varying depending on the relative sizes of the included/excluded columns).

bogu

Look at the SELECT SYNTAX, at SQL_CALC_FOUND_ROWS and FOUND_ROWS

Sxooter

600 columns? do you really need 600 cols? cause thar's gotta be a big part of the problem.

and why do you need to count that many rows? As long as you're selecting that many rows that are that big, it's gonna be slow. Now, if you do select count(*) where indexedfield='xyz' you can get good performance, but not with limit / offset.

scrupul0us

sounds like maybe first u might want to ask for someone to examine the structure of your table design

600 columns is just absolutely absurd! im sure some of it can be combined into maybe storage arrays within similar columns...

something!

i cant even fathom the maintenance or overheard on a table that large

im so intrigued for that matter that i WANT to see it

Piranha

I have only heard of one other database table that have that many columns. Or to be correct, it was set up when databases didn't have the ability to use that many columns, it had 2 tables with 254 columns and one table with around 200 columns, the columns were joined with the primary key when quering the database. What is more with that database is that there were inserted around 130 million rows each week with the predictions how much would be sold every week for the next 1.5 years in each of 200 stores and each of the around 8500 products.

scrupul0us

YIKES!

webnick

NogDog wrote:
For the above example, you definitely want indexes on the group and SSN columns.

To get the total row count of matches, use COUNT():
$sql = SELECT COUNT(*) FROM `t` WHERE `group`= '2' AND `SSN` LIKE '%1212%';
This will return just one result row with one column (the count) as opposed to returning every column of every matching row.

Finally, on your actual data query (with the LIMIT clause), unless you really need all 600(!) columns in each result row, instead of doing SELECT *, provide a list of only the columns you actually need. (Yes, it may be a lot of typing if you need 60 of them, but that would still mean transmitting only 60 columns of data per result row instead of 600, or roughly 10% as much (obviously varying depending on the relative sizes of the included/excluded columns).

It's enterprise data. I went ahead and created the indicies - they're under a MB with 460MB of data - you could see the difference in execution time. Thanks for help on this count also!