Optimization :: Subqueries

Subqueries On 4.0.25 Without Subqueries
i have 2 tables. knowing subqueries cant be performed on this version how would i write the following statement?

i have 2 tables
table.a, table.b

I want to select all records from table.a that match table.a.id = table.b.id WHERE table.b.varchar = 'value'

once the table.b.varchar is found, get table.b.id and match it to any records in table.a.id that match it.

Can this be done in 1 statement. I'd hate to have to run a loop here. I've tried without success JOIN statements.

here is one of my attempts:
"SELECT table.a.* FROM table.a, table.b WHERE table.a.id = table.b.id AND table.b.varchar = '$value' GROUP BY table.a.sortkey ORDER BY table.a.is_primary DESC ";

this does return 1 row, and joins the 2 tables accordingly, however i need all rows in table.a that match the table.b.id where table.b.varchar = 'value'

View Replies !

---

Optimization
I wanted to ask information to you on like optimizing MySQL, in how much in the event that I illustrate I have found to you of 'the anomalous' performances.
In the first place I have intalled MySQL 5.0.11. I have a table 'Anag' with 31 fields, primary key Id AutoInc and one key univoque for Desc+Indirizzo+Localita.
If from the Query Browser I execute 'select cod, descr from anag' come extracted 150000 records in 4 second ones. If instead I execute 'select cod, descr from anag order by descr' the 150000 records they come extracted in 100 second ones.
I have tried to create a single index on the field descr, but the times do not change. The times are not change to you after to have made the 'Check Tables' and 'Optimize Tables'.
Not creed that is normal school that the clause Order by on an indexed field me must cost therefore a lot. What I could modify in order to improve the performances?

View Replies !

---

My.ini Optimization
I'm having some performance issues that seem to be MySQL related.

I am running W2KAS IIS, PHP 5.1.4 and MySQL 5.0.21. Memory or CPU speed do not appear to be the problem.

I'm running a phpBB forum and having difficulties editing posts, the install of phpBB seems to be perfectly okay, but when editing posts it either takes forever and times out or just comes up with a blank page after a couple of minutes.

I notice that mysqld-nt.exe will use up about 30-50% of cpu time when this is going on. Code:

View Replies !

---

Use Of Subqueries With IN
I'm trying to do the following query:

select * from table1 where col1 in (select col1 from table2);

I keep getting a message to check my query after "in". But I've cut and pasted that query and it works. This is a very simple query so I don't understand why it doesn't work.

View Replies !

---

Subqueries With ALL
I got following statement:

SELECT * FROM ip WHERE id <> ALL (SELECT ipid FROM pcip);

Like I can see in the documentation, this should be correct, but SQL drops a SyntaxError near "ALL (SELECT ipid FROM pcip)"

What it dows should be ovious, select all IPs that IDs are not noticed in the PCIP table under IPID

The Tables are set up correct, means both tables exist and got values for the mentioned collums

My MySQL version is 4.0.21 if that is part of the problem...

View Replies !

---

Subqueries For V3.23
An upgrade is definately coming, as some some SQL practice, but I'm trying to figure out how to do a subquery and not entirely certain where to turn to.

From my limited understanding of SQL, what I want to do should look like:

SELECT
(SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='0') as a,
(SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='1') as b,
(SELECT count(SurveyData.Choice) WHERE SurveyData.Choice='2') as c
FROM SurveyData

The SurveyData table contains 3 fields: SID (Int), Name (Varchar 100), Choice (Int)

I'd like to use it as the basis for a reusable/scalable web survey app (perl CGI), but all I'm getting is a syntax error.

View Replies !

---

Subqueries With NOT IN And &lt;&gt; ALL
I am having troubles with the following on 4.0.22:
select *
from question as q
where q.id NOT IN (select distinct question from choice );

choice.question ---> question.id

Apparently there's a syntax error, at (select distinct ....)
<> ALL yields the same result.

