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?
View Complete Forum Thread with Replies
Related Forum Messages:
Slow Queries When Using Views Compare To Direct Table Queries
I’ve having problems with my server load for a while now. I have two tables with different content, but I need to display them in the same results, so I created a view with a union all (named: top_news_videos). The problem that I’m seeing is that when running a select to the view it takes a lot longer (and in that way, more server intensive) than running the query directly to one of the tables. For example, I created a page where I run 7 queries similar to this one: MySQL Code: ...
View Replies !
MySQL Expert, Optimization Queries
Need someone to optimize my database. I have dedicated server for mysql, which is always overloaded. I run there DB for site with 120k-150k uniques/day. Something is wrong there. Please PM me if u are interested in that kind of job and you have sufficient skills. need it asap
View Replies !
Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.
View Replies !
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?
View Replies !
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?
View Replies !
How To Log-slow-queries
I'm running MySQL 5.0. Now I would like to log-slow-queries. With MySQL 4 I used mysqld --log-slow-queries --log-long-format start but now I get following note: mysqld: Too many arguments (first extra is 'start'). Use --help to get a list of available options It looks like starting and stopping now only works with etc/init.d/mysql start
View Replies !
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.
View Replies !
Slow Queries With LIMIT
Working with a large table (1 Million Records). Setting up pagination using LIMIT command. Queries near the end of the table are slow when non-indexed fields are selected (but not included in the 'where' clause). For example: With indexes on id (PK), and type: SELECT id, user_id, name, type, selected, creation_date FROM titans where type = 2 LIMIT 500010,10; +---------+---------+------------+------+----------+---------------+ | id | user_id | name | type | selected | creation_date | +---------+---------+------------+------+----------+---------------+ | 1000016 | 100001 | Uni Ekko | 2 | 0 | 1228149051 | | 1000017 | 100001 | Dynaneedle | 2 | 0 | 1228149074 | +---------+---------+------------+------+----------+---------------+ 2 rows in set (4.17 sec) If I only search on the ID field, the query is much faster. mysql> SELECT id FROM titans where type = 2 LIMIT 500010,10; +---------+ | id | +---------+ | 1000016 | | 1000017 | +---------+ 2 rows in set (0.36 sec) explain SELECT id, user_id, name, type, selected, creation_date FROM titans where type = 2 LIMIT 500010,10; +----+-------------+--------+------+-------------------+---------------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-------------------+---------------+---------+-------+--------+-------------+ | 1 | SIMPLE | titans | ref | avatars_indx2,tst | avatars_indx2 | 2 | const | 381988 | Using where | +----+-------------+--------+------+-------------------+---------------+---------+-------+--------+-------------+ 1 row in set (0.01 sec)
View Replies !
Do Unnecessary Parentheses Slow Queries Down?
I know a person who seriously writes queries like this: SELECT * FROM my_table WHERE ((((my_col = 30))) AND (((other_col = 40)))) I told him all those parenthesis were unnecessary and he responded that he does it so later if he has to add more criteria the parenthesis will be there. I about fell out of my chair that sounds so ridiculous. So my question is, other than the obvious ugliness and being hard to read, is there any other reasons why NOT to do this?
View Replies !
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:
View Replies !
Fine Tune :: Timeout Slow Queries?
I have a very busy database with some tables having hundreds of thousands of records. I am running MySQL 5.0.16 on Windows. Some of the queries running on my database are not fined-tuned enough, and need improvement. However, the system is live in production and I am looking for a way to prevent MYSQL from committing Harakiri, i.e. from running very slow queries forever, taking the cpu to 50% and beyond for hours. My scripting language is PHP. I found that after a while the end user receives a timeout on the browser, but MySQL continues trying to run the query, sometimes for hours. My question is, is there a way to specify a timeout for MySQL so that it will abort any queries that it is running that have not returned a result by then. The end user is no longer around anyway, so I am looking for a way to protect the database resources (and in parallel I will start investigating how to optimize the slow queries)
View Replies !
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?
View Replies !
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.
View Replies !
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...?
View Replies !
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.
View Replies !
Long Queries VS Multiple Short Queries
I have a php script that requests a very long query from our mysql database. It has lots of joins and accesses at least 5-6 tables. My question is should i break it up into smaller separate queries or leave it as one long one, in regards to best practices?
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 !
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 !
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 !
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 !
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 !
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 !
Optimization :: Subqueries
i have a little complex query that involves sub queries upto three levels. now thing is that , i think, mysql evaluates the sub queries every time that query is evaluated, whereas, i know that results for the third and 4th level queries are same for some number of queries. can we force mysql to store the result of the subqueries to be used later, instead of reexecuting the query.i studied mysql query optimization but, finally concluded is that things over there are just how mysql optimizes queries, not how can we optimize the query to be performed better. any resources for query optimization, i mean good resources ?
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 !
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 !
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 !
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 !
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 !
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 = ཈' ) 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 = ཈'
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 = Ƈ' AND t.next_period = '-1' ORDER BY c.id DESC l
View Replies !
Small Query Optimization
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed.The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
View Replies !
Optimization Of MySql Interaction
I'm building an application in C#, using the .Net connector to interact with MySql. The application tries to find a shortest path between 2 points in a network, and uses a database which is filled with 2000+ network connections. The algorithm for solving this question executes a query to the database in each loop. Such query is a stored procedure with 3 input parameters. This stored procedure is a joined select query over multiple tables, and returns 5 rows (average). The problem: the algorithm is slow in executing the queries. It sometimes runs for minutes. The MySql Administrator shows an average of 300 queries per second, the Taskmanager shows a 100% cpu usage of which 85% is used by mysql and 15% by my program. Is there somehow a possibility to speed things up? Maybe in the configuration of C#>.Net connector>StoredProcedure(3 ins)>MySqlDataReader>C#? Perhaps I can try to move the algorithm closer to the data and store it all in a MySql Function? I have no history of MySql programming though..
View Replies !
Using WHERE With GROUP BY - Index & Optimization
As my database has begun to grown in activity, I've been working to better optimize some queries that I've identified as a bottleneck. One of the biggest issues I've found has to do with a pretty simple SELECT query that has a WHERE condition as well as a GROUP BY clause. It's searching a table that has about 44,000 rows. If I remove the GROUP BY, the query runs in about a second. With the GROUP BY, my query time is upwards of 25 seconds. PHP Code:  SELECT quotes.quote_no , quotes.quote_id FROM items LEFT JOIN quotes ON quotes.quote_id = items.quote_id WHERE quotes.salesman_id = '123ABC' GROUP BY items.quote_id , items.product_idÂ
View Replies !
Mysql 5.1 My.conf Optimization
Can someone help me about my.conf ? I'm using a mysql dedicated server like this : Intel Xeon 2,66 GHz / 2,67 GHz Windows 2003 SP1 Memory 4 GB of RAM Mysql 5.1 Can you tell me for my.conf parameters the max and min values recommended ? (for example by posting a my.conf with comments in each parameter telling max and min values to use). For informations in the soft using DB: - A lot of user - A lot of datas in some big tables (not a lot of join in queries) - Some data sorts or order returning a lot of data
View Replies !
Optimization :: Avoid Order By
I need to get the last entry of a table, so I'm using the following query: SELECT * FROM table WHERE (some conditions) ORDER BY date DESC LIMIT 0,1 Does anybody know an another way to do that? Can I keep a ordered table and than avoi the use of "order by"? If yes, how can I do that?
View Replies !
Optimization :: More Columns Or More Rows?
Is this better to create ? - one row per user with 81 columns - 40 rows per user with each 4 columns Types of my columns are statics: MEDIUMINT or SMALLINT or TINYINT Each SELECT for this user will load all data (one row or 40 rows depending on the choice) My most important criteria is the performance.
View Replies !
Query Optimization Advice?
I am familiar with the VERY basics of MySQL (in other words, I am not an expert...), and I am currently working to create a simple search engine on our website. Users should be able to search a database of over 150,000 sheet music titles by simply inputting a keyword(s) phrase. That phrase should be searched into different tables at the same time but if the keyword phrase includes more than one word, any word need to be searched. For example, let's say an user is looking for "Brahms violin concerto". The search engine should display all the records that matches all those entered keywords. Here are the tables that need to be searched: |title|composer|instruments|description| and here is the query I am trying to work on (after have split the keyword phrase into separate keywords): Quote: SELECT title, composer, instruments, description, price FROM mydatabase WHERE (title REGEXP "(^| )brahms( |$)" OR title REGEXP "(^| )violin( |$)" OR title REGEXP "(^| )concerto( |$)") AND (composer REGEXP "(^| )brahms( |$)" OR composer REGEXP "(^| )violin( |$)" OR composer REGEXP "(^| )concerto( |$)") AND ((instruments REGEXP "(^|;| )brahms(;| |$)" OR instruments REGEXP "(^|;| )violin(;| |$)" OR instruments REGEXP "(^|;| )concerto(;| |$)") OR (description REGEXP "(^| )brahms( |$)" OR description REGEXP "(^| )violin( |$)" OR description REGEXP "(^| )concerto( |$)")) order by title like "%brahms%", title like "%violin%", title like "%concerto%", instruments like "%brahms%", instruments like "%violin%", instruments like "%concerto%" The query works, but it takes too long to be executed... over 10 seconds! I am aware that REGEXP (like "LIKE") don't uses indexes, but I cannot find a different solution to match any possible keyword or part of it. Any suggestion to optimize it? Or should I work on a completely different approach?
View Replies !
Mysql Index Optimization
Can anyone suggest which index is optimal for table "items"? CREATE TABLE categories ( id TINYINT(2) UNSIGNED NOT NULL AUTO_INCREMENT, parent_id TINYINT(2) UNSIGNED NOT DEFAULT 0, name VARCHAR(30) NOT NULL DEFAULT '', count MEDIUMINT(8) NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX parent_id (parent_id) ); CREATE table items ( id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, cat TINYINT(2) UNSIGNED NOT DEFAULT 0, sub_cat TINYINT(2) UNSIGNED NOT DEFAULT 0, sub_cat_sub TINYINT(2) UNSIGNED NOT DEFAULT 0, PRIMARY KEY (id), /* option 1 */ INDEX browse (cat, sub_cat, sub_cat_sub, pageviews) /* option 2 */ INDEX sub_cat_sub (sub_cat_sub, pageviews), INDEX sub_cat (sub_cat, pageviews), INDEX cat (cat, pageviews), ); Note that the results in "sub_cat" is a subset of the results in "cat" as is "sub_cat_sub" to "sub_cat", so that "sub_cat_sub" is most unique. The user must be able to browse all items found in a specified category (cat), the items found in the subcategory of that category (sub_cat), and the subcategory of that subcategory (sub_cat_sub). I am aware option 1 will make INSERT faster and will save disk space compared to option 2. However, the option 2 will make SELECT faster and save memory usage as the index used is optimal (in terms of from most unique to least unique). Also with option 1, the only time an index is used to sort (by pageviews) is when all three categories are set.
View Replies !
|
TRACKER
