w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Do we need to delete the collection named system.indexes to make new indexes apply on the collection
Thew system.profile collection is not there however, no you do not. The profile collection is the output of the profiler, nothing more. Indexes will still apply. Edit Since your question says two things, no you do not need to drop system.indexes either, MongoDB will handle updating the records in there for you. Dropping it might actually damage your database.

Categories : Mongodb

NSFetchResultController with UICollectionView issue with indexes/cells on update and delete action
You can't link a fetched results controller to a collection view in quite the same way as a table view. A fetched results controller was designed specifically to mediate between core data and a table view, so the delegate methods tie in with how a table view works. The main difference is that a table view has a beginUpdates/endUpdates method pair that you can wrap all of the updates in. The collection view doesn't have this, you have to instead build up all of the required update calls yourself, then when the fetched results controller has finished updating, execute them all within a performBatchUpdates:completion: call. There is an example implementation of this on GitHub.

Categories : IOS

Indexes in MySQL
For question 1: Yes, the query will use both indices. FULLTEXT indices can be kind of tricky, however, so it's a good idea to read the MySQL documentation thoroughly on them and use EXPLAIN on your queries to make sure they are properly utilizing indices. For question 2: If you have a multiple column index, the index has to have the same columns in the same order as the query to be used. So in your example, the index wouldn't be utilized. EXPLAIN is a very powerful tool for understanding how queries use indices, and it's a good idea to use it frequently (especially on queries which are programatically generated). http://dev.mysql.com/doc/refman/5.0/en/explain.html

Categories : Mysql

MySQL indexes eq to Key?
Yes. MySQL treats INDEX and KEY as synonyms in most contexts. They refer to the same data structure. You can even declare an index in a CREATE TABLE using either KEY or INDEX: CREATE TABLE foo ( x INT, y INT, KEY (x), INDEX (y) ); Re your comment: InnoDB in MySQL 5.5 and later (or 5.1 if you enable the InnoDB plugin) supports Fast Index Creation, which builds only the index you create, and leaves the table and other existing indexes alone. You don't need to do anything differently to take advantage of this feature, just CREATE INDEX or ALTER TABLE...ADD KEY as you would normally. But fast index creation doesn't apply for the following cases: When changing a primary key. If you use MyISAM tables. If you use MySQL 5.0 or earlier If you use MySQL 5.1 and the "builtin" Inn

Categories : Mysql

Mysql - Query and indexes
Can you provide more details on the tables by using "SHOW CREATE TABLE "? From the EXLPAIN output it seems that your current keys (which are the PRIMARY ones) are sufficient for this SQL. As you can see, all the lines out of the EXPLAIN state that a KEY is being used (the PRIMARY one). I suggest reading http://dev.mysql.com/doc/refman/5.0/en/explain-output.html on how to interpret the output, most important things to look out for are, possible_key being NULL (meaning that you are not using any key for the data selection), rows being a small number (unless you are not matching anything, then the total number of rows in the table is ok), Extra shouldn't say things like Using temporary; Using filesort which means a slower join/sort/select/search is being preformed.

Categories : PHP

When does MySQL update the indexes
You may want to refer to the following links which I found helpful for your question: This question on stackoverflow This link from mysqlperformanceblog This post from DBA Stackexchange forum

Categories : Mysql

MySQL Indexes and possible_keys
Possible_keys only considers indexes that might be relevant to the current query. You may certainly have other indexes in the table, even if they can't be of any help to this query. MySQL considers the frequency of values in a column, and may decide not to use an index because you're searching for values that don't help to narrow down the set of rows. By analogy, if you look at the index at the back of a book, you don't see the word "the" in the index. It would just list of every page number in the book, which would be pointless. There's no reason to use the index in that case, and it would actually make the search slower to use the index. Likewise, you're searching for terms empl_id=86560 AND week_number=22, but these specific values may be so common in your table that MySQL decid

Categories : Mysql

How can I determine the best place to add indexes in mysql?
Some thumb rules: Index all the fields on which you will define WHERE conditions. Index all the fields on which you will define relationships. Index all the fields on which you will define grouping criteria. Avoid indexing everything in your table. Think before creating the indexes. (Although it depends on your specific needs, I personally avoid creating indexes on floating point columns) Obviously, the above rules imply that you must index all the fields that work as primary key or as foreign key (if your tables are normalized, you already must have created the appropriate primary keys; if your tables are not normalized, then normalize them).

Categories : Mysql

Clustered indexes, mysql and Rails
If you don't want to use composite_primary_keys then you may be stuck just relying on a standard index on :organisation_id or [:organisation_id, :id] My understanding is that Rails cares about PrimaryKeys so much because of the assumptions is makes with relationships between models. Perhaps it should be improved, you could always suggest it as a future feature.