View Replies !

---

Possible Without Subqueries
I'm trying to merge the result of 2 queries but I can't use subqueries.

The first query gets all the sign-ups from a particular referer, the second query gets how many of those sign-ups are still active.
So the first query does a count, group by ref and the second one does count where active group by ref - I know I could do this easily with subqueries but without them I'm lost.

View Replies !

---

Subqueries?
I'm using MySQL Control Center 0.9.2-beta. Will this take subqueries? I'm trying to extract data from tables in two different databases, and I'm not all familiar with JOINS. What is the syntax to do this in a subquery? In a JOIN?

View Replies !

---

Max() & Subqueries
Cant seem to get what i want Missing something so fundamental. Did it with subqueries on server at home just cant seem to get my head round it without using subqueries

I have 3 tables ....

View Replies !

---

My.cnf Optimization
Server: AMD Opteron 8212 (Quad Core), 8 GB RAM

my.cnf:

Quote:

[mysqld]
skip-name-resolve
skip-locking
skip-innodb
#skip-grant-tables
old_passwords=1
default-character-set=utf8
character_set_server=utf8
init-connect='SET NAMES utf8'
thread_concurrency=2
max_connections=768
#max_user_connections=100
#key_buffer=512M
key_buffer=768M
myisam_sort_buffer_size=64M
join_buffer_size=2M
read_buffer_size=3M
sort_buffer_size=1M
#table_cache=1024
table_cache=1024
thread_cache_size=256
wait_timeout=60
connect_timeout=30
max_allowed_packet=16M
max_connect_errors=10000
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#log-slow-queries = /var/log/mysql_slow_query.log
#long_query_time=1
set-variable=long_query_time=5
log-slow-queries=/var/log/log-slow-queries.log
#log-queries-not-using-indexes
log-error = /var/log/mysqld.err
low_priority_updates=1

Extended status:

Quote:

+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 2320 |
| Aborted_connects | 1933 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 85775335 |
| Bytes_sent | 760429570 |
| Com_admin_commands | 95 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 14258 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 3105 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 3312 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 5062 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 21353 |
| Com_set_option | 14174 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 53 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 108 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 1 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 3789 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 16153 |
| Created_tmp_disk_tables | 26 |
| Created_tmp_files | 3418 |
| Created_tmp_tables | 625 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 3716 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 59 |
| Handler_read_key | 12262128 |
| Handler_read_next | 244141509 |
| Handler_read_prev | 54807585 |
| Handler_read_rnd | 35201 |
| Handler_read_rnd_next | 603941647 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 732517 |
| Handler_write | 3644263 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 3 |
| Key_blocks_unused | 508958 |
| Key_blocks_used | 137760 |
| Key_read_requests | 70876514 |
| Key_reads | 137778 |
| Key_write_requests | 8305 |
| Key_writes | 6096 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 769 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1102 |
| Open_streams | 0 |
| Open_tables | 1024 |
| Opened_tables | 1069 |
| Qcache_free_blocks | 935 |
| Qcache_free_memory | 26676448 |
| Qcache_hits | 16752 |
| Qcache_inserts | 20392 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 997 |
| Qcache_queries_in_cache | 2891 |
| Qcache_total_blocks | 6768 |
| Questions | 94632 |
| Rpl_status | NULL |
| Select_full_join | 58 |
| Select_full_range_join | 0 |
| Select_range | 2826 |
| Select_range_check | 0 |
| Select_scan | 2809 |
| Slave_open_mysql_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 1473 |
| Sort_merge_passes | 3706 |
| Sort_range | 3811 |
| Sort_rows | 17002892 |
| Sort_scan | 1847 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 43727 |
| Table_locks_waited | 4318 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 341 |
| Threads_created | 1428 |
| Threads_running | 304 |
| Uptime | 1250 |
+-----------------------------------+-----------+

View Replies !

---

