php MySql 2 column as index and 1 as another index?

Question

I'm using php and i have a table that have 2 column of varchar , one is used for user identification, and the other is used for page name entry.

they both must be varchar.

i want to insert ignore data when user enter a page to know if he visited it or not, and i want to fetch all the rows that the user have been in.

fetch all for first varchar column.
insert if not exist for both values.

I'm hoping to do it in the most efficient way.

what is the best way to insert without checking with another query if exist?

what is the best way other then:

SELECT * FROM table WHERE id = id

to fetch when the column needed is varchar?

Not entirely sure if you're looking for this, but if you aren't concerned with all the columns in a table, just condense the query down to the relevant columns: `SELECT col1, col2, col3 FROM table_name WHERE id = ?`. Also, if a certain column is used as an `id`, it is always a good idea to use types such as `TINYINT`, `INT`, `BIGINT` with `UNSIGNED` attribute. Makes the look-up much more faster than `varchar`. — Dhruv Saxena, Mar 30 '17 at 21:09

score 2 · Accepted Answer · answered Mar 30 '17 at 20:45

You should consider a normalized table structure like this:

CREATE TABLE user (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE page (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE pages_visted (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    page_id INT UNSIGNED,
    UNIQUE KEY (user_id, page_id)
);

INSERT IGNORE INTO pages_visted (user_id, page_id) VALUES (:userId, :pageId);

SELECT page_id FROM pages_visted WHERE user_id = :userId;

score 1 · Answer 2 · edited May 23 '17 at 12:25

I think you want to implement a composite primary key.

A composite primary key tells MySQL that you want your primary key to be a combination of fields.

More info here: Why use multiple columns as primary keys (composite primary key)

I don't know of a better option for your query, although I can advise, if possible:

Define columns to be NOT NULL. This gives you faster processing and requires less storage. It will also simplify queries sometimes because you don't need to check for NULL as a special case.

And with variable-length rows, you get more fragmentation in tables where you perform many deletes or updates due to the differing sizes of the records. You'll need to run OPTIMIZE TABLE periodically to maintain performance.

thank you, that will use for the insert, do you know if there is any thing better then "select all where id" to get the data? — dvrer, Mar 30 '17 at 20:43

php MySql 2 column as index and 1 as another index?

2 Answers2