MYSQL :: A Million Rows

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.

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??

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.

Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A..

Example:

B.part = "abcdefg" matches A.part ="cde"

I cannot put literal in query.. it must be from column data..

It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.

Converting From Many Columns/few Rows To Few Columns/many Rows
So I have a table that looks like:

Code:

char_id1234(...up to 30)
582NULL416
25739NULL12
391NULLNULLNULL

Each char_id is the primary key in another table, each numerical column value is the primary key (skill_id) in a third table, and each data value is the primay key (rank_id) in a fourth table.

I am attempting to join all four tables into a single query, but it seems impossible without converting the table to something like this:

Code:

char_idskil_idrank_id
5812
5834
58416
2517
25239
25412

Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with.

My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear.

What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear.

My Table...

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
4..............QD...................4......................2007 (current month and year)
5..............QE...................5......................2007
6..............QF...................6......................2007

I can simply get the question for the current month...

SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007;
but if I try to get all others using

SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007
I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is

id.............Question...........runMonth...........runYear
1..............QA...................1......................2007
2..............QB...................2......................2007
3..............QC...................3......................2007
*********************(remove runMonth4 and runYear 2007)
5..............QE...................5......................2007
6..............QF...................6......................2007

What am I missing?

How Many Rows?
I've a query as below, where i need to get the number of rows it produces that is the count of number of rows. Plz help .

Select * from audio left join video on (audio_sid=video_sid and audio_prgm_num=video_prgm_num) left join
pcr_info p on (audio_sid=pcr_sid and audio_prgm_num=pcr_prgm_num) right join stream s on (stream_id=audio_sid) where audio_type =
'MPEGLayer1' and isnull(video_type) group by stream_name order by stream_name

Sum Rows But The Max
I'm trying to create a query that calculates the sum of a bunch of rows then return the max (or the highest) value of those sums. So for example I have a table called imgarchives with a field called LinkBacks. I want to do SELECT SUM(LinkBacks) FROM imgarchives ORDER BY LinkBacks DESC LIMIT 0,1

The trouble with that is that I'm not looking for a sum of ALL the columns, I want to create a sum for all the archives based on BlogID. I was thinking GROUP BY might be necessary but I'm not sure how to use that with SUM(). My thoughts are:

SELECT SUM(LinkBacks) FROM imgarchives GROUP BY BlogID ORDER BY LinkBacks DESC LIMIT 0,1

That doesn't seem to work. -_- ack.

Add Of Rows
i have a table with data (time[int] und output[int]). I need a column in that the difference between the previous measurement and the current measurement is inside.

Rows Between 10 And 20
I want to select all rows between 10 and 20 from a view how can I do this?

Top N Rows
is there a way in mysql to find out the top n rows?

for example, employee names of top 3 salary holders...

