MYSQL :: How-to Calculate The Size Of 1 Million Rows?

A Million Rows
i'm setting up a squid log analyser.
i wrote a little perl script which copies the log file entrys into a mysql db (with only one table).
i get ~5 million entrys a day. so my table gets larger day by day.
all entrys older than 30 days are automatically deleted.
now i'm searching for a method to collect the data from the sql table to generate a report (perhaps a online report via php).
but it's very slow to collect that data (atm ~30 million rows).

i tried to collect the amount of connections the last 24h data with the following command:

$ToDate = time() - (24 * 60 * 60);

print " <tr>
<td align="left" class="row1"><b>Total Connections</b></td>
";
$result = SqlSelectQuery("SELECT DISTINCT id FROM logfile WHERE time > "" . $ToDate . """, __FILE__, __LINE__);
$number = SqlNumRows($result);
print " <td align="left" class="row1">  " . $number . "</td>
";

well it works, but it takes a while and as result i get "1629258".
so about 1 minute to collect that data? ~1.5 million rows?

How To Calculate The Size Of An AES_ENCRYPT() Result
what I want is to calculate the result string for an AES_ENCRYPT() function, in the mysql manula says:the result string length may be calculated as 16*(trunc(string_length/16)+1) but I dont know what value trunc has.

Could any one please explainme how to calculate the max size of an 10 chars max wide password value?

How To Calculate Data Size In MySQL Tables
In PostgreSQL you can access meta-data about table names, etc. so you could write a program that will automatically scan through all the tables in a database.

How do you do this with MySQL?

Here's the scenerio: Want to scan through all the tables in a db, finding data that matches a specifc key value (ie. a specific user), and calculate what percentage of space (and total space) is being used by those matched (that user).

Calculate Number Of Rows
From the packet data returned from a query, where in the packet data are the
bytes signifying the number of rows returned?

Size Of DB - Do Too Many Rows Cause A Problem?
What is big for a MYSQL DB. E.g. I am working on someone elses DB that
has a many-many link table that notes what emails are sent out to what
users. Therefore each time a message gets sent out from this
application, if it got sent to 500 users for example, 500 rows would be
added to this table. If the amount of users was something like 10,000
then each time they were all mailed this would be 10,000 rows added. It
is already at 250,000 and as the new maintainer of the DB should this
be someething I should worry about?

How Big ( Disk Size ) Would This Database Be: 4.2 Billion Rows
I have a database in MySQL version 5.0.19 that I'm making. Its structure is:

CREATE TABLE `big` (
`keyval` int(10) unsigned zerofill NOT NULL,
`name` varchar(20) default NULL,
`avail` enum('y','n') default NULL,
PRIMARY KEY (`keyval`),
KEY `keyval` (`keyval`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The 'keyval' will be filled with every value from 00000000 to 4294967295 ( 4.3 billion rows ), and assume worse case of 20 characters in each 'name' element and 'y' or 'n' always set in the 'avail' colmn.

Determine The Size Of A Set Of Rows Based On A Where Clause
I have tried looking for a solution to this problem but no luck. The
thing is, in my system, I have a set of users who have a 'quota'
limiting their usage. They can create n number of tables and put m
number of rows in it, but at the end, their 'usage' can not exceed,
lets say, x MB. My problem is, how to I evaluate the space being used,
given the fact that I can pull all data for a user by including a user
ID in the where clause. In other words, I need to find size of the
result set containing all user's rows.

I am using mysql jdbc driver and not sure if there
is any API that deals with the physical size of the resultset.
resultset.getFetchSize() talks in terms of rows v/s the actual space
they take.

100 Million Record
I would like to store 100 million record using MYSql. Can MySql store 100Million record?

Float A Million
I have a problem making the price: 1.200.000
Until now I was using only prices under one million so it was no problem using:
MySQL Table Syntax: `koopprijs` float(10,3) default '0.000',

615 Million Records
my record count is up to 615 million. In 3 months it will be over 1.2 billion.
Problem: My webpage takes over an hour to display records.
(Using php)
1) I know my table design is very good.
2) Using the admin tool I see that my query takes 17 seconds
Then it reports that MySql is still doing something after the query is complete.
Does it buffer the data? (lots of records)
Does it write to a temp table?
Why is it taking so long?
(My query is super fast in spite of the large number of records.)
Can anyonme provide insight on what mysql does post query?
How about tips on increasing performance for large databases?

