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 Complete Forum Thread with Replies
Related Forum Messages:
Help With A Small Query Optimization Using An Index. THANKS!
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 !
One Big Query Vs Lots Of Small Queries
I am in the process of migrating a MySQL database from one schema to another and am writing a script to extract the info from one table to be inserted into multiple tables on new new db. My question is this, is it better for me to make one giant query (about 1 million records returned) on the source table, manipulate the data, then enter in the data by cycling through the results, or would it be better to split up the query on the source table into lots of smaller queries with a short pause in between?
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 !
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 !
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 !
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 !
Cross Join Effects With Query Optimization
ELECT count(t.id) FROM `ll_textlink` AS t, `ll_period` AS p , `ll_user` AS ua , `ll_user` AS up WHERE t.actual_period = p.id AND p.status_approval_publisher = Ƈ' AND p.value > Ɔ' AND t.online >= Ƌ' AND Ƈ' >= ( SELECT count(p.id) FROM `ll_period` AS p WHERE p.textlink = t.id ) AND t.online <> Ɗ' AND t.current_value > Ɔ' AND t.advertiser = ua.email AND t.publisher = up.email AND ua.rights = Ɔ' AND up.rights = Ɔ' ; This is what I am at now: SELECT count(t.id) FROM `ll_textlink` AS t INNER JOIN `ll_period` AS p ON (p.textlink = t.id) INNER JOIN `ll_user` AS ua ON (ua.email = t.advertiser) INNER JOIN `ll_user` AS up ON (up.email = t.publisher) WHERE t.actual_period = p.id AND p.status_approval_publisher = Ƈ' AND p.value > Ɔ' AND t.online >= Ƌ' AND t.online <> Ɗ' AND t.current_value > Ɔ' AND ua.rights = Ɔ' AND up.rights = Ɔ' ; However, the number return is quite a bit higher than the from the first query. Are there any cross-join effects and if so how can I avoid them?
View Replies !
Handler_read_rnd_next = 13k On SMALL Db
I have two very small DBs. One for a little number game, which only 6 people hit 1-3 times a day. The other is for a prototype of a game I'm working on, and there are no more than 3 users on this. Using phpmyadmin, I check the page with runtime info and I see Handler_read_rnd_next 13 k If I reload this page a couple times, it goes up to 14k. Most of the queries I use look like Code: SELECT c.color, b.percent FROM tbl_creatures a, tbl_creatures_colors b, tbl_colors c WHERE a.creature_id =1 AND a.creature_id = b.creature_id AND b.color_id = c.color_id And the EXPLAIN SQL for these comes up with Code: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE a const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE b ref PRIMARY,color_idPRIMARY 4 const 2 1 SIMPLE c eq_ref PRIMARY PRIMARY 4 hq_proto_creatures.b.color_id 1 To me, this all looks fine. I can't tell if all the Handler_read_rnd_next is due to the phpmyadmin status checks or not.
View Replies !
Small Databases
I am wondering if having a database for the client back office, a database for the static website and a database for website stats would be a good idea. The client database would hold credit card info, user info, reg info, project info, client messages and more. The website database would carry all the content for the pages of the public website like about us, contact, home page, news and such. The website stats database would keep track of user agents, new hits, returning hits, keyword searches made on the client websites and all that jazz. Should I make this all ONE giant database or three smaller database?
View Replies !
One Big Table Or Many Small Ones?
I am designing an interactive website consisting of a few pages and I want to track individual user progress. I was wondering if it would be better to create one big table for all values on all pages (Which may have a lot of columns, easily hundred or more), or created a separate table for each page (so I could have some 10 tables with 10 columns or more). Or does it not matter?
View Replies !
Big Select Vs Small Ones
I have an array of 10 IDs. I need to query the db for values associated with these 10 IDs. In a lot of places, I have seen people using a php foreach like this foreach (ids in array) { query = SELECT values FROM tables WHERE id = [id 1 in array] do query store values } we could build one big select statement like this: query = SELECT values FROM tables WHERE id = [id 1 in array] OR [id 2 in array] OR [id 3 in array].... until we have ids.
View Replies !
One Big Database Or A Lot Of Small Ones?
My boss is implementing a system where a lot of different sites will be using the same exact database structure. He has it in his head that things would be simpler with one database shared by hundreds, possibly thousands of sites. I feel like each site deserves it's own database, even if it involves more maintainence.
View Replies !
Backup A Small Database.
I'm looking to backup a small mysql database. The problem is mysqldump does not exist on the server I'm trying to export the database from. Does a php script exist which I could use to do essentially the same thing as mysqldump?
View Replies !
Small Favor Gone Wrong
I have a VPS account and I am trying to move my sister's professional organizations site onto my server more or less as a favor (getting paid a little - but not really worth my time). It is being hosted by a professional hosting company that supplied me with ftp access to download the site for the move. I am running into a problem because I don't know how to set-up/use mySQL through UNIX. Previously I have used the Plesk interface provided with my VPS account. Plesk (or at least my hosting services configuration of it) won't allow me to create the user I need in order to import the database because the user ID is contained within the user PW. (this is the database coming from the vendor). The original host has been unhelpful at best so I thought before I go to them and ask for assistance (that I am pretty sure I won't get anyway) that I would post here to find out if there is any easy way through this roadblock or if any the unix commands are simple enough, how I would proceed that way.
View Replies !
One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I use only one table with an indexed column for the ID and another column for the data, or should I use 500 tables each with only one column for the data?
View Replies !
MySQL Upload Small Files
I'm uploading small files (>10K) and mysql is deleting it. The one field is a blob field and everytime i test upload a file thru phpMyAdmin within 2 seconds mysql deletes the row. Theres nothing in mysqld.log, there another log i should be checking out?
View Replies !
Structure Of Small Social Network
I need to setup a social network type mysql database, but not as intense as friendster, thefacebook, myspace, etc. i have a structure set up but it think it's weak and extremely limited: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | +---------+-----------+ i fear it's going to be a lot of redundant IDs in a column, which i think could be a problem when retreiving friends: Code: +---------+-----------+ | user_id | friend_id | +---------+-----------+ | 5 | 4 | | 1 | 7 | | 1 | 9 | | 7 | 9 | +---------+-----------+ i want to do a "bob is friends with john" and a "jackie knows sally through richard" (aka friend of a friend). that's it and nothing more. how do i structure mySQL database to be able to this?
View Replies !
One Huge Table As Appose To Many Small Ones
i have a very large XML DB (60gb+ and growing on a daily basis) which holds complete life cycles of stock option , i used to hold it all in folders with each folder named like the option paperId so it seemed logical to me to hold it now in diffrent tables for each paper even tough the are all the same exact table , now i'm having some problems with hibernate (one of the biggest reasons i left XML) and so many tables , is it more effiecent to keeeping data like that ? ofcours i will have to change the PK from datetime to paperId+DateTime if i decide to change to one big table ... how can i calculate diffrence in retrieving data ?
View Replies !
A Huge Database Or A Lot Of Small Databases
I have a question regarding database performance. Which of these two designs would be better: -Having one huge database with 50 tables or so (each table having millions of rows) or -Having a lot of databases with the same amount of tables, but each table would have only thousands of rows *The mysqlserver is running on a P4 3.0 ghz with 1 gb in ram, but I may upgrade my server soon. *Privileges to create databases is not a problem since I own the server
View Replies !
Several Small Calls Vs One Large Call
I am working on a php interface to a mySQL db which will pull lots of rows from the database. Assuming I am selecting a hundred rows by their auto_incremented ID's, is it better to do this with a loop of 100 "SELECT ... WHERE id=" calls, or to do it with one single "SELECT ... WHERE id IN(...)" call, with IN() holding a list of the hundred items.
View Replies !
One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this. My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group. If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?
View Replies !
Small Database, High Load
I have a very heavy mysql database used website. Data is constantly being updated and at peak there is well over 1,000 people online, after it hits the 1,000 mark, the website starts to lag pretty bad. My database is only small at around 90mb. We have a web server for the files, and a database server for the database. The load on the web server is fine. But the database server is pretty high. Would replication reduce lag and load? if i was too add a second database server?
View Replies !
Inserting Small Image Directly Into Database
How do you insert an image into a table directly using something like data studio? I have tried googling it and it seems all the examples are using a language to do it for you. The images are between 2-4k and the table is just an int column followed by a blob column. I just thought the statement would go like INSERT INTO sometable VALUES (1,'image.jpg'); The web suggests uploading and downloading in PHP (which im using) but im building the data first using data studio then just displaying the table information using PHP. Before i go into displaying the information on the page i just wanted to make sure the insert query is correct.
View Replies !
Small SQL & Php Webpage Field Database Task
In a webpage, imagine a little section where the user fills in his/her contact details to register. Among this section is a field called "Country". The task is to make this field automatically generate the persons current country of residence. I am told, that the IP ranges and the country to which they belong is in a CSV .xls file. I am also told that the best way to do this is to: 1) Insert the CSV into database table (MySQL). 2) Use php to get that country name onto the website field.
View Replies !
Large .sql File (break Down To Small Parts)
i have an oscommerce installation that generates a 200mb sql backup file. i want to try and rebuild the installation on my local pc (localhost) to emulate and play with, unfortunately the 200mb file is difficult to handle. phpymadmin seems to crash. i have console access on the host however. is there a way i can break down the sql file into smaller increments for easier uploading?
View Replies !
How To Reset Yahoo Small Business MySQL Database
I am a new user of "Yahoo Small Business" web hosting service and I changed a lot of settings in my "MySQL Database" using "phpMyAdmin" utility during my learning period. Now, I want to reset my Yahoo Small Business MySQL Database to default settings or to reinstall it (I want it to be the same as it was newly activated). Note: I tried to repair my database with Yahoo's "Database Setup" page and the message "Database scheduled for repair" appeared for a few days but nothing happened. And, I can not login to my database using "phpMyAdmin" utility. If you can help me to reset my "Yahoo Small Business - MySQL Database" I will be very pleased.
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 !
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 !
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 !
|
TRACKER
