MYSQL :: Slow View Queries

View Slow Query Log
I searched here, google and MySQL docs but did not find an answer.
I'm using MySQL-Front from a Windows platform to administer a remote database. The remote server doesn't have phpMyAdmin or anything like that.
MySQL-Front reports 133 Slow Queries and an average of 15 queries per second. But I dont' know how get more information than that.

Slow Queries
I had a working web page that queried 20 tables and returned the data in the form of a table ... all of a sudden this stopped returning the results , and on investigation, up to ten tables, the query time is about 0.01 second ... but as the number of tables is increased to fifteen, the query time increases to 60 second .. and then gets too slow with nothing being returned at all.

A few days ago, this was all working fine. There have been no changes to the code on the web page ....
The version of mysql is 4.0.20, the server is a dual 1 MHz Xeon with 512 meg of RAM, running linux.

The tables have up to 15 fields each, and there is only simple text or numerical values in the fields.

Can anyone suggest what might have changed to suddenly slow down the query ?

What Causes Slow Queries
What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?

Slow Queries
I just got a new dual opteron system, with raid 01, 2gb ram, and fedora
core linux running 2.4.22-smp kernel. For some reason mysql is running
pathetically slow.

Queries that should take 2ms occasionally take up to
20 seconds. It isn't every time, but almost once per page. The problem
usually occurs with queries accessing the large tables (up to 1gb), but
not always. I've tried 2 versions I compiled myself (with flags
suggested in the readme), as well as the version off the mysql website.
All were 4.0.18, and all had the problem.

Slow Queries
I have set long query time to 2 seconds in my cnf file. Therefore all the queries taking more than 2 seconds are logged in the file called mysql-slow.log and the file size is 20 Mb.
Is there anyway to sort this file and find out the queries those are taking the most time so that I can optimize only those ones

Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form.
My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot?
Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?

Enabling -- Log - Slow - Queries
I want to enable --log-slow-queries of MySQL.

How do I do it without restarting the DB server?

Slow Queries And Optimization
I am hosting a site with an increasing number of members. Letely the site has become slower because of too many queries are being used.
Is there a way of asking the server which queries are used most often, as well as asking which queries take a long time to process so that I would know where to start my optimization process?

Unable To See Queries In MySQL In Database View Dreamweaver...
I have MySql running on my Mac using NaviCat, I have created a few tables and a Query, When I load Dreamweaver I can see the Tables but can not see the Query. Would anyone be able to help me with this?

MySQL Timing Out? Really Slow Queries, Already Indexed.
For whatever reason, MySQL does not seem to be executing. So, I ran a few stat functions and here is what I got:

Uptime: 220372
Threads: 39
Questions: 18748899
Slow queries: 808
Opens: 28723
Flush tables: 1
Open tables: 4096
Queries per second avg: 85.078

The "open tables" worries me for a few reasons. First, because it is 4kb (exactly, since it is divisible by 1024, evenly). This makes me think that maybe I am hitting a ceiling.

Second, there are a few variables in MySQL that equal this, and maybe it's a cap on something. Here are the variables that equal 4096:
query_cache_min_res_unit = 4096
table_cache = 4096
transaction_prealloc_size = 4096

Do any of those have anything to do with this? Am I even close? Are any of those values from mysql_stat a big deal?

