CODEWITHSUNDEEP
mysqlsql
user3853978
user3853978

Reputation:

create index in SQL table shows no index defined

i used the following code to create a table table is created but warning is shown 

No index defined!

i used the following SQL command to create table

CREATE TABLE IF NOT EXISTS `test` (
`path` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

i used the following PHP code to insert multiple image path into database but each path is store in new row how do i store in single row in SQL table

if ($_FILES) {
$upload = new Upload_Rename();
$destination = 'upload';
$paths=$upload->multiple_files($_FILES['myfile'], $destination);

//Fill this with correct information
$mysql_hostname = "";
$mysql_user = "";
$mysql_password = "";
$mysql_database = "";
$tbl_name="";
$pathfield_name='path';
//
$mysql= new mysqli($mysql_hostname,$mysql_user,$mysql_password,$mysql_database);
foreach($paths as $path){
$query='INSERT INTO `'.$tbl_name.'` (id, `'.$pathfield_name.'`) VALUES ("'.$mysql- >escape_string($path).'");';
$mysql->query($query);}
$mysql->close();
}
?>

<form  method="post" enctype="multipart/form-data">
<?php for ($i = 0; $i < 10; $i++): ?>
file: <input type="file" name="myfile[]"><br>
<?php endfor; ?>
 <input type="submit">

Upvotes: 0

Views: 1748

Answers (3)

hiennt
hiennt

Reputation: 890

You should have a PK as A_I field in your table, thats better performance for query/indexing.

CREATE TABLE IF NOT EXISTS `test` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`path` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Also, the path only 50 chars, why you expected multiple paths in 1 row? What is purpose?

Upvotes: 0

Stanislav
Stanislav

Reputation: 11

  1. If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

  2. If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

  3. If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

So in case of 3 the synthetic index will be created. And this warning just provides the understanding, that no special index has been defined for the table, that can cause in the future issues with sorting, searching e.t.c. queries.

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

Upvotes: 1

Andrzej Reduta
Andrzej Reduta

Reputation: 767

CREATE TABLE IF NOT EXISTS `test` (
`id` serial,
`path` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 0

Related Questions