First 'N' Rows Of Each
For example: Table (type, serial#), with data

('X', 1000)
('X', 1001)
('X', 1003)
('X', 1010)
('Y', 1002)
('Y', 1005)
('Y', 1011)
('Z', 1015)

How would I retrieve at most 2 rows for each type?

('X', 1000)
('X', 1001)
('Y', 1002)
('Y', 1011)
('Z', 1015)

Getting All Rows
I've two tables audio and video and need to get all the rows from two tables by joining. How do i do that.I need all the columns from both the tables. audio_sid and video_sid from both tables are same along with audio_prog_num and video_prgm_num from audio and video tables respectively.

Rows
does there exist a mysql query option that would only find rows, whert the first letter of a given varchar-column is caps?

or in other words to give a php-pendant, a mysql-query that would do about that righti within the query:
PHP Code:
if (preg_match ("/[A-Z]/", substr ($query,0,1))) { echo $given_back_row; }

Certain Rows
I am trying to extract certain rows from a database table that contains the following fields:
bookingid, bookingdate, bookingestateid, bookincustomerid, bookingconfirmed.
My SQL is:

SELECT * FROM booking WHERE bookingDate > '2005-10-16' AND bookingConfirmed !=0

I want to count the number of rows from the bookingdate (which is dynamic) until the next row where bookingconfirmed is anything other than 0. I can't work out how to do it.

50 Rows
I have a view that references another view that references some tables and views.
When I perform SELECT * FROM myView I get all rows as expected. When I perform SELECT myColumn FROM myView I only get 50 rows.
Is there any reason for this behaviour

How Many Rows
I've a query as below, where i need to get the number of rows it produces that is the count of number of rows. Plz help .

Code:

Select * from audio left join video on (audio_sid=video_sid and audio_prgm_num=video_prgm_num) left join
pcr_info p on (audio_sid=pcr_sid and audio_prgm_num=pcr_prgm_num) right join stream s on (stream_id=audio_sid) where audio_type =
'MPEGLayer1' and isnull(video_type) group by stream_name order by stream_name

Get Best Fit Of Rows
I was wondering if its possible in Mysql to pull out the rows in a table that values add up to a certain number - but making sure the best fit is achieved. For example, the table has 2 columns:
record and cost (cost being a decimal field -ignore the £ sign)
rec1 £0.50
rec2 £1.50
rec3 £0.25
rec4 £1.00

Lets say I have a cost of £5.00 I want to get the records that fits into £5.00 (having the least amount of records). So the best fit would be:
rec2, rec2, rec2, rec1

Is there a way of Mysql returning these 4 rows? Or at least just the 2 rows rec2, rec1?

100 Rows In A Db Better Than 1
you have a db for auto repair shops. One table is called services. It has two columns, one for ID and one for the service title. Now let us say you have 100 services.
Now let us say you have a table called shops. That table has typical info about a shop (address, name, tel, etc) and also an id field.
As I have been taught if I want to specify which services a shop offers I SHOULD NOT add a column to the shop table called "services" and enter a concatenated list such as - 1, 23, 34, 45, 55 (where each number is a service) but rather I should have a third table with two columns, one for service ID and one for Shop id.
Now if a single shop offers 100 services that would be 100 rows. If 10 shops have 100 services that is 1,000 rows.
Why is this better than having one more column in the shop table.

900,000 Rows
I have a large text file of data 900,000 rows to be exact.What is the best way for me to import them into a mysql table that will take the shortest amount of time?

Get Number Of Rows Above A Row
I have one table with a some user info, and a column with the users points. I would like to fetch the user data and sonehow calculate in which place that user is. Basically, the number of users that has more points than the user.

An exampel of the table and data:

+----+---------+--------+
| id | name | points |
+----+---------+--------+
| 1 | chris | 1000 |
| 2 | john | 900 |
| 3 | jeff | 800 |
| 4 | jay | 700 |
| 5 | michael | 600 |
| 6 | eric | 500 |
| 7 | stuart | 400 |
+----+---------+--------+

And an example of the data set I would like:

+----+---------+--------+----------+
| id | name | points | position |
+----+---------+--------+----------+
| 1 | chris | 1000 | 1 |
| 3 | jeff | 800 | 3 |
| 6 | eric | 500 | 6 |
+----+---------+--------+----------+

I'm not sure how to do this in one query, and I'm not even sure that it's possible, but if it is I sure would like to know how.

I'm using MySQL 4.1.16.

Best Way To Do This... Unique Key But 3 Rows Need It?
I have a weekly schedule table, each week should have a unique number but each week requires 3 different records (one for each game). What would be the best to create a unique identifier that is the same for the 3 records each week?

Delete Rows Using Max()
I want delete the row that having id < of max id - a number
I try this but i received an error:
DELETE FROM chat WHERE id<MAX(id)-3

Ordery By Two Rows?
Im codeing a script, which allows templateing.
YEY!.
So now im possitioning the moduals, which is great " order by 'order' " although, what happens if someone enters the same order number.

It will display the first coulmb, which i dont want it to do. It needs to not do.

So the question im asking you SQL buffs,
Is how to i do order by x then order by z

(and can you please remind me of asc is it? .. and the other one, and which way they go)

Re-orgering Rows?
I've never had to do this before and my mind is pretty lazy today so I'm hoping someone has a simple solution here...

I've got 2 tables, both from the same Software, that have the rows in different order. My problem is - I need to Merge the data from one DB to the other. Not sure how this ordering problem happened but here's an example:

Let's say the First User Table has the following:

id
type
username
password
email
gender
etc
etc2

Now the second User Table has:

id
type
username
password
new_password
gender
etc
email
etc2

Of course the REAL table has WAY more rows in there so it makes it very difficult, otherwise I'd be able to manually change them pretty quickly.

I'm using phpMyAdmin,

How To Get Top 8 Rows From A Table
I have a table with the fields xId, yId and count. count is an int from 1 to n. I would like to get the 8 rows that has the highest count. Like 983, 980, 850, 843.. and so on. Also I want the 8 highest counts grouped with yId.

That was a pretty simple query I found out. I read about it here:
http://www.plus2net.com/sql_tutorial/second-highest.php

Swap 2 Rows
I'd like to be able to move up/down output data (rows).
So i need some help how i could swap two rows in a table.

This is what i tried:

$q_pos = mysql_query("SELECT * FROM table WHERE id='$id'");
while($item = mysql_fetch_array($q_pos)){
$pos = $item['position'];
$q_down = mysql_query("UPDATE table SET position='".($pos+1)."' WHERE id='".$id."'")or die(mysql_error());
$q_up = mysql_query("UPDATE table SET position='".($pos-1)."' WHERE id='".($id+1)."'")or die(mysql_error());
}
This works fine but not when the next id isn't (id+1).

Swap Value Between Rows?
I'm wondering if there is a way to swap a column's values between rows. That is, given a table with columns col1, col2, and col3, I want to swap row 1's col2 value with row 2's col2 value, but leave the other rows and columns untouched.

To express this in some rough pseudo

UPDATE table
SWAP(col2)
WHERE col1 IN (1,2)
What I'm trying to do is to add a sorting order to an existing table, but the rows need to be swappable (each row represents a question to be printed on a survey, and needs "move up" and "move down" controls). I could do this in PHP with a couple of SQL commands, I'm just hoping there's a way to do this purely in SQL.

Subtracting Rows Together
I have a database that has a record for every port on every switch that I have, polled every 5 mins. What I need to do is pass this information through a formula to come up with utilization records.

My table looks like this....

Get Rows From One Table That AREN'T In Another
I have 2 tables, e.g., as so:

Selecting Rows 10-20
Is there a way to select a certain group of 10 rows? Say, I want to have multiple pages for a huge result. I want 10 rows on each page. How would I go about getting rows 11-20 on page 2?

Counting Rows
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/itsyouma/public_html/staff/memberlist.php on line 6

<?php
$SQL_statement = "SELECT * FROM users";
$resultset = mysql_query($SQL_statement);
$count_rows = mysql_num_rows($resultset);
?>

Max Rows In MySQL
I am using mysql Ver 12.22 Distrib 4.0.21, for Win95/Win98 (i32). I was wondering if there is a set maximum number of rows that can be added to a table.
If yes, is there a way to get around it.

Counting Rows
I have a table containing

Article | State
-------------------
1 | 10
2 | 10
1 | 5
1 | 5

and I want to count all Articles in one row with the different states

SELECT Article, COUNT(State=10) AS Ordered, COUNT(State=5) AS Free FROM...... GROUP....

but this SQL doesn't work (on my Server 4.0.20) I don't know if it work above but in the docu I can read somthing COUNT(expr) my be it works in 4.1

but is there another way that works wit 4.0.20?

Getting ALL Rows When Using GROUP BY
I have a query of the form

select * from t
group by f1, f2
having count(*) > 1

to find rows that share the same values for fields f1 and f2. However, it is only returning the first row in the group rather than all of the rows in the group.

Can I get ALL the rows in the group without having to perform a separate query based on f1, f2 from the above query? I tried an explicit "select all", but with the same result.

This is for a 3.23.49 installation.

I can't seem to find where this behavior is described or anything in the forums.