Optimization Question
I've got a table T with 20 columns (fixed length) and one column C
which is varchar.

Somehow C has to be varchar.

T may have millions of records.

Is there a way to optimize this situation ?

View Replies !

---

UNIONS In Subqueries
I have a problem using UNIONs inside subqueries. I have simplified my query to make it more readable. The question is about the right syntax.....

View Replies !

---

Join Optimization
what (in general) causes a join to use temporary tables and
filesorts...
More specifically, what can I tune to avoid it? I can give more info about
a specific situation if anyone=B9s interested.

All the docs say is that you are likely to get a =B3Using temporary=B2 if you
sort on a different column set than the =B3group by=B2, but I=B9m not doing
that...something else must be triggering it.

View Replies !

---

How To Rewrite This Without Subqueries?
i have a table that contains the fields order_id, canceled, and
captured. the same order_id can appear many times in the table. i
want to retrieve all the order_ids for which NO record exists that has
both canceled and captured = N.

i am not able to upgrade my version of mysql at the moment, so i would
like to write a query that does that with no subqueries.

View Replies !

---

SQL, Count And Subqueries
I have one table that lists a number of courses available (including
CourseID, CourseName and MaxPositions), with CourseID being auto_increment and unique

I have another table which has CourseRegistration (Containing CourseID and
CustomerID), with no unique fields. Customer information is stored
elswhere.

Without having a field in the Courses table holding the number of people
currently registered, how would I return a result set which included:

CourseID, CourseName, MaxPositions and the Number of people registered to
take that course.

Preferably this would not use features in MySQL 4.1, but anything is fair
game at this point.

View Replies !

---

Theory Of Subqueries For 4.1.7
While converting SQL statements for a database change, I discovered a
big performance hit in MYSQL with subqueries vices Sybase. I'm hoping
that someone might be able to help me understand why?

I have two tables USERS (2200 records) and JOB Decriptions (163
records). I wanted to retrieve all the job description not in the
USER table. (No Indexes on JOBDESC currently)

select JOBDESC from JOBS where JOBDESC not in ( select JOBDESC from
USERS where JOBDESC not NULL )

In Sybase the query returns in about .56 secs
In Mysql 4.1.7 query returns in about 8.78 secs

The funny thing is if I run the query like so