Search Over 1 Million Records (7 Columns)
What you should do in case of database with only one table containing over 1 Million records (7 columns) with over 1 GB of size and you want to run search query on it, making it searchable for public. want to search only 2 fields given below:

SUBJECT varchar 255 FullText
BODY text FullText

What you would have done/ran the query to get the BEST possible results. MATCH AGAINST? LIKE? or both or someother option.

2-3 Million Record Table With MySQL
Does anyone have any experience working with very large tables? Say, 2-3 million records? I have the opportunity to work on a new project where at least one of the tables could grow to be that size, and I'm looking for any input on if MySQL is a good solution.

Slow Queries, 4 Million Records, Need Educated Advice!
I have created an app a few years back to store some records in a DB.
According our calculations we were never to exceed 500,000 records in
the DB. Seems we were off by a decimal point or so.

I set up a FreeBSD box with MySQL three years back and it has been
filling up. One table has over 4,000,000 records. Yes, four million.

As a web developer and not a DBA, I have struggled to upkeep the server
the best I can. As of the last one million records the server has been
struggling to keep up with multiple requests and as you can imagine the
user base is growing too.

Hardware:
Dual Xeon 3.06 Ghz
4 GB ECC RAM
800GB RAID5 SATA array

Software:
FreeBSD 5.3
Apache 2.0
PHP5
MySQL 5.0.2

Basically I have to perform a search on one of two columns in this huge
table (10 columns, 4 million rows).

The table is MyISAM with a single primary key that is used largely for
updating row data.

Most records are ten digit numbers for one column and a ten digit
varchar for the other, but sometimes either column can be a series of
characters up to 100 chars long, so each column is set for
varchar(100).

The action performed is

SELECT count(*) FROM tableName WHERE col LIKE "%123%"

then...

SELECT * FROM tableName WHERE col LIKE "%123%" LIMIT 0,25

with "%123%" being any random string typed into a search window.

Size Of Indexes Versus Data File Size
I am adding indexes to a table with about 200,000 records. Every field indexed adds about 2MB to the index file (*.MYI).

The index file is around 8MB and the data file is around 10MB.

Is there some optimum ratio I need to know about?

(I have examined the queries and am indexing field used in WHERE, ORDER BY and GROUP BY clauses.)

Calculate Age
I have the dob day, month and year of my users, in difference fields.
I want to calculate the age in mysql, upto now i've been doing it with php, but it's much easily to show only people minimum age of so old or maximum age of so old when you're calculating the age in mysql rather than php.
What's the best way to do it ?

Is There A Way To Calculate Running SUM
I have a table (T1) consisting of 2 columns, Date and Observerd as follows:

Date Observed
4/1/07 2
4/2/07 4
4/7/07 3
4/10/07 5
4/12/07 1
4/18/07 7
4/22/07 2

and we want to write a query to create a table or a view with the following data:

Date ThreeDaySum
4/1/07 9 <-- (2+4+3)
4/2/07 12 <-- (4+3+5)
4/7/07 9 <-- (3+5+1)
4/10/07 13 <-- (5+1+7)
4/12/07 10 <-- (1+7+2)

Calculate Percentage
Is there a way to calculate the percentage of a series of values in MySQL?
I need to have the total of sum in order to calculate the percentage in each row. How can I do this in the following query?

SELECT product.productId, product.name, COUNT(product.productId) AS count, SUM(invoice.value) AS sum
FROM product, invoice
WHERE product.productId=invoice.productId GROUP BY product.productId