Categories : Mysql

Do the indexes from the phpmyadmin MySQL need to be the same in Sphinx
No. You setup sphinx to index the column(s) you need to be able to search. They dont need to match mysql indexes at all. But if you recreating a search that used to use the mysql full-text indexes, then you would create the sphinx index on the same column(s) plus any additional columns you need to be able to filter, group or sort by.

Categories : Mysql

Mysql query still slow even using indexes
Reverse the order of the tables and use a join condition, which includes the extra condition: select distinct customer_id from systems_address join customers_address on systems_address.address_id = customers_address.address_id and customer_id != -1 where system_id = 2 This should perform very well, using indexes and minimizing the number of rows accessed. Make sure you have the following indexes defined: create index idx1 on systems_address(system_id); create index idx2 on customers_address(address_id); Just to be sure, also update the statistics: analyze systems_address, customers_address;

Categories : Mysql

MySQL InnoDB indexes slowing down sorts
In the slow case, MySQL is making an assumption that the index on STATUS will greatly limit the number of users it has to sort through. MySQL is wrong. Presumably most of your users are ACTIVE. MySQL is picking up 50k user rows, checking their ACCESS_ID, joining to MIGHT_FLOCK, sorting the results and taking the first 100 (out of 50k). In the fast case, you have told MySQL it can't use either index on USERS. MySQL is using its next-best index, it is taking the first 100 rows from MIGHT_FLOCK using the STREAK index (which is already sorted), then joining to USERS and picking up the user rows, then checking that your users are ACTIVE and have an ACCESS_ID at or above 8. This is much faster because only 100 rows are read from disk (x2 for the two tables). I would recommend: drop the index

Categories : Mysql