select JOBDESC from JOBS where JOBDESC not in ('President','Vice
President','Treasaur','Secretary')
returns in .03 secs.

Those four descriptions are returned by the inner select.

All select statements individually return in .03 secs.
I created indexes on both tables for JOBDESC and reduced the time to
..97 secs. I used explain command to help understand what is going on
hence the indexes, but why the difference in speed?

View Replies !

---

UNION And Optimization?
My question is about a UNION query to deal with an (annoying) JOIN
over two tables. I am joining over a double column primary key (where the
order of the columns can be changed). This is so slow using a
join, but very fast using a union. How come this is? Code:

View Replies !

---

Removing Subqueries
the query i want execute is:

select email from avalia where email in (select * from avalia where nome=´img/im1.jpg´) and email in (select * from avalia where nome=´img/im2.jpg´)

but mysql 4.0 don't acept subqueries.

View Replies !

---

Comparison Optimization
On to my problem. I'm running a query on a table with over 100,000 rows in it. Basically, my query is performing a "similar" check on books that people have logged in their "book list". It looks through the table and checks to see who has the most number of books similar to you. Here's the query:

select count(a.mem_id) as numMatches,m.username,m.mem_id as memberID from book_list a LEFT JOIN book_list_members m ON a.mem_id=m.mem_id where a.book_id in(224,164,30,43,47,1,6,52,90,45,120,270,...,2442) GROUP BY a.mem_id ORDER BY numMatches DESC LIMIT 50

In the in() comparison, this can be anywhere from 1 integer to 1000 integers, it all depends on how many books a user has in their list. I've indexed "book_id", but unfortunately if there are more than say, 100 elements in the IN() comparison, the query takes anywhere from 1-2 seconds to execute. That's a long time when I have hundreds of users hitting a website and running that query. Is there a better way to do this? Is there a way I can "store" this query result somewhere, and only have it updated every couple hours (cached in other words, for immediate access).

View Replies !

---

What Is Query Optimization?
What is mysql indexing ? give some few examples

What is query optimization ? give some few examples

View Replies !

---

Optimization Advice
I'm having Database connection problem on my VPS server runing SMF forum script.
It often displays "unable to connect to database" notification when there is more than 200 users online (in 15 minute period).
I have raised max mysql connection number from 100 to 150, but that didn't help.
Connections per second number has doubled in last month (but traffic is only slightly higher)

Here are some mysql informations, any advice how to get this numbers to normal values is welcome.

View Replies !

---

DataBase Optimization
What does optimizing a table do exactly in mysql. I have a users table and the primary key is userid which i use to track users and a bunch of other stuff. If I optimize the table does it delete any unused userids considering that these are auto_number? Because if a user was logged in say, and I optimized my table, and his/her userid changed.

View Replies !

---

Optimization Of Select
Anybody can help me on optimization some queries
In the slow_query_log the query had the execution time: 12 sec
(Query_time: 12 Lock_time: 0 Rows_sent: 75 Rows_examined: 469546) but if I run the same query in phpMyAdmin i get the time 2,6 seconds.
If I remove the " order by " the query it will be more efficient, but I think there is something wrong if only the ORDER BY it takes like 2 seconds. Any Ideas ?

EXECUTION TIME 2.6183
SELECT cars.id,cars.url,cars.car_name,cars.year, videos.id,videos.brand_id,videos.car_id,videos.name,videos.rewrite FROM cars LEFT JOIN videos ON videos.car_id = cars.id WHERE brandID = '50' ORDER BY year DESC;

EXECUTION TIME 0.0235 sec
SELECT cars.id,cars.url,cars.car_name,cars.year, videos.id,videos.brand_id,videos.car_id,videos.name,videos.rewrite FROM cars LEFT JOIN videos ON videos.car_id = cars.id WHERE brandID = '50' ;

View Replies !

---

SELECT Optimization
I have a table that basically keeps track of the ID numbers of items in 9 other tables.
I do have the need to see all the 'real' information at once and not just the ID numbers.
So, my question is. Is it better to have a SELECT statement with 9 JOINs in it, or to have the SELECT statment only pull the ID numbers and then do separate SELECTs on each ID number.The logic of my brain suggests the first, but I just wanted to check.

View Replies !

---

Sub Query Optimization
I am running a dated version of mysql, like 3.23.xx, and I have a query:
Code:


$sql = "SELECT * FROM users_info
WHERE first_name != '' AND users_id IN (SELECT DISTINCT id AS list FROM equipment WHERE id != '0' GROUP BY list )
ORDER BY last_name ASC ";


The query is substantial slower than just running a nested query

View Replies !

---

Subqueries And The IN Operator
hey all,

i am having serious problems with getting my head around why this will not work. i am trying to return a number of games played with a count of the number of people in it. the table structures are as follows:

games

gameref | gamepot | gamedate | gamelength |

..1...... | .... 50.... | 12.12.04 | ... 03.00.00 |

winnings

gref | pref | amountwon | stake |
1 . .| 1 .. | ....20 ...... | 10...|

i have used the dots to try and keep the table in some sort of form. i have only entered one row of data for each just to show the format of it, if this is not enough please ask for more.

the games table holds each game that has been played, the total pot available to be won the date played and the duration of the game.

the winnings table is teh winnings of each player in each game. the gref is the game it refers to, the pref is teh player it refers to, (there is a player table but is not used in this query at all). the amount that player won and the stake they entered into the pot.

if ya didnt gues its a poker database. my problem is as follows.

Quote: $sql = ("SELECT games.*, COUNT(winnings.gref) as playercount, winnings.gref
FROM games, winnings
WHERE gameref = gref
group by gameref
order by gamedate");

this query works a treat. it counts the amount of players in each game and outputs it all nicely.. but i wanted to edit the query to be able to show just all the games a single player had been in.

Quote: $sql = ("select games.*, COUNT(winnings.gref) as playercount, winnings.gref
from games, winnings
where gameref in (select gref from winnings where pref ='$pid')
group by gameref
order by gamedate");

$pid is passed into the page by

Quote: pt_register('POST','pid');

but this throws an error

Quote: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/ghoxmfjz/public_html/database/poker/allgames.php on line 32
Query failed!

line 32 is:

Quote: $nrows = mysql_num_rows($result);

i can get it to work if i substitute the subquery for (1,2,3) being the first 3 games played. this would assume a certain player would have been in these 3 games.

the problem i get is that the COUNT function just counts every player in everygame not the individuals. if i group it by gref it counts them (sort of.. it doubles the count) but then does not return any of the other information for the query.

i am very confused any and all suggestions of things to look at or consider or a straight ur doing XXX wrong would be grand.. thx all

Jon

oh btw if you wish to look at the output if that helps the URL is:

http://www.happygolucky.me.uk/database/poker/allgames.php

with the variable $pid as the reference to try the player.

View Replies !

---

Subqueries Clarification
im using MySQL 4.1 the one the comes with XAMPP installer.. I have a question regarding correlated subqueries..in this version of
MySQL, are correlated subqueries supported?

as in like..

$query1= select field from table where field = 1;
$query2= select field from table4 where field= 4;

Insert into tmpTable ($query1,$query2)

View Replies !

---

SubQueries Prior
Does anybody know how to rewrite the following query to run on mysql 4.0.20.
Is it possible to do this with JOINS? (I don't really have to join 2 tables...
SELECT *
FROM OAK_photos
WHERE cat_id = (SELECT cat_id FROM OAK_photos WHERE id=$id)
AND order_nr >= (SELECT order_nr FROM OAK_photos WHERE id=$id)
ORDER BY order_nr ASC
LIMIT 0 , 5

or should I run the 2 subqueries first as seperate queries and pass on the outcome via variables to the third query, the above main query?

View Replies !

---

WHERE Statement Optimization
I have a function that tacks together an unknown, variable number of ID numbers that I'm using to filter results in a MySQL. The function constructs a string that looks similar to:

WHERE field = value1 OR field = value2 OR field = value3...

Is there a more optimal way to structure this statment? Can you compare a field against an array, for instance?

View Replies !

---

Using CONCAT In Subqueries
SELECT tblmonths.fldMonth, tblyears.fldYear, tblmonths.fldID, CONCAT(tblmonths.fldID," ", tblyears.fldYear)
FROM tblmonths, tblyears
where CONCAT(tblmonths.fldID," ", tblyears.fldYear)
NOT EXISTS
(SELECT CONCAT(fldMonth, " ", fldYear)
from tblexpensesclaims)
GROUP BY tblyears.fldYear, tblmonths.fldID

View Replies !

---

Subqueries In The FROM Clause
I'm using 1&1 web hosting services with MySQL as database (Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i686) using readline 5.0).

When I ran an example in Chapter 13.2.8.8 from MySQL 5.0 Reference Manual:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;

I always get the following error message:

ERROR 1064 (00000): 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 'SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb

View Replies !

---

Inner Join Optimization
I have Two tables (ENGINE=MyISAM), each table (t1 and T2) have a
primary key bigint (ID),


I execute the request


Select count(*) from t1 inner join t2 on t1.id =t2.id;


it takes 8 secondes


in eahc tables i have more the 2 million records and more. Can anyone
provide me a suggest to resolve this problem (Changing Mysql
parameters , redesign table...?)

View Replies !

---

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 Replies !

---

Problem With Subqueries
I am trying to find the rows in table1 that do not have matching rows in table2 in order to set the value of a field in table1. I can get a query to return the rows that do have matches with this query:

select item1 from table1 where item1 in (select item2 from table2);

However, the following queries return no data:

select item1 from table1 where not(item1 in (select item2 from table2));
select item1 from table 1 where item1 <> all(select item2 from table2);

My understanding of the first query is that the not operation would be applied after evaluating whether item was in the result of the subquery. With the second query, I thought it should return true if item is not equal to every result in the subquery. If I try to use a left join and then simply find the rows that have a null value coming from table2, I get the correct resultset but I can't use that as a subquery for my update statement. Since table1 is what is being updated MySQL tells me that table1 cannot be used in the subquery for the update statement. I have this working in a stored procedure by importing the result of the join into a temporary table, but I'd rather not have to write all that data and then delete it.

View Replies !

---

Can I Do Subqueries With MySQL?
I wanted to know how can I do subqueries with MySQL?

View Replies !

---

Slow Subqueries
I know (by internet) that mySQL is very fast. Problem is that my subqueries that are very fast in Access or SQL Server but they are very slowly in mySQL - since I have to restart my computer because mySQL freeze all the processes. (the resources where used at maximum - 2 Gb RAM, 2,5 Ghz processor)

This is the query :
SELECT NPL, PP FROM P_A INNER JOIN ACTIONS
ON P_A.NA =ACTIONS.ACT_N WHERE P_A.NA in
(SELECT NA FROM P_A WHERE NP ='ABC')

P_A has 5 columns and 12000 rows
Actions has 5 columns and 770 rows

Any suggestions ?

View Replies !

---

Column Subqueries
to the best of my knowledge, a column subquery is a single column resultset with many rows, i'm assuming joined by some previous column / row value.

can anyone show me a column subquery, manybe even a multiple column subquery, with a very small discussion on what they did and what it would return?

I'm looking to return a query like username, company name, company location, application, application role, application persission where I do not get the "normal" repetitive SQL resultset.

note: column subqueries may not be my direct answer but I still wanted to see what column subqueries was all about.

View Replies !

---

Subqueries In MySQL 4.0?
I've got the following query. I just found out that subqueries are not supported by MySQL 4.0...

How would I replicate this to work with MySQL 4.0?

SELECT DISTINCT CategoryID, tblCategory.Category FROM tblCategory INNER JOIN
(SELECT Cat FROM ( SELECT tblCategory.Category AS Cat FROM tblEntries)
UNION SELECT Cat FROM ( SELECT Category2 AS Cat FROM tblEntries) UNION
SELECT Cat FROM ( SELECT Category3 AS Cat FROM tblEntries)) As A ON
tblCategory.CategoryID = A.Cat;

Table tblCategory
CategoryID = index key
Category = Text entry describing the Category

Table tblEntries
Category = key link to tblCategory
Category2 = key link to tblCategory
Category3 = key link to tblCategory

Basically, I want a list of Categories that are used in tblEntries.

View Replies !

---

5.X Optimization Tips
I have a unix box that has 32G of RAM. I want to optimize MySQL because right now it's pretty slow using the default settings. Do you have any configuration settings that you recommend to maximize MySQL

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 512M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 512M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 512M
thread_cache_size = 8
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M



Edited 1 time(s). Last edit at 01/26/2009 03:46PM by John Doe.

View Replies !

---

Subqueries And Limit
I am a beginner with MySQL and I have this very large table where queries take a long time to complete. I was trying to limit the amount of time by limiting the scope of the query to only a few rows, by using LIMIT. To that effect a construct like:

SELECT * FROM table LIMIT 100;

seems to work perfectly, because it returns the 1st 100 rows from the table.

The problem is, I cannot use this in a subquery. In other words, if I try something like:

SELECT * FROM (SELECT * FROM table LIMIT 100) as temp;

I'm getting an error. I read the documentation about subqueries in the FROM clause, but I cannot figure out what's qrong with the syntax.

View Replies !

---

WHERE Clause Optimization
if there is a way to optimize 'where' clause with a wild card character, ie - the way star (*) works in unix/linux?
I want to be able to do somsething in this spirit:
WHERE proj != 'P%'
meaning "where data in field proj doesnt start with P"

View Replies !

---

Query Optimization Help
I've got a query that is trying to find matching rows for two endpoints. Like this...

$sql = "SELECT * FROM my_table WHERE 1234567890 BETWEEN begin_range AND end_range";

The problem is, my_table has about 3.5 million rows, and therefor takes about 4 seconds to run. I've already indexed both the begin_range and end_range fields and I'm just wondering if (other than throwing hardware / memory at the problem) there's anything else I can do?

Is there a better way to query the data?

View Replies !

---

Subqueries To Mysql 3.x.x
I know that mysql 3.x.x does not support subqueries so i need your help with a query..

I need to make possible to search concurrently both fields. The user can insert only part of the word that is searching so i used the like '% keyword %'. in musql queries

Now my tables are
Table Titles:
Title (mediumtext)
AuID (integer)

Table Authors:
Author (mediumtext)
Au_ID (integer)

If i had support for subqueries then i would have done it easily

select Title from Titles where Title LIKE '%user title entry%' and AuID= (select AuID from Authors where Author LIKE '%User author entry%')

Now i don't have support for subqueries i need help for writing this query.

View Replies !

---

Queries And Subqueries
SELECT COUNT(t.id) AS tuttotal, (SELECT COUNT(t.id) FROM tutorials AS t WHERE t.status = 1) AS pendtotal, (SELECT COUNT(r.id) FROM reports AS r) AS reptotal FROM tutorials AS t
If I input that SQL into mysql it returns exactly what I'm after. And on my development server it works as expected.

However on my production server I am getting an error when I try it in PHP;

You have an error in your SQL syntax near 'SELECT COUNT(t.id) FROM tutorials AS t WHERE t.status = 1) AS pendtotal, (SELECT' at line 1

I think it might be that my server is running an earlier version of MySQL, 3.23.28 I believe.

View Replies !

---

DB Structure/optimization?
I was wondering if anyone could recommend any REALLY good articles/tutorials/whatever on good database structures or optimization?

View Replies !

---

MySQL Optimization??? Help!
im recently been suspended daily by my hosts, saying that im causing a high load on my server with around 500,000 requests daily!!!

Now they are saying "usual" reasons for this are databases, and i run a big phpbb forum. Unfortunatly my knowledge of MySQL is limited, very limited in fact, so i cannot make optimization changes what-so-ever. Iv asked for help at phpbb official support......no answer.... however from browsing, i found "modded" forums can sometimes cause over 100 requests per post, but it can be optimized...... so im looking for help, tips, or anything you guys can give me to combat or fight this problem!

I have access to phpmyadmin, the forum db size is around 100mb, 5,000 members, not all active!

and i also run a topsite list!

if i cannot do this myself im willing to let a mysql expert have a look round, see if thye can fix this, and ill pay them to do so, if must be but my budget is limited, otherwise id just move to a new host


View Replies !

---

Query Optimization Help Please
I am traversing over a table that holds "adspaces" via two aggregate functions to get the total number of adspaces for a publisher and the number of approved ones. The query is very slow and there must be a way to convert the subqueries into joins or anything else. Would really appreciate it if someone could give me a hand.

PHP

$sqlQuery =      "SELECT a.*, " .
                       "(SELECT COUNT(b.id) FROM ".DB_TABLENAME_ADSPACE." AS b " .
                          "WHERE b.publisher        = a.publisher " .
                           "AND b.status_approval <> '" . STATUS_WAITING . "' " .
                          ") AS num_adspaces_total, " .
                       "(SELECT COUNT(c.id) FROM ".DB_TABLENAME_ADSPACE." AS c " .
                          "WHERE c.publisher        = a.publisher " .
                            "AND c.status_approval  = '" . STATUS_APPROVED . "'" .
                         ") AS num_adspaces_approved " .
                  "FROM `" . DB_TABLENAME_ADSPACE . "` AS a "            
                . "WHERE (a.status_changes  = '" . STATUS_WAITING . "' "
                .     "OR a.status_approval = '" . STATUS_WAITING . "') "
                .    "AND a.watchlist       != '" . WATCHLIST_WAITING . "' "
                . ";";

View Replies !

---

SQL Query Optimization Help Please
SELECT sess.id, u.id AS user_id FROM
(SELECT s.id, s.session_id
FROM `ll_session` AS s
WHERE s.online = &#3912;'
) AS sess
LEFT JOIN `ll_user` AS u ON (sess.session_id = u.last_visit)
What I have so far is this:

SELECT s.id, s.session_id, u.id AS user_id FROM `ll_session` as s

LEFT JOIN `ll_user` AS u ON (s.session_id = u.last_visit)
WHERE s.online = &#3912;'

View Replies !

---

Query Optimization
I have the following scenario:

* I have n categories
* I have m users
* I have x textlinks

Textlinks are referenced to a category by the ll_partof table.
I am willing to fetch every user (advertiser) for every category and output the date of the last textlink purchased, the number of his textlinks in that category and the amount of money spent in that category.Here is my query so far. It uses subqueries and is therefore very slow. How can this be optimized?
Select
c.name as Kategorie,
(SELECT timestamp
FROM ll_textlink as t
WHERE t.advertiser = u.email
ORDER BY timestamp DESC LIMIT 1
) as last_link,
u.email as Email,
u.forename as Vorname,
u.surname as Nachname,
u.company as Firma,
(SELECT count(id) from ll_textlink as t
WHERE t.advertiser = u.email
GROUP BY t.advertiser
) as num_links,
(SELECT sum(current_value) from ll_textlink as t
WHERE t.advertiser = u.email
GROUP BY t.advertiser
) as budget_links,
FROM ll_user as u
INNER JOIN ll_textlink as t ON (t.advertiser = u.email)
INNER JOIN ll_adspace as a ON (a.id = t.adspace)
INNER JOIN ll_partof as p ON (p.adspace = a.id)
INNER JOIN ll_category as c ON (p.category = c.id)

WHERE
t.extension_possible = &#391;'
AND t.next_period = '-1'
ORDER BY c.id DESC
l

View Replies !

---

SELECT .. WHERE .. Subqueries ?? Is It Possible?
I have a page which will send over three possible query's to the other page, but the page receiving the query's will only ever execute one..

gal/event/date
gal/venue/date
gal/event/venue/date

I need the page receiving this data to be able to handle what ever gets passed over..

I currently have:

SELECT photo_id, DATE_FORMAT(date, '%d%m%y') AS subdir, views, caption FROM photos WHERE venue_id='$venue_id' AND date='$date'"
which deals with 'gal/venue/date' from above, but how can I structure the SELECT and WHERE so I can have them all covered but in the one SELECT statement?

View Replies !

---

Subqueries And LIMITs...
I want to display a subset of data from another query...

Basically I've got 3 tables which are all joined together in a query...

Lets say I have 300 results rows displayed from that query. Is there a way I can then limit those results without effecting the original query (I still need to be able to pull all the results as well)

I've tried (and it didn't work)...

Quote:

SELECT * FROM products, manufacturers, brands
WHERE

(SELECT stock_code FROM products, manufacturers, brands

WHERE

(manufacturers.manufacturer_id = brands.house_id) AND
(products.brand_id = brands.brand_id)

ORDER BY

manufacturers.manufacturer_name,
brands.brand_name,
products.stock_code)

LIMIT 0 , 10

View Replies !

---