Calculate Time
Have a table with a starttime and endtime, need a query that will tell me how much time was spent using starttime and the endtime. If the start time is 11:00 am and endtime is 1:00 pm I need the query to display that it took 2 hours.

Auto Calculate
I want to auto calculate DAYS for leave taken based on date key-in by user:

For example:

Start date: 15-04-2004 -- (Friday)
End date: 18-04-2004 -- (Monday)
Days: 3 -- (calculate automatically)

From the date given above, system will automatically:-
1) Count days as 3 -- assuming working day is 15, 16 and 18 only.
2) Sunday will not be count as working day.
Is this possible with MySQL(3.32) + PHP ?

Faster Way To Calculate
I'm looking for a faster way to calculate a 5-day moving average in a MySQL table. I have a database of stock prices for various different ticker symbols. I have a column that I use to store the 5-day moving average calculation, which is just the average stock price over the last 5 days.

What I am presently doing is querying the entire table, and sorting by ticker (ascending), then date (ascending). Then I am simply obtaining a row of data at a time and writing the calculated moving average back into the table for that particular row. Since each row has a unique row number associated with it, I can easily write a particular value to any specific row within the table.

Calculate Records
I have a database that I want to display the current records from on my webpage. Current meaning not older than 30 days. I am currently using this:
SELECT * FROM Table1 WHERE (Current= 1) ORDER BY ID DESC. Now, I half to change each record from my database manually by changing all the 1's to 0's when they become older than 30 days. I have a DATE, not a TIMESTAMP, that I could use, but I don't know how to write the SQL query that can use the DATE field and return only the records that are less than or equal to 30 days old.

Calculate Total Using MySQL Or PHP
Can someone recommend a the better of two ways of calculating a list of product totals:

By using the following SQL:

#########################################
# Get Product, Pricing and Taxes Amounts #
#########################################
SELECT
PRDS.prd_product_name,
PRCS.prc_base_price as Price,
TAXS.tax_tax_name as Tax,
(TAXS.tax_tax_amount * PRCS.prc_base_price) as Tax_AMNT
FROM
lop_lists_of_products as LOPS,
prd_products AS PRDS,
prc_prices AS PRCS,
map_prices_to_taxes AS MP2T,
tax_taxes AS TAXS
WHERE
LOPS.prd_product_id = PRDS.prd_product_id
AND
LOPS.lop_list_of_products_id = 1
AND
PRDS.prc_price_id = PRCS.prc_price_id
AND
MP2T.prc_price_id = PRDS.prc_price_id
AND
TAXS.tax_tax_id = MP2T.tax_tax_id;
I generate the following result set:

prd_product_name PriceTax Tax_AMNT
test Product 1 54.9900G.S.T3.2993999262527
test Product 1 54.9900P.S.T4.3991999016702
Test Prod 2 27.5500G.S.T1.6529999630526
Test Prod 2 27.5500P.S.T2.2039999507368
As you can see the two products each have G.S.T. and P.S.T. taxes. If I want to get a total price that includes both taxes would I be better to:Use PHP get the Price and the Tax amounts and add them together? OR;Is there a way to get this total using SQL, and if so is it better to use SQL for this type of result or even though a method may exist to calculate totals (the way in which I am asking) the processing overhead is too great and PHP doing the calculation would be a better way?While I know how to perform the calculation in PHP, I don't know where to start to take the existing result set (shown above) and then perform another calculation upon it using SQL.

Calculate Time Between Records
I have been trying to do this for days and would appreciate some help!

This database was designed by someone else and i have been lumped with writing some reports!

I have a table that stores GPS information from vehicles.
I am trying to write a report that will show a vehicles "idle time"

Below is the table Structure.

Field Type
Date date
Time time
Vehicle int(11)
Latitude char(11)
Longitude char(12)
Street char(50)
Suburb char(50)
Height int(11)
SpeedInst int(11)
SpeedMax int(11)
SpeedAv int(11)
SatCount int(11)
SatsInView int(11)
CourseInst int(11)
CourseAv int(11)

