Peter J. Boettcher — Thu, 27 Jun 2002 12:33:16 +0000

This would be interesting to find out. Using COUNT would probably be faster up to a certain point (depending on number of records), then the SHOW TABLE.. command would probably be faster.

zollet — Thu, 27 Jun 2002 06:35:59 +0000

Quote: Originally posted by korndragon
but what would take more resources? using 10 queryies to get specific information, or use i sql querie that has to return every bit on information about the 10 tables?

I think the SHOW TABLE STATUS FROM databasename is pretty safe to use. You could test them though! Make 2 scripts, one with the SHOW STASTUS and one with 10 queries or whatever and then use microtime() to get the exact time before and after the queries. Then subtract the time before from the time after for each script and you'll be left with the processing time for each script. Finally post the results here so we can see them too.

korndragon — Thu, 27 Jun 2002 05:47:54 +0000

let me just ask all you sql masters (lol), how would YOU get this information? Think about if you were trying to make your scripts efficient.... how would you work this problem?

korndragon — Thu, 27 Jun 2002 05:46:13 +0000

Quote: Originally posted by Peter J. Boettcher
SQL only allows for one top level COUNT statement. If you want more than one then you have to use sub-queries, for example:

select
CountTutorials=(select count(*) from tutorials),
CountResources=(select count(*) from resources)

And so on, you can add WHERE clauses to your sub-queries.

damnit, why cant mysql be that simple? I saw that they are implementing sub-queries in 4.1

korndragon — Thu, 27 Jun 2002 05:44:31 +0000

but what would take more resources? using 10 queryies to get specific information, or use i sql querie that has to return every bit on information about the 10 tables?

zollet — Wed, 26 Jun 2002 17:57:43 +0000

Each table's information is return in a row with the following columns:

Name
Type
Row_format
Rows
Avg_row_length
Data_length
Max_data_length
Index_length
Data_free
Auto_increment
Create_time
Update_time
Check_time
Create_options
Comment

Peter J. Boettcher — Wed, 26 Jun 2002 17:26:59 +0000

I don't have a lot of experience with mySQL, but I'm guessing that commands returns other info too. Does it return the values as separate fields/rows or one large string that has to be parsed?

zollet — Wed, 26 Jun 2002 16:19:48 +0000

Interesting. Have you guys tried the "SHOW TABLE STATUS FROM databasename"? It is very useful for getting rowcount and such from all tables in a database.

Mark Hensler — Wed, 26 Jun 2002 16:03:55 +0000

Quote: Quoted from mySQL docs: 1.7.4.1 SubSELECTs
MySQL Server currently only supports nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT .... You can, however, use the function IN() in other contexts. Subselects are currently being implemented in the 4.1 development tree.

Peter J. Boettcher — Wed, 26 Jun 2002 14:18:05 +0000

Maybe it doesn't, I was just refering to the Transact-SQL standard. OOps...