MySQL - will indexes speed up my search queries?
From MySQL docs http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html which is a pretty interesting read anyway: Suppose that you issue the following SELECT statement: mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer will attempt to use the Index Merge optimization (see

Categories : Mysql

Finding optimal indexes for this MySQL query
I would recommend changing the userid index (assuming you're not using it right now) to have active first and userid later. That should make it more useful for this query.

Categories : Mysql

Indexing mongodb collection with 2dsphere indexes and IP address indexes
This should work: db.position.ensureIndex({"geoField.coordinates":"2dsphere","ipaddress":1}) Take a look at this article I hope it helps

Categories : Mongodb

MySQL PDO delete users older than 2 days, delete content from other tables with user_id
Ideally, you'd define things with a FOREIGN KEY constraint, and define an ON DELETE CASCADE, which automagically will delete all that related data for you. If that's not possible for some reason (stuck with a MyISAM table for instance), you could simply JOIN the related tables (yes, you can delete from more then 1 table at once). If it's your first time doing that, do it on a testdatabase, and certainly not in production.

Categories : Mysql

Difference between paired indexes and single column indexes?
You wasn't using the paired index. You are restricting your data based on one of the columns at a time. Not both at once. In fact you are just using the TIME index because of your WHERE clause. The paired index would have speed your query up if your WHERE clause restricts the SENSOR_ID additionally. To see which indexes are really used you should have a look at the execution plan of the query.

Categories : Mysql

Speed of MySQL SELECT (are indexes worth it on columns with little variation)?
Create a composite index of both columns in the WHERE clause: CREATE INDEX ix_sp ON buildings (scrape_status, parse_status); Although each of them by itself doesn't partition the table too much, the combination probably does. Try the following as well: SELECT b1.id, olr_id, COMPRESS(source_html) from buildings b1 JOIN (SELECT id FROM buildings WHERE scrape_status=1 AND parse_status=0 LIMIT 1) b2 USING (id) Your original query may be compressing all matching rows, even though most of them are thrown out by the LIMIT clause. This version only compresses the one selected row.

Categories : Mysql

I want to create table with some specific columns with indexes and data in mysql
Try this: CREATE TABLE newtable AS ( SELECT id FROM oldtable ); Note oldtable has more fields. Here is the sqlfiddle link.

Categories : Mysql

Creating indexes prior to LOAD DATA for performance in MySQL
The AWS recommendation to put secondary indexes in place before loading the data applied to older MySQL versions (< 5.5) because of the way secondary indexes were handled: From the MySQL 5.5 docs: Creating and dropping secondary indexes has traditionally involved significant overhead from copying all the data in the InnoDB table. The fast index creation feature of the InnoDB Plugin makes both CREATE INDEX and DROP INDEX statements much faster for InnoDB secondary indexes. MySQL offers the following recommendation in the 5.5 documentation: Because index maintenance can add performance overhead to many data transfer operations, consider doing operations such as ALTER TABLE ... ENGINE=INNODB or INSERT INTO ... SELECT * FROM ... without any secondary indexes in plac

Categories : Mysql

Mysql before delete trigger, prevent delete if no where clause?
That's really a mistake a developer should only make once... but, here's the correct fix if you believe it needs to be system-wide. No cleverness required. (Also, triggers in MySQL are FOR EACH ROW so the answer to your question is "not really" although you could hack it with clever use of a session variable or two). MySQL has a system variable called sql_safe_updates. mysql> SET GLOBAL sql_safe_updates = 1; Query OK, 0 rows affected (0.00 sec) Disconnect and reconnect so your session picks up the new global value. mysql> delete from t1; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column When using the command line client, you can temporarily enable this setting just for your session w

Categories : Mysql

MySql LEFT JOIN Takes a Long Time - Indexes have been added
MySQL cannot use an index to help with date_format(a.date_audit, '%b %Y') = 'Dec 2012'. Do you have separate indexes on job_number and date_audit or one combined index. If it is a combined index then again MySQL cannot use it for any form of query on date_audit alone. Either create an index on date_audit by itself or a combined index with date_audit first and job_number second. Then convert the WHERE clause to compare date_audit to actual date values. This is a bit of a shot in the dark, but worth a try: First, time this: SELECT * from f31 a WHERE a.date_audit >= '2012-12-01' AND a.date_audit < '2013-01-01' It should be quite fast. If not, you need to verify your indexes and the types (DATE, DATETIME, or TIMESTAMP) of the date_audit columns. Then: SELECT DISTINCT a.job

Categories : Mysql

How to delete an image when I delete the path in mysql db with PDO
First: use bindParam in the following way: $sth = $conn->prepare("DELETE FROM `IMAGENES` WHERE `id_imagen` = :idToDelete") $sth->bindParam(':idToDelete', $id_imagen, PDO::PARAM_INT); But before that most probably you have to use SELECT in order to get the name of the file. After that use that name in unlink, not the variable with ID. Post your table structure here if you want good advice. Quite not sure what is $_POST["recordToDelete"] and why you trying to use $_GET after that. If the imagenes column store file names you want to delete, based on id_imagen try the following way: <? include_once("config.php"); /*hope above is the connection with MySQL and that connection is $conn */ if(isset($_POST["recordToDelete"]) && strlen($_POST["recordToDelete"])>0 &&

Categories : Jquery

Selection Indexes are not same to getText indexes
See Text and New Lines for more information and a solution. The basics of this link is to use: int length = textPane.getDocument().getLength(); String text = textPane.getDocument().getText(0, length); The above will only return " " as the EOL string so the offsets will match when you do a search and then select the text.

Categories : Java

Single column indexes vs multiple column indexes
If your data is usually unique based on urlId alone, you are probably better off not creating a secondary index esp. if you can use irlId as the clustered index. Since the 2nd part of the index is much larger than bigint, you will likely have better performance by have more keys per block and you avoid the extra overhead in key maintenance. However, if you can be fairly sure that you don't have lots of records that share a common urlId, the 2-part is likely to be a better choice for better worst-case performance. You really have to measure to be sure though if the difference is important to you.

Categories : SQL

Delete Row from DB using PHP/MYSQL
In your deleteKey.php you may have an error: require 'db.php'; $sel_key = $_POST[sel_key]; //SQL statement to select information $sql = "SELECT * FROM addKeywordTable WHERE keyID = $sel_key"; Should that second line read $sel_key = $_POST['sel_key']; instead? This would cause the record to never be selected in the query that follows.

Categories : PHP

How to delete a certain row from mysql table?
Add a limit to the delete query delete from orders where id_users = 1 and id_product = 2 limit 1

Categories : Mysql

MySQL: Delete Trigger
Try that GRANT DELETE ON databasename.tablename TO 'SENEVICA'@'MYIP'; FLUSH PRIVILEGES; EDIT: how about : SELECT * FROM mysql.db WHERE USER='SENEVICA'; SELECT * FROM mysql.tables_priv WHERE USER='SENEVICA'; SELECT * FROM mysql.columns_priv WHERE USER='SENEVICA'; SELECT * FROM mysql.procs_priv WHERE USER='SENEVICA'; FLUSH PRIVILEGES; In those queries You have special priveleges for tables/columns.... see if any query returns any row. EDIT 2: I have found also: GRANT SUPER ON '<database>'.'<tablename/*>' TO '<username>'@'<host/connection/ip/%>';

Categories : Mysql

Can't delete rows in mysql
These things? foreach ($_POST["borrar_usuario"] as $valor){ $del_user = $valor; $q_borrar = "delete from usuarios where id_user in ('$del_user')"; mysqli_query($con, $q_borrar); echo "usuario borrado"; header('refresh: 3; url= exito.php'); }

Categories : PHP

Not delete specified content from mysql
Is the ID numeric only? Would the addition of quote marks around $id not help? $query = "DELETE FROM `users` WHERE `id`='$id'"; mysql_query($query); Not sure...but give it a go!

Categories : PHP

php delete mySQL row from
Look at connect.php and make sure there is NO output. header() won't work if there is any blank spaces or empty lines output before it. Very easy to have some kind of blank characters put in by mistake. http://php.net/manual/en/function.header.php

Categories : PHP

PHP-MYSQL Cannot delete on mysqli_multi_query
TRy to use this code: if ($mysqli->multi_query($query4)) { do { if ($result = $mysqli->store_result()) { $result->free(); } /* print divider */ if ($mysqli->more_results()) { printf("----------------- "); } } while ($mysqli->next_result()); }

Categories : PHP

MySQL Delete with Limit
The delete statement is not working because of incorrect syntax near the limit keyword...you cannot do LIMIT 1,18446744073709551615...LIMIT must be followed by a single number when used with DEELTE Reference: DELETE MYSQL Only in a SELECT statement can you have LIMIT 1,18446744073709551615 clause Reference: SELECT MYSQL What you can do alternatively is find the course_id of the first row you want deleted, (and hopefully if the table is set up correctly and it is a primary key for the table and auto increments), do something like the below: $sql = "DELETE FROM ".TABLE_PREFIX."news WHERE course_id >=".COURSE_ID_YOU_WANT_DELETED."; Or this works as well: $sql = "DELETE FROM ".TABLE_PREFIX."news WHERE course_id !=".COURSE_ID_YOU_WANT.";

Categories : PHP

MySQL query help. (delete from where)
If you are just looking for the correct syntax then you can use: DELETE FROM {table} WHERE {param} = 84 Replace {table} with your table name and replace {param} with your column where you expect the ID to match ie item_id or purchasedBy. I can't give better advice at the moment as I don't really understand your table structure.

Categories : Mysql

MySql delete row and add it to another table?
What you are searching is creating a "BEFORE DELETE" "TRIGGER" with an "INSERT" statement. Google it and you will find how to do something like this. Here's an example for you: DELIMITER $$ DROP TRIGGER `delete_personne`$$ CREATE TRIGGER `delete_personne` BEFORE DELETE on `personne` FOR EACH ROW BEGIN INSERT INTO histo_personne SELECT OLD.initialespers,OLD.nompers,OLD.prenompers,OLD.datenaisspers,OLD.photopers,OLD.validepers,now(); END$$ DELIMITER ; Source: http://forums.mysql.com/read.php?99,140057,141249#msg-141249

Categories : Mysql

Delete multiple rows by sql statement "delete from tableName" whereas TRIGGER "After Delete" had applied on that
Either set @roid=(select ReachOutID from deleted(nolock) where deleted.NotificaionType='reachoutlike') Or set CACHE_Reachout.LIKEcount= (select [dbo].[getReachout_Notification_Count](@roid,'like') ) where CACHE_Reachout.ReachOutID=@roid is returning more than 1 row of data. Raj

Categories : Sql Server

How to delete user from MySQL database
The problem is that every input has the same name so you get nothing when you try to obtain POST values. Try a different approach. Maybe you can add a new field (hyperlink) that performs the redirect action and send the parameters of deleting action with GET. Example: echo " <input type='text' value='$row[user_name]' name='user_name'> <input type='text' value=' $row[course]' name='course'> <input type='text' value=' $time'> <a href='Test.php?username=$row[user_name]&course=$row[course]'>delete</a> <br>"; } Test.php is the name of your php page. Then you can query the database by using the the GET value: $_GET["username"]

Categories : PHP

Delete last character and add new two characters in MYSQL
Try something like this: UPDATE ds_1_headword_backup1 SET stem = concat(substring(stem,1,length(stem)-1), '|i'); Example: SQLFiddle

Categories : Mysql

delete string date in mysql
You can do it like this: DELETE FROM result WHERE NOW()>`date` + INTERVAL 7 DAY Tip: do not use function names and reserved words as column or table names. Tip: do not use mysql_* functions since they are officially deprecated

Categories : PHP

Delete duplicate rows in mysql
You forgot to mention count(*)>1 DELETE FROM location where country in ( SELECT * FROM table_name GROUP BY country, state, city having COUNT(*)>1); Just for your information. You can also use ALTER IGNORE TABLE to delete the duplicates. Something like this:- ALTER IGNORE TABLE location ADD UNIQUE INDEX (COLUMN1, COLUMN2)

Categories : Mysql



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.