As you can imagine, it has ALOT of records

So i wrote this query to narrow down the records where the vehicle is idle...

SELECT
`vehiclehistory`.`Date`,
`vehiclehistory`.`Time`,
`vehiclehistory`.`Vehicle`,
`vehiclehistory`.`SpeedInst`,
`vehiclehistory`.`Suburb`,
`vehiclehistory`.`Street`,
`vehiclehistory`.`Latitude`,
`vehiclehistory`.`Longitude`,
`vehiclehistory`.`SpeedMax`,
`vehiclehistory`.`SpeedAv`,
`vehiclehistory`.`CourseInst`,
`vehiclehistory`.`CourseAv`
FROM
`vehiclehistory`
WHERE
`vehiclehistory`.`Vehicle` = &#55613;&#57184;'
AND
`vehiclehistory`.`Date`= &#55614;&#57159;-02-14'
AND
`vehiclehistory`.`SpeedInst` < Ƈ'
AND
`vehiclehistory`.`CourseAv` = `vehiclehistory`.`CourseInst`
GROUP BY
`vehiclehistory`.`CourseAv`
ORDER BY
`vehiclehistory`.`Time` ASC

FINALLY! What i am trying to do is calculate the time between the records that the query displays... that will give me the idle time...

Sorry for the long story but felt i should give you all the information.

Calculate The Number Of Fields With Value X
I need to retrieve from a database a number of fields where field_x = y. How can I do it?

How To Calculate With Time-fields?
i have a problem with sql-time-calculations.

"update <tablename> set calc=start-stop"

the fields "start", "stop" and "calc" are from datatype "time".

after executing the sql statement most of "calc" fields contains "00:00:00" and that's is an error.

i reied the same sql-statement with datetime-fields and got the same error.

Calculate Time MySQL
I have a DATETIME field with data ex.: 1.1.2007 12:00:00 and I want to get an exact calculation of time that elapsed from input till the time I run the query, so it would show me how many days, hours, minutes and seconds passed since the data was entered.

I was trying something like this (see below), but had trouble with midnights. If data was entered at 23:59 , the query resulted that it was 1 day old after one minute. The same trouble was with end of months and years(I think).

SELECT
CONCAT(DAYOFYEAR(NOW())-DAYOFYEAR(date1),' days ',
DATE_FORMAT(ADDTIME("2000-00-00 00:00:00",SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(date1))),
'%k hours and %i minutes')) AS time FROM time_table;

I've tried

SELECT NOW() - date1 AS passed
FROM time_table;

but I don't know how to get anything readable or user friendly from the result.

Calculate Time Between Records
This database was designed by someone else and i have been lumped with writing some reports!

I have a table that stores GPS information from vehicles.
I am trying to write a report that will show a vehicles "idle time"

Below is the table Structure. Code:

Calculate The Difference Between 2 Dates
I searched here and also the MySql mans, but couldn't find my function!

I don't wana simply subtract the year part of 2 different dates, but I need to subtract the whole part! Something like:

"select '2000-05-07' - '1994-03-05'"

And it should return "6-2-2",

Calculate Time Difference
I have a column called timeinterval which is of type datetime. I need to select the first and the second timeinterval and find the difference between them. if it is greater than 30 seconds i need to do something. how to achieve this. I tried using datediff and timediff.

eg:
select TIMEDIFF ('2006-10-31 11:50:31' , '2006-10-31 11:50:01')
eg:
select DATEDIFF ('2006-10-31 11:50:31' , '2006-10-31 11:50:01')

but it is saying you have an error in sql syntax. Iam using mysql 4.1.0 version.

one more thing. If the query works fine how can i check whether it is greater than 30 seconds. If suppose it is greater than one day it is going to return 1. How to check this condition also.