Optimizing Slow Queries On Millions Of Records
I have a table that has millions of records in it.
About 100 records are added every 5 minutes (one per OIDID) (the sample
provided below has data for 2 OIDIDs (99 and 100)
And I have a webpage that executes 9 queries one after the other, and then
displays the results on the webpage.

When the database was empty, this process was very quick.
But, as the DB grew, it became slower.
Now it takes about 38 seconds for all queries in the example below.
MySQL 4.1 on Windows

I have tried different indexes, and they do help and are used, but the
queries still seem to take a long time. Code:

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.

Complex Queries Versus Multiple Simple Queries
I am constructing a database to contain information about stories posted on my site. Information included will be things like title, author(s), genre(s), story codes, synopsis, etc.

I worked out that storing this information properly, so that it can all be searched on, could take as many as ten tables.

My question is this: Is a single complex query really better (more efficient for the server) than multiple simple queries? In other words, I may need the information for as many as 25 or even 50 stories for a single page.

Is it better to get all of the information out of a single, massive, complex query, or is it acceptable to get the information essentially one story at a time, which could mean 25 or 50 simple queries...?

Many Smaller Queries Vs. Large Comlicated Queries
I am wondering if any one can help me. I have a page that will run around 85 smaller queries but if i combine the queries it will go down by almost half. This page is a high traffic page and I don't a complicated query taking up mysql resources while it created a temp table and such. My question is this:

Is it better for mysql to run a lot of smaller queries (ex: simple selects with zero or one join, group by) or one larger complicated query with everything combined.

The thing i have to keep in mind is that the mysql selects are comming off the localhost that the web server is also running on so they share the same resources.

Is This Possible With A View?
I'm trying to cut down on the size of my database so I've changed it so that rather than implicitly setting every value in one table, I've split the data into two tables: one table (iData) contains data for the start of evey week:
iData
-----------------------------
2000-01-01 Volume: 20000000
2000-01-06 Volume: 30000000
2000-01-11 Volume: 40000000
Another table (jData) contains the delta (difference) from the previous iData entry:
jData
-----------------------------
2000-01-02 Volume: 1
2000-01-03 Volume: -2
2000-01-04 Volume: 1
2000-01-05 Volume: 3
2000-01-07 Volume: 2
2000-01-08 Volume: -5
2000-01-09 Volume: 6
2000-01-10 Volume: 1
2000-01-12 Volume: 2
2000-01-13 Volume: -4
2000-01-14 Volume: 4
2000-01-15 Volume: 3

Obviously this will save a lot of space as the delta is much smaller than storing the exact value (standard compression technique).

So what I would like to do is create a view to automatically display all of the data as if it were all stored in one table:

All Data:
---------
2000-01-01 Volume: 20000000
2000-01-02 Volume: 20000001
2000-01-03 Volume: 19999999
2000-01-04 Volume: 20000000
2000-01-05 Volume: 20000003
2000-01-06 Volume: 30000000
2000-01-07 Volume: 30000002
2000-01-08 Volume: 29999997
2000-01-09 Volume: 30000003
2000-01-10 Volume: 30000004
2000-01-11 Volume: 40000000
2000-01-12 Volume: 40000002
2000-01-13 Volume: 39999998
2000-01-14 Volume: 40000002
2000-01-15 Volume: 40000005

I know I can do this programatically, but is it possible to do this with a view? Any pointers would be much appreciated.

View SQL Files
I am about to by the content of a site, but it is in sql files. I need to view this content and extract to make a new site from it in html format.

How do I view SQL files and extract it content? Is there any program that does it?

Can I View .frm, .MYD And .MYI Files?
At my current host, the server crashed and all data seemed to be lost. Lucky for us they had had a backup of all data which they put back on the server.

But now I get errors with some tables in phpMyAdmin where they show up as "in use" and when I view them I get the following error:

MySQL
#1033 - Incorrect information in file: *.frm
or

MySQL
#1016 - Can't open file: '*.InnoDB'. (errno: 1)

Because of the issues, they send me the *.frm, *.MYD and *.MYI files.
But am I able to view these files to see the structure or data?
And can I verify if structure or data is corrupt?

View Implementation
Anybody has implemented View for MySQL?
I think it can be done outside of Database
if query transformation approach is used.

CREATE VIEW Model
SELECT * FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid

Select brand_name from Model
where model.name = 'Test'

This can be transformed into

SELECT brand_name FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid and
Model_CA.name = 'Test'

VIEW Problem
I create the next view:

CREATE VIEW VideoDB.FindCustomer AS
SELECT id, last_name&' '&name&' '&initial AS cname, picture, birthday, birthday_already, account_type, rents, exp_date, deposit, exp_deposit, auto1, kinship1, auto2, kinship2,
auto3, kinship3, auto4, kinship4, auto5, kinship5, auto6, kinship6
FROM VideoDB.customers;

When I execute the next statement the result is 0:
select cname from videodb.findcustomer;

How can I concat the whole name.
last_name&' '&name&' '&initial AS cname

Create A View
I have a master table and five detail tables. Now I want to have an overwiew on all detail tables that are related to the master table. The problem is that there are to many repeated records.

My wish is that to have each record of detail table in a single row with master table information in the view table. I tried to reach this with joins (left or right). No chance.. Code:

Mysqldump View
I try to dump the view definition and records using the following command.

mysqldump -uroot -psecret! --databases test --tables proxy_view > mytest.txt

What all I get is the view definition without records. What is the correct method of dumping a view?

Create View
I currently have a script that I have developed on a MS SQL server. As part of it I use a VIEW to do most of my lookups. I want to transfer it to a MYSql DB, but when I try and CREATE VIEW I get the following message....

mysql> create table t (qty INT, price INT);
mysal> insert into t values (3, 50);
mysql> create view v as select qty from t;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'view testv a
s select qty from t' at line 1
mysql>

I am using MYSQL for Netware. I am using an example that I have found to demostrate it, but it just does not work.

Exponential View
how to store numbers in exponential view to MySQL. E.g. 4,56e-9 = 0,00000000456

I have 4,56e-9 as a variable in perl and want to store it to MySQL. Decimal as typ seems not to work. Does i realy have to convert it to 0,00000000456?

How To View All The Sp Created?
is there a way to view all the stored procedure created in mysql? a command to view all avaialble sp's?

Creating A View
I'm running the following create view:

create view letters_of_recommendation_x_organizations as select * from organizations o left join letters_of_reccomendation l on o.OrgID=l.orgid

, and getting an error "1064 (42000): You have an error in your SQL syntax; (blah blah blah) ... syntax to use near 'view junk as select * from organizations'"

I've checked the select statement - it runs ok.

I've tried a simpler select statement as well, "create view junk as select * from organizations" .

CREATING A VIEW
I am trying to creat a virtual table by using a dynamic sql. This sql needs to be reuseable on different applications. To make matters more simple to explain let's say my table is a 3 column table. The ID column is a process identifier.

ID     Integer
NAME   VARCHAR
VALUE  VARCHAR

The data will appear like this:

1      FIRSTNAME      JOE
1      LASTNAME       SMITH
2      FIRSTNAME      MARY
2      LASTNAME       JONES

I need to create a table that would take the data above and create the table like below:

ID           INTEGER
FIRSTNAME    VARCHAR
LASTNAME     VARCHAR

The data would look like this:

1      JOE            SMITH
2      MARY           JONES

Any ideas on this could work?

View Optimization
In my database I have separate four tables and from that four tables i have created one view. Each of the table contains round five lacs of records. So when view gives result it is approximately around fifteen lacs of records,

When I call that view like "select * from MyView where name='vimal'" it takes too long to give me result. And due to this one query my application server load increases by 80%. When I execute this view that time mysql Memory consumption is approximately 80% of total memory. What I can do to optimize the same? What are the other alternatives?

View Implementation
Anybody has implemented View for MySQL? I think it can be done outside of Database
if query transformation approach is used.

CREATE VIEW Model
SELECT * FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid

Select brand_name from Model
where model.name = 'Test'

This can be transformed into

SELECT brand_name FROM Model_CA, Brand
WHERE Model_CA.brand_rid = Brand.rid and
Model_CA.name = 'Test'

Create View
I must be going mad! How come I can't create a view?

mysql> create view testview as select * from userdetails;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'view testview as select * from userdetails' at line 1

(View) Performance
I have a huge table with product data from different shops in my database. There is a flag column 'import' witch flagt products with 1 for my PHP script.

To select from one shop all product individually, work with it, and then update these product import flag to 0 in the database, seems to be the slowest way. I thougt about a view about all products of a shop with flag '1'. Code:

If Condition In View
Basically i want to add put a where condition in a view if the user variable '@org_id' is > 0. Here is some pseudo code:

CREATE VIEW view_students AS
SELECT *
FROM students
If (@org_id > 0) THEN
WHERE org_id = @org_id
END IF

I've tried lots of different approaches but i just cant figure out how to do it.

Currency View
When displaying product prices in MySQL database its only showing 17.5 instead of 17.50. Tried different things in Excel to make the view display correctly, but had no luck. Any ideas?????

View Dependencies
When creating a view, it will (almost certainly) take information from one or more existing tables/views in the database. If I come to the database with no knowledge about the view (other than its name), how can I determine which underlying tables it depends on?

(I can do this is PostgreSQL by performing queries against the pg_class & pg_depends internal tables but I haven't discovered any way of doing it in MySQL either with SQL queries or with the C API.

View The TimeStamp
I know you can view when a table was updated in mySQL Administrator. Is there a way in SQL to view that TimeStamp?

View With Parameters
can I put parameters into a view?

example

CREATE VIEW `dmz_sms`.`vwProva` AS
SELECT * FROM tb_sms where id=@ID;

I get error 1351.

SQL Source For A View
How do you obtain the SQL source that created a View?

I can Describe the view, but that doesn't give me the SQL that created it.

. . .

I should have searched the forums a little longer. The answer lies in the

SHOW CREATE VIEW viewName statement.

Can't Create View
I am using MySQL 5.0.0 server Alpha release. Though the manual suggests that views on tables can be created in this version but i am unable to do so.

I have a table named 'ads'.
I am creating a view named 'test' with following syntax..
create view Test as select * from ads;

Msg returned by MySql is:
[MySQL] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'view Test as select * from ads' at line 1.

UPDATEABLE VIEW
I am getting an error when updating a table from a view in MySQL 5.
I have created a view & then reference it in my update command, by MySQL caomes back with an unknown table error.

I can 'show' & 'describe' the view, so it is definately there.

VIEW In MySQL
select NO_RESPONSABLE, NOM + PRENOM as NAME from RESPONSABLE

Gives me the number of the ID, but also "0" as the result of the
concatenated NAME.

View Like In Sql2000
I have just started to look at mysql. I had to download mysql 4.0 as i had problems running mysql 4.1+.

Basically I want to know is in SQL2000 i can create views, I was told I cannot create them in mysql and was wondering how I overcome this problem.

CREATE VIEW
My servers DB is using MySQL ver4.1.10.

I need to use a query in the type CREATE VIEW

CODECREATE VIEW currentCostOfStock AS
    SELECT P.partName, P.partNo, P.manufacturer, S.vehicleType, S.years, S.price, S.stockLevel, S.price * S.stockLevel AS productCost, SUM productCost AS stockCost
    FROM Parts P, StockLedger S
    WHERE P.partNo = S.partNo ;

VIEW In MySQL
select NO_RESPONSABLE, NOM + PRENOM as NAME from RESPONSABLE

Gives me the number of the ID, but also "0" as the result of the
concatenated NAME

Create View
I'm really really stuck!
I am trying to create a view in the database but I don't know why my syntax won't work. Here is the statment...

[MYSQL]CREATE VIEW options AS SELECT customer_extras.extrasID, customer_extras.itemID, customer_quote.quoteID FROM customer_extras, customer_quote WHERE customer_extras.itemID=customer_quote.itemID;[/MYSQL]
The SQL administrater program says there's a mistake in the SQL so i tried to type it into the command line as well. Neither worked. I haven't got a clue. I have done this LOADS of times before but it won't work for me today.

I'm running the database on an xp pro machine. Latest mysql server and administrator.
Any help whatsoever would be great as I have wasted the last day looking on the internet. It's really annoying me now. I'm ready to give up

Create VIEW
I need to create a view in my mysql 4.0 and I canÂ´t, the database give me a error.
The syntax is:

CREATE VIEW LosProveedores (Nombre, Telefono, Fax, Direccion)
AS
SELECT LasEntidades.NombreIdentidad, LasEntidades.Telefono,
LasEntidades.FAX,Proveedores.Direccion
FROM LasEntidades, Proveedores
WHERE LasEntidades.IDIdentidad = Proveedores.IDIdentidad

and the errror message is:

Error 1064, you have error in your sql syntax.....Check the manual that corresponds to your MySQL server version for the right syntax to use near 'VIEW LosProveedores (Nombre, Telefono, Fax, Direccion)
AS
SELEC

Can't View Tables
I have a table located in the following directory. C:ProgramFilesMysqlDataRecipesTablesshirt.sql
The Database is Recipes and within that Database is another Database called Tables that has several tables I just used the shirt table as an example. How do I get to this table?

Create View Question
I'm trying to create a view in mySQL Query Browser. But when i create the view i get the following error,

"Definer is not fully qualified"

Here is the view itself,

CREATE VIEW `dzoic`.`POLLS_vwNumAnsPerQuestion` AS
SELECT
COUNT(poll_answers.answer_id) AS NumAnswers,
poll_questions.question_id
FROM
poll_answers
INNER JOIN poll_questions ON poll_answers.question_id = poll_questions.question_id
GROUP BY
poll_questions.question_id
Now i've played with it a little and when i add a definer (DEFINER = `dzoic`) i get the following error,

Script line: 1Definer is not fully qualified
Script line: 1You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VIEW `dzoic`.`POLLS_vwNumAnsPerQuestion` AS
SELECT
COUNT(poll_answers.an' at line 1
now i know there was an issue with a bug in mySQL versions prior 5.0.16 when creating a view. Currently the version of mySQL that i'm using is 5.0.18 and the bug was fixed by this release.

Slow View Queries

I have a fairly complex view which, when you execute a SELECT * shows all info very quickly (0.24 seconds to return nearly 3000 rows). However, when I start throwing slightly more complex queries at it, things really slow down - an average query can take 40 seconds to execute.