Calculate Datetime Data
I am using MySQL 4.0. I have a table: foo with Id, TimeStamp columns.
TimeStamp is in DATETIME format. I want to retrieve all Ids with (Now -
TimeStamp) > 30 days. How can I do that.

Need SQL To Calculate Difference Between Two Date/time
I need difference between two date not only in terms of day or hour or minute or second as outputted by following stmt.

SQL
SELECT
TO_DAYS(NOW()) - TO_DAYS(&#55614;&#57159;-09-16 11:45:00') AS diff_in_whole_days,
ROUND((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(&#55614;&#57159;-09-16 11:45:00')) / 86400, 1) AS diff_in_days,
ROUND((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(&#55614;&#57159;-09-16 11:45:00')) / 60 / 60, 1) AS diff_in_hours,
ROUND((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(&#55614;&#57159;-09-16 11:45:00')) / 60, 1) AS diff_in_minutes,
UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(&#55614;&#57159;-09-16 11:45:00') AS diff_in_seconds;

But I need the difference by days and the remaining hours, and remaining minutes and so on as shown below example:

Difference bet 2007-09-17 15:45:14 and 2007-09-16 11:15:00
is 1day 4hr 30 min 14 sec.

I need these all in sql statements for this requirement.

How To Calculate The Actual Value Of A Tables Row Length
Is there a way to calculate the actual value of the row length for a particular table? I need to issue the following ALTER TABLE command :

alter table CMS_BACKUP_FILES max_rows = 200000000000 avg_row_length = x ;

But I am unsure what value to specify for the parameter AVG_ROW_LENGTH.

Fiscal Year Totals - How To Calculate?
I am given the month number for the fiscal year. For exmaple, "4" indicates the fiscal year begins April 1 each year. April 2, 2005 would be Fiscal Year 2005. March 30, 2005 would be Fiscal Year 2004.

With the following table structure:
TABLE_A
id
date
amount

My current set up is like this, based on calendar year:

PHP

// get a list of years in the db
$years = $dbh->getCol("SELECT DISTINCT(YEAR(r.date))
FROM table_A r
ORDER BY YEAR(r.date) ASC");

foreach ($years as $s) {
    $yearSum = $db->getOne("SELECT SUM(r.amount)
    FROM table_A r
    WHERE YEAR(r.date) = '$s'");
    //echo something here
}

ISSUE1:
I need to calculate the total for each fiscal year. For example, April 1, 2004 - March 31, 2005. AND April 1, 2005 - March 31, 2006, and so on and so on for all years.

ISSUE2:
I need to calculate the total for each MONTH within each fiscal year. For example, during the fiscal year April 1, 2004 - March 31, 2005, what was January's total, Feb's total,... For each fiscal year.

Date Functions, How To Calculate Last 7 Days...
Hope you guys don't mind me posting questions every other day MySQL is not my strong suit..

Q: If my table uses the DATE field, how would I go about selecting all the rows where the date is within the last 7 days?

Calculate Percentage In A Single Sql Statement
i am trying to calculate percentage of student came to computer laboratory in a single sql statement but no luck.

SELECT f.facultyInitial, COUNT(*) AS TOTAL
FROM attendance att
INNER JOIN academic a
ON att.academicNo = a.academicNo
INNER JOIN program p
ON a.programId = p.programId
INNER JOIN faculty f
ON p.facultyId = f.facultyId
WHERE YEAR(att.attendanceDate) = 񟭆'
GROUP BY f.facultyInitial
ORDER BY f.facultyInitial
alright, sql statement above will produce an output like below:

facultyInitial | TOTAL
---------------------
Account | 2
Civil | 1
FITQS | 3

what i want to do is, to put a percentage at the right side of the TOTAL column.

facultyInitial | TOTAL | PERCENTAGE
-----------------------------------
Account | 2 |
Civil | 1 |
FITQS | 3 |

here is my example to produce a percentage but it does not work

SELECT f.facultyInitial, COUNT(*) AS TOTAL, ROUND(COUNT(att.attendanceId)/TEMP.TOTAL_ROWS * 100, 2))
FROM attendance att
INNER JOIN academic a
ON att.academicNo = a.academicNo
INNER JOIN program p
ON a.programId = p.programId
INNER JOIN faculty f
ON p.facultyId = f.facultyId,
(SELECT COUNT(*) AS TOTAL_ROWS
FROM attendance TEMP
WHERE YEAR(att.attendanceDate) = 񟭆')
WHERE YEAR(att.attendanceDate) = 񟭆'
GROUP BY f.facultyInitial
ORDER BY f.facultyInitial

How To Calculate Weeks Elapsed In MySQL V4.0.25
Can anyone provide an example of the most straight-forward way to determine if a datetime field in a table is from the future, the current week, last week, or the 3 months previous in MySQL v4.0.25? The TIMESTAMPDIFF function looks promising but it's only available in v5.0.

I'd like to pass something to the WHERE clause of the query.

Construct A Query To Calculate The Most Selling Product
Could anyone advise how to construct a query to calculate the most selling product from each supplier in a given month.

suppliers, order.orderdate and products are in different tables.

How Do I Calculate A New Value From 2 Columns Whilst Looping A Table
I have a single table and i would like to calculate two columns (imp and clicks) while looping through the results.

this is what the table would look like...

campaign | imp    | clicks
A        | 200000 | 100
B        | 100000 | 40
C        | 50000  | 10
D        | 300000 | 200

and here's what i would like to achieve with a mysql query and php. I would like to create a new column CTR% and calculate the CTR for each row by looping through the table...

campaign | imp    | clicks | CTR %
A        | 200000 | 100    | 0.05
B        | 100000 | 40     | 0.04
C        | 50000  | 10     | 0.02
D        | 300000 | 200    | 0.06

i've tried a few things that i've read up on by googling but none really worked (based on using a while loop). now i would like to wipe the slate clean and get your suggestions on what to do??

Calculate A Moving Average And Updating A Table With It
I'm learning SQL and now I'm trying to update a table (koersen) with stockdata (date, ticker, open, close, volume, MA10vol).
The column MA10vol is empty and should contain the avg volume from the last ten records with the same ticker.
What I tried sofar:

SELECT @hdat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,1);
SELECT @ldat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,10);
SELECT tickerVL, AVG(totvolume) AS MA10
FROM(
    SELECT  SUM(k.volume) AS totvolume
        FROM koersen k WHERE k.datum BETWEEN @hdat AND @ldat
        GROUP BY k.tickerVL
    ) AS tijdelijk

The update part comes later. I'm now trying to make a subquery to get me the total volume of the last ten days. But I get a message that the subquery returns more then 1 row (more tickers I think).

But I want a query that updates every MA10vol for every ticker. I know I'm doiing something wrong but what?

Auto Calculate Total DAYS For Leave, Help Please!
I want to auto calculate DAYS for leave taken based on date key-in by user:

For example:

Start date: 15-04-2004 -- (Friday)
End date: 18-04-2004 -- (Monday)
Days: 3 -- (calculate automatically)

From the date given above, system will automatically:-
1) Count days as 3 -- assuming working day is 15, 16 and 18 only.
2) Sunday will not be count as working day.

Is this possible with MySQL(3.32) + PHP?

Calculate Days Between Current Date And Date
I need to calculate the days between the current day and the date stored in a date column. I saw the datediff() command, but that only works with mysql 4.1.1. I am running 3.23.58. How can I do this?

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Size
What is the Size limit to MySQL can it have 200 gigs of data?

DB Size
i have a problem with my db Import maxsize (2.048KB) and i want to make it 12.000KB. How can i do it.

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

How-to Calculate The Size Of 1 Million Rows?

Anyone have any tips for calculating the size of a certain number of rows in a MySQL DB? If phpMyAdmin gives my row size as 19 bytes will 1 million rows be 1 million times that size?